Toggle Form between Queries

[seocavaz]

I have a form that is basically just a matrix/visual of a query. Is there a way to "toggle" between the use of queries/data?

i.e. have a drop down and select query, "SH-LS", "FPH-ENV" or "FPH-Asset" and it will show the corresponding data. The queries are all set up the same, just gathers/calculates different criteria.

Thanks!

[ranman256]

Not sure what you want.
If your drop down selects a query and it opens....then what do you need? Queries = Data.

[seocavaz]

I want to be able to select different queries while in the form and it update.

[ssanfu]

It sounds like you can have a combo box to select the parameter, then have one query (since they are all the same) reference the combo box to limit records.

The query would look like

SELECT field1, field2, ... fieldN
FROM MyTable
WHERE Field10 = Me.ComboBoxName

if the combo box "ComboBoxName" is on the same form.

You could post the SQL of the queries; could be a little more specific

[ranman256]

Execute the query in the combo..

docmd.openquery cboQuery

[seocavaz]

The query SQL is:

SELECT Count(IIf([risk1]="X",1,Null)) AS Risk1CT, Count(IIf([risk2]="X",1,Null)) AS Risk2CT, Count(IIf([risk3]="X",1,Null)) AS Risk3CT, Count(IIf([risk4]="X",1,Null)) AS Risk4CT, Count(IIf([risk5]="X",1,Null)) AS Risk5CT, Count(IIf([risk6]="X",1,Null)) AS Risk6CT, Count(IIf([risk7]="X",1,Null)) AS Risk7CT, Count(IIf([risk8]="X",1,Null)) AS Risk8CT, Count(IIf([risk9]="X",1,Null)) AS Risk9CT, Count(IIf([risk10]="X",1,Null)) AS Risk10CT, Count(IIf([risk11]="X",1,Null)) AS Risk11CT, Count(IIf([risk12]="X",1,Null)) AS CTRisk12CT, Count(IIf([risk13]="X",1,Null)) AS Risk13CT, Count(IIf([risk14]="X",1,Null)) AS Risk14CT, Count(IIf([risk15]="X",1,Null)) AS Risk15CT, Count(IIf([risk16]="X",1,Null)) AS Risk16CT, Count(IIf([risk17]="X",1,Null)) AS Risk17CT, Count(IIf([risk18]="X",1,Null)) AS Risk18CT, Count(IIf([risk19]="X",1,Null)) AS Risk19CT, Count(IIf([risk1]="X",20,Null)) AS Risk20CT, Count(IIf([risk1]="X",21,Null)) AS Risk21CT, Count(IIf([risk22]="X",1,Null)) AS Risk22CT, Count(IIf([risk23]="X",1,Null)) AS Risk23CT, Count(IIf([risk24]="X",1,Null)) AS Risk24CT, Count(IIf([risk25]="X",1,Null)) AS Risk25CT, Count(IIf([risk26]="X",1,Null)) AS Risk26CT, Count(IIf([risk27]="X",1,Null)) AS Risk27CT, Count(IIf([risk28]="X",1,Null)) AS Risk28CT, Count(IIf([risk29]="X",1,Null)) AS Risk29CT, Count(IIf([risk30]="X",1,Null)) AS Risk30CT, Count(IIf([risk31]="X",1,Null)) AS Risk31CT, Count(IIf([risk32]="X",1,Null)) AS Risk32CT, Count(IIf([risk33]="X",1,Null)) AS Risk33CT, Count(IIf([risk34]="X",1,Null)) AS Risk34CT, Count(IIf([risk35]="X",1,Null)) AS Risk35CT, Count(IIf([risk36]="X",1,Null)) AS Risk36CT
FROM Qry_SH_IInterval;

I basically want to change the FROM Qry... to FROM Qry_FPH_IInterval.

The big picture is a matrix that shows the totals. Instead of having a different matrix for the different charted data, they want to be able to just drop down in the form.

Thanks!!

[ssanfu]

OK, re-group.... This will take some VBA code to change the record source of the form.

I would try having the queries as saved queries."qrySH_LS", "qryFPH_ENV" and "qryFPH_Asset".
Use the combo box to select a query name; the query names would be a value list in the combo box.
Selecting a query name would change/set the form record source to that query.

[seocavaz]

ok, I already have them as separate queries, so Ill give it a shot with the combo box.