Open Form with Different Record Sources

[Guest]

I have 2 queries based on the same table relationships. One of them has an
additional criteria. I would like to be able to display the results of either
query in the same form. I though that this would work..

DoCmd.OpenForm "frmMiscForm" , acNormal, "qryTest1" (filtername refers to
a query)

...but I was wrong.

I also saw something in the forums about specifying the RecordSource =
"qryTest1" in the OpenArg portion of the OpenForm command... but again, I
could not make it work.

The only way I have been able to get what I want is to put the query name in
the Record Source for the Form, itself... but that means I would have to
create a form for each query... basically the exact same form. That seems way
too messy.

Any ideas?
Thanks!
patrick

 

[Guest]

Take all the filtering criteria out of your query.
Programmatically build the Where condition you would use.
Open the form using the Where argument, not the Filter argument:

If Me.chkSomeThing = True Then
strWhere = "[Customer] = '" & Me.txtCustomer & "'"
Else
strWhere = "[Customer] = '" & Me.txtCustomer & "' AND [Rating] <= 600"
End If
DoCmd.OpenForm "frmMiscForm" , acNormal, , strWhere

The above assumes the field Customer in your recordset is text and Rating is
numeric.

 

[fredg]

I have 2 queries based on the same table relationships. One of them has an
additional criteria. I would like to be able to display the results of either
query in the same form. I though that this would work..

DoCmd.OpenForm "frmMiscForm" , acNormal, "qryTest1" (filtername refers to
a query)

..but I was wrong.

I also saw something in the forums about specifying the RecordSource =
"qryTest1" in the OpenArg portion of the OpenForm command... but again, I
could not make it work.

The only way I have been able to get what I want is to put the query name in
the Record Source for the Form, itself... but that means I would have to
create a form for each query... basically the exact same form. That seems way
too messy.

Any ideas?
Thanks!
patrick

Leave the Recordsource of the frmMiscForm blank.
Is there some criteria that will let Access know which filter to use?

Here are a couple of methods.

1) Code the Command button on that first form:
DoCmd.OpenForm "frmMiscForm"
If SomeCriteria = "XXXX" Then
forms!frmMiscForm.Recordsource = "qryTest1"
Else
forms!frmMiscForm.Recordsource = "Your normal recordsource"
End If

Alternatively, you can use:

2) If SomeCriteria = "XXXX" Then
DoCmd.OpenForm "frmMiscForm", , , , , , "qryTest1"
Else
DoCmd.OpenForm "frmMiscForm", , , , , , "Your normal recordsource"
End If

Then code the Open Event of frmMiscForm:
Me.Recordsource = Me.OpenArgs