Set query recordsource based on forms combo box selection

[Eprice]

Hello,
I am using Access 2010.
I have searched and cannot find a way to add a table to a query based on a forms combo box selection. The reason I want to do this is that I will never know which table the user wants to use for a report. I want to use a query because they also need to have the ability to select if they want to query by state or see all states and query by underwriter or see all underwriters and I would have no idea how to do that with an sql statement. I thought it would be easier to set up a query and have thier combo box selection determine which table the query uses. I already have the combo box that shows all tables in the database. Is this even possible. Please help, I am completely lost on this one.
Lisa

 

[dhookom]

It really sounds like your tables aren't normalized. My opinion is based on having multiple similar tables that can be interchanged in a query. You would probably need code to set the SQL property of a saved query faq701-7433.

If you really need to keep multiple tables that have similar structures and want to avoid code, you could create a union query based on all of your similar tables and add a column that identifies the source table. You can then filter this union query based on the selection from the combo box.

Duane
Hook'D on Access
MS Access MVP

 

[Eprice]

I have decided not to use a query but this code instead and it works so far. Thanks for the help tho.
Dim strUnd As String
Dim strState As String
Dim strFilter As String

' Build criteria string for Underwriter field
If IsNull(Me.PickUnderwriter.Value) Then
strUnd = "Like '*'"
Else
strUnd = "='" & Me.PickUnderwriter.Value & "'"
End If

' Build criteria string for State field
If IsNull(Me.PickState.Value) Then
strState = "Like '*'"
Else
strState = "='" & Me.PickState.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Underwriter] " & strUnd & " AND [State] " & strState

' Apply the filter and switch it on
With Reports![rptUndBranchDetail]
.Filter = strFilter
.FilterOn = True
End With

 

[dhookom]

I always build a where condition that is used in the DoCmd.OpenReport method

Dim strWhere as string
strWhere = "1=1 "
[COLOR=#4E9A06]' Build criteria string for Underwriter field[/color]
If Not IsNull(Me.PickUnderwriter) Then
   strWhere = strWhere & " AND Underwriter ='" & Me.PickUnderwriter.Value & "' "
End If
[COLOR=#4E9A06]' Build criteria string for State field[/color] 
If Not IsNull(Me.PickState) Then
  strWhere = strWhere & " AND State ='" & Me.PickState.Value & "' "
End If

DoCmd.OpenReport "rptUndBranchDetail", acViewPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP