I just took over the DBA role for our company and it's been 10 years since I last worked with VBA code.. But, I have a Dbase that is tracking event reports. The form that opens has every different scenario available in text boxes piled on top of one another. There are 7 different events available in the first combo box and once a selection is made the code currently uses a Select Case routine that will either make visible or hide the text boxes associated with the selected case.
My question is, can I create forms for every case and use the DoCmd to populate the open form with the form associted with the first event selected and have all that data put into the same table?
I can post some fo the VBA code but it's 18 PAGES long!!! argh...
Apart from opening a form is different modes (datasheet, normal, icon, add, edit, read-only, ...etc.), one of the easiest thing you can do if the form is based on a data set consists in providing a filter that will limit (filter) the rows of the data set that the form will display. Such a filter can be a query in the current database or, more simply, a Where Condition, i.e. a string containing the 'Where' part of a SQL statement but without the word 'Where'. Ex. "[InvoiceDate] > #1/1/2010#".
You can also use the last optional parameter of the DoCmd.OpenForm method (OpenArgs) to pass anything you want to the form you open (anything but it must be a string). OpenArgs is a property of the opening form and you can address it using Me.OpenArgs in the form's module. Be aware that it's a Variant of type String, so you must check for Null, not for zero-length string.
I sometimes use this method with a standard schema: one or several arguments can be passed into the string with the list-separator used to separate the different parts:
Option Compare Database
Private m_Arguments As Variant
Private Sub Form_Open(Cancel As Integer)
If Len(Nz(Me.OpenArgs, "")) > 0 Then m_Arguments = Split(Me.OpenArgs, ";")
If I understand you correctly, I can filter and load only the criteria i'm looking for? If that's the case, I'm not sure how that solves my original issue of a single form holding every piece of info stacked on top of one another. It makes it virtually impossible to manage and update the form.
Another possibility is to have the combo box on the Main form and then have an Unbound subform on the main form. When the user selects from the combo box you can change the ControlSource for the Unbound subform.
I have attached a screenshot of a form that I created. The whole right side of the form is an unbound subform and when the user selects from the menu on the left the appropriate subform is displayed.