One Form-Change Recordset to Different Queries on Click

[gyli84]

Is it possible to have a form whose recordset can be changed to that of a different query. I have lots of different queries all with the same fields but different criteria and what I would ideally like to be able to do is to have ONE form and then click a button (one for each query) which would then change the recordset of the form to that specific query and would run the query you would enter the criteria in the dialog box and the information on the form would be displayed. If this is possible how can it be done and what code is required?

Thanks

 

[Aivars]

me.subformName.form.recordsource="NeededQueryName"

 

[AlexMidd]

I have several of these in my databases. The simplest is a form, say Form1, with record source set to, say, "qryEquipment". There is a textbox, say txtSearchCriteria, into which a search string is entered.

Then there are several buttons, eg cmdSearchIDNo, cmdSearchDescription ......

If the user decides to enter an ID No in the text box, s/he then clicks the cmdSearchIDNo button. In the OnClick event would be the following code:

Me.RecordSource = "SELECT * FROM qryEquipment WHERE [IDNo] = txtSearchCriteria"

If they select the Equipment Description option (cmdSearchDescription) the [IDNo] part is changed to [EquipDesc]. So, you can use the above SQL with different WHERE clauses to dynamically update the records that are displayed on the form.

Alternatively you could change the recordsource of the form to a query that you have already set up with the criteria set as you wish, e.g.

Me.RecordSource = "qryEquipmentByIDNo".

I prefer the first method as it cuts down on the number of queries that are set up in the database.

Have fun! )

Alex Middleton