Re: Call a subroutine

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Rick Rothstein (rickNOSPAMnews_at_NOSPAMcomcast.net)
Date: 04/23/04 

Date: Fri, 23 Apr 2004 10:02:49 -0400

> I have a form with check boxes on it. These checkboxes represent tables
where I want to move data from one table in one database to another tablein
another database (the tables are the same name BUT not the same structure).
>
> The routine is part of a data conversion plan between and old system and a
new system. The table names are fixed and the therefore I had them setup on
the form as checkboxes. Then, depending what the user was to click on (1 or
all), the code would loop through the checkboxes and, if it were checked,
call the procedure for that checkbox....the easiest solution being a routine
of the same name. That routine has code to specifically move data from a
table in one database to a table in another database. Hence the
>
> Private Sub cmdTransfer_Click()
> Dim x As Integer
>
> 'which option was checked
> For x = 0 To optTable.Count - 1
> If optTable(x).Value = True Then
> Call optTable(x).Caption
> End If
> Next
> End Sub
>
> The optTable(i).Caption is the name of the routine to call.
>
> This routine is not commercial. It's simply a tool I will need to use more
than once to move data from one table to another in different databases.
>
> The tables are in Access.

I notice you changed from using CheckBox'es to using OptionButton's... good,
that took care of one of my comments.<g> As for your set up, how about
putting this in your CommandButton's click event...

Private Sub Command1_Click()
  Dim x As Long
  For x = 0 To optTable.UBound
    If optTable(x).Value Then
      Select Case optTable.Value
        Case 0
          Call <<routine for optTable(0) here>>
        Case 1
          Call <<routine for optTable(1) here>>
        Case 2
          Call <<routine for optTable(2) here>>
        Case ...etc...
          Call <<....etc....>>
      End Select
      Exit For
    End If
  Next
End Sub

Notice I used optTable.Ubound instead of optTable.Count-1 in the For
statement part of the For-Next loop. Now, the above is suitable for a small
number of OptionButton's. If you have a large number of them, there is
another tact you can take. Declare a variable with Form-wide scope (that is,
Dim it in the Form's General-Declarations section), and set it in the Click
event of the OptionButton; then react to it in the CommandButton's Click
event. Something like this

Dim WhichOption As Long

Private Sub optTable_Click(Index As Integer)
  WhichOption = Index
End Sub

Private Sub Command1_Click()
  Dim x As Integer
  Select Case WhichOption
    Case 0
      Call <<routine for optTable(0) here>>
    Case 1
        Call <<routine for optTable(1) here>>
      Case 2
        Call <<routine for optTable(2) here>>
      Case ...etc...
        Call <<....etc....>>
  End Select
End Sub

This is overall the same as the first method, but it eliminates the need for
the For-Next loop. By calling the routines in the Select Case, you remove
the dependence of keeping the OptionButton Caption's the same as the routine
names. This allows you to use more descriptive Captions; that is, more
easily read phrases and/or sentences with spaces in them.

Rick - MVP

Relevant Pages

  • Re: Modules not Known
    ... Dim pCode$ ... Dim pCodeModule As CodeModule ... Private Sub VBIDEReference() ... Option Compare Database ...
    (comp.databases.ms-access)
  • Re: AddPicture in VBA without explict reference to a file path?
    ... Private Sub SetLinkedImagesLocalPath() ... ' This routine sets the new path for external links. ... Dim oRange As Word.Range ... Dim oField As Word.Field ...
    (microsoft.public.word.drawing.graphics)
  • Re: populate subform after update
    ... Private Sub Form_AfterUpdate ... Dim db As Database ... 'Establish connection to current database ... tblitems does have a yes/no field for active items. ...
    (microsoft.public.access.modulesdaovba)
  • Re: populate subform after update
    ... Private Sub Form_AfterInsert ... Dim db As Database ... Dim LSQL As String ... 'Establish connection to current database ...
    (microsoft.public.access.modulesdaovba)
  • Right Click Menu not working when sheet is in Page Break Preview m
    ... I have a routine which adds two additional items to my right click menu. ... Private Sub App_WorkbookBeforeClose ... Dim ctrl As CommandBarControl ... 'add a 'popup' control to the cell commandbar ...
    (microsoft.public.excel.programming)