Open form based on different queries

[esskaykay]

I’m trying to open a form based on different queries. I have an initial form (frmFlush-NS) that prompts for N or S (north/south). If I keyin “N”, I want frmFlushMH to open based on qryFlush-N; if an S is keyed, I want it to open based on qryFlush-N.

I realize I can do this if I set frmFlushMH record source as a query that prompts for N or S. However, there are other reasons I'd rather not do that if possible.

Any suggestions would be greatly appreciated.

----

My solution:

I created two identical forms based on different queries; then open the corresponding form. This appears to be working but it seems a bit of overhead. I'd rather only have one form.

Any other suggestions would be greatly appreciated.


Thanks,
SKK

 

[pbaldy]

One way would be to test for the N vs S in the open event of the second form, and set the recordsource appropriately.

 

[Thomko]

Hello SKK,

you can set the record source of the form via VBA. You can hand over the "N" or "S" via OpenArgs and then set the record source in the Form_Load event to the query you want.

HTH
Thomas

 

[gemma-the-husky]

you have two basic choices

1. have two different forms, each based on the query

then
a) docmd.openform the correct one, based on the selection you require or
b) if you are using a subform - set the SOURCEOBJECT of the subform as appropriate

2. Have one single form, which can work with two different queries
a) docmd.openform the correct one, but change the RECORDSOURCE of this in the from open/or form load event
b) change the RECORDSOURCE of the subform as appropriate


ie recordsource one way, source object another

-----------
out of interest - since app size isnt often an issue, i often find it easier to do this with two separate forms - rather than trying to make sure that the same code is appropriate for a single form, where the recordsource changes. - since this sometmies causes issues.

 

[esskaykay]

I've been playing with this a bit and am getting pretty close. I have a few issues with closing the first form prior to opening the second but I think I'm on to something. Thank you all for the suggestions. Not being a programmer, it takes me longer to do what comes natural to you guys. I'll keep you posted...

Thanks again,
SKK

 

[esskaykay]

Looks like I'm in business. Thanks all...

SKK