Using a List Box or Combo Box to select a particular query

[saltmarsh]

I'm using Access 2010 and using VBA I'd like to have the selection that I make in a List or Combo box determine which query to run.

For instance, if I select Group, I'd like the query that uses a group factor (calculation) to append form data to table A. If I select Division, I'd like the query that uses a division factor (calculation) to append form data to table A.

I need to use different queries because not only will the query load data from a form, it will also do a calculation based on a factor that can change based on the selection. This calculation is then also loaded into table A in an Adjustment amount field.

Also, just to make things complicated, I'll have approximately 20 different factors so I thought using different queries would be best to keep it all straight.

Any guidance and examples anyone can provide would be appreciated.

Thanks.

[Seth Schrock]

You could either use a Select Case statement in VBA and test for the value selected to determine which query to run, or you could setup a table that has two fields: the field that you want to view and another field that has the name of the query that would be ran for that selection. Based on the fact that you have 20 different options, I would probably go with the latter option. If you choose this option, set your column count on your listbox or combobox to 2 and the column width to 1;0 (the first number doesn't matter, so change it to whatever fits your names, but leave the second number at 0 so it doesn't show). Then you can put a button on the form that runs the query based on your selection.

If Me.Control_Name <> "" Then
    DoCmd.OpenQuery QueryName:=Me.Control_Name.Column(1)
End If

[saltmarsh]

Thank you. Both are great ideas. I'll probably pursue the Select Case statement as these may have more than one condition. By chance would you have any best case examples?

Thank you again.

[Seth Schrock]

The basic syntax for the Select Case statement is

Select Case Me.Control_Name
    Case "Division"
        'Open query for division

    Case "other option"
        'Do something else here

End Select

You would just have to add a Case for each option in your list box/combo box.

[saltmarsh]

Perfect. This all points me in the right direction. Thank you very much for the advice.