Move form from table field into variable to run form

AnninCT · April 17th, 2010, 11:17 AM

Hi Everyone!
I am having trouble building custom menus and specifically ...

I need to take be able to open a form or a report using a variable form/report name that is coming from a table.

First read the file containing the menu options sequentially.
Move the menu description from field ItemText field to the caption property
Move the form name from field Arguement into a variable so that when the form runs the user can click on the menu button and run the variable form.

Code:

Private Sub btn1_Click()
     ' DoCmd.OpenForm "frmAddCustomer"  (This works fine - opens the form)
     ' But here I want to open the form from a variable name loaded below.
DoCmd.OpenForm pgm1
End Sub


Private Sub Form_Load()
Dim conn1 As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    Dim pgm1 As Form
    Dim pgm2 As String
        
  ' Format the strSQL string with the fields that I want and the user selected search field as the customer name to search for.

    strSQL = "SELECT * FROM MenuItems WHERE MenuID=3 Order By ItemNumber"
    
  ' Start the connection and process the SQL query on the recordset.
    Set conn1 = CurrentProject.Connection    
    Set rs = New ADODB.Recordset

    rs.Open strSQL, conn1
    rs.MoveFirst   ' new code added
    btn1.Caption = rs![ItemText]  'THIS PART WORKS FINE
    pgm1 = rs![Argument]           ' THIS WILL NOT WORK AT ALL
    btn2.Caption = rs![ItemText]
    pgm2 = rs![Argument]

End Sub

'I dont know how to put a form name into a variable and then use that variable in a Do.Cmd statement.

If anyone knows how to do this I would be very grateful. I know how to find out if a specific form is open or not, but do not have any idea how to move the field from the table into a variable and then use that in the Do.Cmd statement. Is there some special way to specify a variable as a form? Without knowing what the keyword is, it is almost impossible to look this up or find it on the Internet.

Thanks Everyone!
Ann