Same form - different Queries

[Sgt Bilkp]

I have a standard form and subform setup, with quite a few boxes and organisaed layout.

Trouble is, now i need to run different queries on the date (such a only show location A where equipment = x AND equipment is faulty) varying on types of equipment and locations.

Don't have a problem setting up the queries, but do i have to repeatedly make new forms and set them up, or is there a way of copy and pasting the forms and linking it back to the relevant query?

 

[georgedwilkinson]

It sounds like all you're doing is filtering on the subform, right? No reason for a new subform every time you want to filter. There are a couple of ways to handle that. Are you wanting to filter the main form based on a filter in the subform?

Elaborate, please.

 

[Sgt Bilkp]

Well, filtering on a subform is one, but only when searching for an entry (unique ID). The main work is form and subform. Let me explain...

Form contains equipment (frmMainData)
This contains Location and Type (1/2/3 1=Printer, 2=Cutter, 3=Binder) and also State (faulty/working/sold)

Subform is faults related to the equipment (frmFaults).
Nothing special in here, just fault date, # of days to repair, what the fault was, unique ref code (autonumber)

In my queries, i have variations on the equipment. For example show equipment from x location, that is type =1, and exclude State = sold. Variations include the locations (5 of) and the type (3 of). So i can have up to 15 different queries showing all equipment in all locations, excluding sold items.

I suppose what i want to be able to do ultimatelty is have a opening form, where i can select a location from a combo box, then select a type and then click a command button which opens up the main form and subform displaying only that data. I thought mutiple queries was the way to go. Perhaps not? Perhaps best just to use queries to exclude the State = Sold items?

 

[georgedwilkinson]

You say you have the field "State" on your main form. You can just right click on that during run time and filter it for whatever state you want on the main form. That would avoid all the extra queries. There are also methods of applying filters that don't require a whole new query for each filter.

Does that make sense or even apply to you?

 

[Sgt Bilkp]

You say you have the field "State" on your main form. You can just right click on that during run time and filter it for whatever state you want on the main form. That would avoid all the extra queries. There are also methods of applying filters that don't require a whole new query for each filter.

Does that make sense or even apply to you?

It does. I need to make it simple, for the varying users who will use the application. What i was thinking was a main page (switchboard - called frmMenu) with two combo boxes, one "cmbSelectLocation" and "cmbtype" which lookup values from their respective tables and then a command button to run the event.