Best way to set subform datasource

[hrabbit]

Hi all,
I have two forms that use the same subform. The datasource for the subform needs to be different for each of the two forms, so I set it in an on_current event private sub in each form. It works but is this the best practice?

Also, the datasource for one of the forms is a query, while the datasource for the other form is a modification of that same query. Would it be better to:
A. Maintain two seperate queries
B. Not use queries and just set the datasource to a "Select" statement (they are very long)
C. Some other solution I haven't thought of

I tried to set the datasource for form A to "qryQueryName" and the datasource for form B to "qryQueryName" & "querymodification" but it doesn't like that.

Thank you for any and all help.
Rebecca

 

[charityg]

First, what do you mean by a "modification of the query"?

 

[hrabbit]

The query itself is "Select [cadb_PSRM Pred].[psr_scdn], [cadb_PSRM Pred].[psr_name FROM [cadb_SBR Set] INNER JOIN [cadb_PSRM Pred] ON [cadb_SBR Set].[sbr_name]=[cadb_PSRM Pred].[psr_scdn]" This is a way shortened version. That query is called qryPredsNoDup and works for form A.

For form B the above query needs to have a "Where" clause added.

So I tried to set the recordsource for the subform in form A to "qryPredsNoDup" (which works) and the recordsource for the same subform in form B to "qryPredsNoDup" & "Where [cadb_PSRM Pred].[psr_pscd])<>[cadb_SBR Set]![sbr_name]" but it doesn't seem to want to concatenate these two.

I wanted to avoid having two seperate queries but maybe that's the best solution?
Thanks,
Rebecca

 

[charityg]

Maybe this might clear it up: The end of your first query string will end with a ";" and when you try to concatenate your where clause, your probably getting an error. Try setting the recordsource for your second form= left(qryQueryName,len(qryQueryName)-1) & querymodification & ";"
This will remove the ';' from the first query and append a semicolon to the second query.Hope this helps! I'll cross my fingers for you!

 

[hrabbit]

Thanks so much for your suggestion. I've tried using it all different ways and it just won't work. No matter what I try it will not concatenate anything with a query.

The message I get is:
"Runtime error 3078
The microsoft Jet database engine cannot find the input table or query 'Left(qryPredsNoDup,Len(qryPredsNoDup) - 1)Or ((([cadb_PSRM Pred].[psr_scdn])=[cadb_PSRM Pred]![psr_pscd]));'
Make sure that it exists and that it's name is spelled correctly.

What would be your suggestion as to the best practice in this situation. Maintain two different queries?
Thanks again for your expertise.
Rebecca