Use one form for multiple seperate queries

[Miff3436]

Hi all,

I am currently using MS Access 2007 and am trying to reduce the amount of userforms in my database. I have one large table that imports each time the db is opened.

From here I have a form with 5 separate command buttons ( like a switchboard) each of these buttons opens a form. These forms are identical and each one returns a different query, the querys all come from the same master table and the only difference is one field.

Is there a way I can have just the one form and when the user selects the command button it runs that query in the form.

Thanks Matt

 

[plog]

Sounds like you are applying criteria in the wrong spot. My guess is you should only have 1 query and then apply criteria to the form.

Let's say you had a company directory database and wanted to have a form that listed all people of a department. You wouldn't have 1 query for each department (Accounting, IT, Sales, etc.). Instead your query would show all your data and you would base your form on that. When someone wanted to see just the Accounting department you would have an 'Accounting' department on your main form and use a DoCmd.OpenForm (https://msdn.microsoft.com/en-us/library/office/ff820845.aspx) to open the department form. The last argument of the DoCmd.OpenForm allows you to pass criteria to filter how you are opening that form. In it you could pass it code so that it opens to just the Accounting department.

So, 1 query, 1 form and you use code to filter how you open that form.

 

[Miff3436]

Hi Plog,

Thanks for the response. That makes perfect sense, a case of not looking past the end of my nose. Each command button has this code but with a change to the line description value, works fine.

 Private Sub Command7_Click()
 DoCmd.OpenForm "frmMain", , , "LineDescription = 'Bakerloo'"
 End Sub

Thanks for your help