Drop Down Menu Linking To Tables

[tristangemus]

I am creating a database storing purchase information for numerous machines. I have created tables for each machine which inside stores that information. I would like to create a splash form to display some sort of drop down menu (such as combo box) to list the forms so once clicked they will be brought to that form. Is there any way to do that? Or any better way? I am trying to avoid having to create buttons one by one for each machine since there are over 100 of them. Thank you.

[Dal Jeanis]

1) Are these tables all similar, or are they very different from one another? Typically, if they all had mostly the same kind of information, then you'd just have one combined table which had a "type" field for what kind of machine it was, then the other fields. If there were some kinds of machines that had very different information, then you might have an additional table for all the unique fields.

2) If not, then you could easily create a new "tblSplashindex" table that has machine-type, form-name as columns, and load that into a combobox cbxSplashIndex. In the AfterUpdate of cbxSplashIndex, you'd use DoCmd.OpenForm to open the form for that machine.

[Missinglinq]

Place a Combobox on your Form. When the Combobox Wizard comes up, click Cancel.

Right-Click on the Combobox and go to Properties – Other and change the Name Property from the one Access assigned to cboFormOpen.

Now go to the Data Tab and paste this into the RowSource Property:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") And (MSysObjects.Type)=-32768 ORDER BY MSysObjects.Name;

Then use this code

Private Sub cboFormOpen_AfterUpdate()
  stDocName = Me.cboFormOpen
  DoCmd.OpenForm stDocName
End Sub

When you make a selection the chosen Form should Open.

Linq ;0)>

[Dal Jeanis]

linq - does your code assume that each form to be opened is named precisely after the machine that it is intended to affect? I assumed a tblSplashIndex would be needed to relate those two items.

[Missinglinq]

That RowSource pulls all Forms in the Database.

Linq ;0)>

[tristangemus]

Thanks for the input link. Where am I putting the last bit?

Private Sub cboFormOpen_AfterUpdate()
  stDocName = Me.cboFormOpen
  DoCmd.OpenForm stDocName
End Sub

Place a Combobox on your Form. When the Combobox Wizard comes up, click Cancel.

Right-Click on the Combobox and go to Properties – Other and change the Name Property from the one Access assigned to cboFormOpen.

Now go to the Data Tab and paste this into the RowSource Property:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") And (MSysObjects.Type)=-32768 ORDER BY MSysObjects.Name;

Then use this code

Private Sub cboFormOpen_AfterUpdate()
  stDocName = Me.cboFormOpen
  DoCmd.OpenForm stDocName
End Sub

When you make a selection the chosen Form should Open.

Linq ;0)>

[Missinglinq]

In the Code Module for the Form that contains the Combobox.

Linq ;0)>

[Dal Jeanis]

OKay, so trist - that code assumes that the user can tell, from just the name of the form, what machine the form is related to. If that's the case, use linq's method. If not, you'll have to either rename the forms to make that happen, or build a table with that information, as I suggested.

[tristangemus]

OKay, so trist - that code assumes that the user can tell, from just the name of the form, what machine the form is related to. If that's the case, use linq's method. If not, you'll have to either rename the forms to make that happen, or build a table with that information, as I suggested.

Is there a way for the DB to automatically group them into one form? I require them in seperate tables for organization purposes. And we are constantly adding new parts so if it were to automatically update the big list from the individual one then I would go with that.

[Dal Jeanis]

I have no way of knowing without a lot more detail about how you are already set up. If the tables all have a naming convention in common, like they all start with a prefix, then you could load a drop-down box with the table names using code very similar to linq's post #3, and let the user select the appropriate table from a single form.