Load same form with different queries

[tagteam]

Is is possible to load the same from but with different query based on user button selection. I have a form that loads from a query where the data is in SQL server but when I use the where condition in vba to load the form it takes a long time. If i put the form variable in the query as a criteria it loads quickly. I need to have 5 different criteria but only one at a time. when it tried to put all the data in the query as or conditions it was also slow.



Is it possible for user to select button 1 and load form 1 with query 1. If user selects button 2 it loads form 1 but with query 2 etc?

[June7]

Yes, code can set the form RecordSource property. Pass query name to form with OpenArgs and use form Open event to set its RecordSource.

[tagteam]

ok, thanks, I will research that and give it a try tomorrow.
Thanks

[Gicu]

Consider an alternative approach using an option group to make the selection and only one button.
Cheers,

[tagteam]

So you saying use the form RecordSource property but have a combo box that allows a user to select Railcarid, customerid, vendorid, or car number and a single button to open the form. Then using the combo box selection use that to kick off the correct RecordSource property. I like that idea, I will research that as well. I will post back later today if I have any problems. I have to do some searching first and figure out how to do the RecordSource thing and put it in a case statement or if else loop i guess.

[Gicu]

If you want to load only one of those ID's you will need a combo for the ID and another (or an option group - https://docs.microsoft.com/en-us/off...ss.optiongroup) for the record source type. Once you select the option (RailcarId, CustomerID, etc.) in the option group you reset the row source of the selection combo to include the ID and the description columns (the ID being hidden). And finally in the AfterUpdate event of the selection combo you set the record source of the form itself.

Cheers,

[tagteam]

Hey guys, i guess I am a little confused. From what you have said and I have researched you open a form/report and in the on load event you enter in the code to choose the recordset. I guess I am confused because it seems that if you are selecting the recordset in the on load of the form then you would have to make a copy of the form and save it to have a different on load code. Then you would have the same form but have to have it saved 4 or 5 times with different names.

Can someone please help me with what I am missing here.

[Micron]

When you open the form/report this way, you assign the recordsource property when an event runs. That event is usually one that belongs to a control on a form that you use to trigger the opening of the form/report. So it might be a button click or perhaps AfterUpdate event of a combo. IMO, the button is better as a trigger because you can make the wrong selection in a combo and then things happen when you don't want them to. So a bunch of option buttons is one way to select what it is you want to see. However, each time you need a new option, you're redesigning the form. That's why I'd tend to use a combo - easy to add an item to the list.

See if this is something that you might want to use
http://allenbrowne.com/ser-62.html

EDIT - In case I wasn't clear, no you do not create several forms or reports that look the same except for the records you see. The search form link shows a common method of filtering records when using the same form/report object.

[Gicu]

Not at all, you only need one form, please have a look at the attached sample I just created for you.

Cheers,

[June7]

So you want to build 5 queries with dynamic parameters that reference controls on a form for input? And on this form user will select which output, perhaps with combobox or listbox. Code behind this form will open another form (or a report) and pass value from combobox via OpenArgs for the desired output, like:

DoCmd.OpenForm "formname", , , , , , Me.comboboxname

Then code behind other form in Open event:

Me.RecordSource = Me.OpenArgs

[tagteam]

Thanks gicu for the quick db, but i think i must not have explained to well. I need it to open in a separate form. More like what June 7 is saying.
I have the form f_AllVendorInvoices and users have a drop down box to select VendorNumber, Railcar, customer, or InvoiceNumber. Then when they select or enter what they want it will search the table for those records and open them in a continuous form. I need it to be a contiuous form and not worksheet because I have buttons on those line item to open further reports.

I will try like what June7 says and make 5 queries and try to use them to open the same form.

[tagteam]

In trying to work on this I am thinking more info might be helpful. Below is a picture of my form:

Attachment 47467

A user can select a value from the dropdown boxes. They usually only search by one criteria so that is what I am working on now. (i do have a query that allows search by multiple criteria and it is slow but since they don't use it much they said it is fine)

So for example the user enters a railcar. Then I want to have a button or dropdown that will open the form f_AllVendorInvoices but will use the query q_AllVendorInvoices_RailcarID as the source. I do that because I can then use the form field box value as the criteria in the query and it will open very quickly. (If I open the form with a where condition it takes almost 30 seconds and I have a different post on that point)

So the options would be like this:
1. User is searching for vendor number so they select a vendor number from the dropdown. It then opens f_AllVendorInvoices using the query q_AllVendorInvoices_VendorNumber
2. User is searching for RailcarID so they select a Railcar from the dropdown. It then opens f_AllVendorInvoices using the query q_AllVendorInvoices_RailcarID
3. User is searching for Customer so they select a Customer from the dropdown. It then opens f_AllVendorInvoices using the query q_AllVendorInvoices_Customer
4. User is searching for InvoiceNumber so they select a Invoice from the dropdown. It then opens f_AllVendorInvoices using the query q_AllVendorInvoices_InvoiceNumber

I hope that makes it more clear on what I am trying to do, and I am definitely open to suggestions for better or other ways.

[June7]

I tried to open image and get error "invalid attachment".

Did you try the approach I described?

[tagteam]

I can see the attachment when i click on the link, not sure what else to do so i will include a onedrive link Form.PNG

ok, here is what I have done.
I created 4 queries each with a criteria set to one of the field boxes.
I created a button (starting with that instead of dropdown) and put this in the on click event -= DoCmd.OpenForm "f_AllVendorInvoices_Dynamic", acNormal, , , , , Me.RailCarID

BUT HERE IS WHERE I AM CONFUSED
The form f_AllVendorInvoices_Dynamic has a record source of q_AllVendorInvoicesDynamic, this is query that has the data I need but no criteria.
I am not sure how this form is to know what query to use just from putting Me.RecordSource = Me.OpenArgs in the on open

I need the form f_AllVendorInvoices_Dynamic to open and show all the data in the query q_AllVendorInvoices_Dynamic filtering on the RailcarID (In this case)
Then I need to button to do the same but for VendorInvoice, Customer, InvoiceNumber

[tagteam]

I can see the attachment when I click on the link , not sure why you cant so I will include a one drive link: Form.PNG

Here is what I have done so far:
I created 4 queries each with a criteria set to one of the field boxes
I created a button (starting with that instead of dropdown) and put this in the on click event - DoCmd.OpenForm "f_AllVendorInvoices_Dynamic", acNormal, , , , , Me.RailCarID

BUT HERE IS WHERE I AM CONFUSED

The form f_AllVendorInvoices_Dynamic has a record source of q_AllVendorInvoicesDynamic, this is query that has the data I need but no criteria. I am not sure how this form is to know what query to use just from putting Me.RecordSource = OpenArgs in the on open event.

I need the form f_AllVendorInvoices_Dynamic to open and show all the data in the query q_AllVendorInvoices_Dynamic filtering on the RailcarID (In this case)<br>Then I need to button to do the same but for VendorInvoice, Customer, InvoiceNumber