Hi Everyone!
I think I posted this question in the wrong place yesterday and thought maybe it would better to start a new thread.
I am trying to build an Access 2007 form (a menu system) dynamically from a table. The table looks like the Switchboard items table, but instead of having a number that represents the type of event, I have the actual code in a field.
MenuItem - table
MenuID the specific menu, 1, 2, 3, etc
Menu Option - the option number within the menu
Menu desc - the literal that will appear on the form buttons caption
MenuCode - the actual code such as Do.Cmd that needs to run for this button.
I have the program working so far like this.
Open a recordset using a SQL to grab all of the records for a particular menu such as menu 2 which is inventory management.
Then I load all of the buttons that are on that menu form - changing their captions to what is in the table. The buttons are blank until this happens in the code at Form_Load time. All of this works just fine.
The problem - I know how to use VBA to alter the caption property from a value in a table, but not how to build an event from the code that is also strored in the table.
If I were doing this manually, I would go to the OnClick event for the button, click on the elipsis which would open the code editor and present me with an OnClick Sub and End sub for that button. Then I would put the Do.Cmd line of text between them and save it, and the button would then be able to open a form or report.
I know that the OnClick is a property just like Caption is, but cannot find anything in the book on how I would go about programmatically building the menu so that the OnClick events would show up in the Code Editor, coming from a table. Is this even possible?
My code to change the captions is shown below. It needs to be put into a loop and is just procedural at the moment, but I wanted to figure out how to do this before making the code better with error trapping and looping instead of essentially hard coding each menu option which is how it is now.
If you look at the program below, you can see that I am changing the caption property with the field ItemText from the database table. The question is, how would I take a field called MenuCode and make the Do.Cmd OpenForm code get into the code editor using VBA? Right now I coded all of the _Click events for all of the buttons, but I have the text DoCmd.OpenForm "CustMenu" in the table. I hope my description makes sense and that someone with VBA experience would know if this is even possible to do.
Thanks so very very much for reading this. The assignment for school is to just be able to build the menus and take the captions from the table which I was able to do - but I really want to be able to make totally dynamic menus where everything is stored in a table.
Thanks for any help, ideas or suggestions you might have! :)
Best Regards,
Ann Z in CT
Code:
Option Compare Database
Private Sub btn1_Click()
DoCmd.OpenForm "CustMenu"
End Sub
Private Sub btn2_Click()
DoCmd.OpenForm "InvMenu"
End Sub
Private Sub btn3_Click()
DoCmd.OpenForm "OrderMenu"
End Sub
Private Sub btn4_Click()
DoCmd.OpenForm "ReportsMenu"
End Sub
Private Sub btn5_Click()
DoCmd.Close acForm, "MainMenu", acSaveYes
End Sub
Private Sub Form_Load()
Dim conn1 As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL 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=1 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]
rs.MoveNext
btn2.Caption = rs![ItemText]
rs.MoveNext
btn3.Caption = rs![ItemText]
rs.MoveNext
btn4.Caption = rs![ItemText]
rs.MoveNext
btn5.Caption = rs![ItemText]
End Sub
Is it possible to dynamically update OnClick code for Event?
