Multiple subforms using one query

[thopp23c]

Hello all,

Is it possible to have a main form (with a record source set to a query) feed records into multiple subforms? I'm trying to limit the number of times I query the db to speed the form up. Currently around 2+ mins to load.

Background on my project:
I'm trying to display 23 different records for a surgery center status board. Each record represents a patient in a bay(room). I have accomplished this in using 23 instances of a subform and changed the record source on the main form for each. This is slow to load. Obviously. I need a subform because I also need to display small icons and show/hide them for each patient. I've also tried a continuous form with no luck on the icons. I have also optimized the queries as best as I can.


I appreciate any ideas to make this work.

Access 2010
Windows 7

[ItsMe]

Why do you need twenty-three different queries? Perhaps table structure could be optimized.

[June7]

23 instances of subform - how is that accomplished?

Maybe a single form in single record view with an unbound listbox that shows the 23 bays with occupant names, click on list item to filter form to show associated record.

[thopp23c]

As of now I have 23 queries to search for an occupant in each of the existing rooms. Whatever is returned is populated into the display. I'd like to not have 23 but 1 if possible.
I don't think it's a table structure issue especially since my tables are not local (ODBC) but belong to our cooperation. Sorry I didn't explain that initially.

I kept adding the same subform to the main form and access changes its name to ChildX. My subform code then runs for each.

I like the idea of a listbox but all bays need to be shown at once so it can be seen at any given time with no manipulation.

Hope this helps clarify.

[June7]

All bay records can be displayed in datasheet or continuous view form. Don't see how having 23 instances of a form accomplishes "all bays need to be shown at once", only one form can be seen at a time.

[thopp23c]

Well as I said in the original post, I tried using a continuous form and it does show all the records I need but I can't show/hide the icons for each. A continuous form treats them as the same.

Also, all 23 subform a are in view at the same time in my current version. Just like any other subform would be on any other form.

The trick here is having the records and control of the icons. A subform does that for me but querying 23 times is slow as Christmas.

[ItsMe]

I would look into conditional formatting. It is an intrinsic tool that can be found within the ribbon. It can be affective in continuous and datasheet view.

Maybe there is an expression that can be used to show or hide a control that represents your icon. I do not know all of the limitations of conditional formatting.

[June7]

Conditional Formatting is available to textbox and combobox but visibility is not available setting.

I still don't understand how you are viewing 23 subforms at one time. Unless the 23 forms are in tile formation, only 1 form can be seen (with others sitting in background).

[thopp23c]

June7, we must be talking about two different things. Here's a screen shot. Any ideas of how to do this best will be appreciated.

[ItsMe]

You might try bringing over the data to a temp table. Then, each of your subforms should have their own recordsource based on the temp table and a WHERE clause. For best performance, you could use a Query Def object and assign SQL to it when you requery the subforms.

[June7]

Okay, tile formation. These 23 forms are not used for data entry/edit, only display?

You said 23 'instances' of a subform. So are there 23 form objects (as seen in Navigation pane) or is there 1 form you somehow display 23 times?

It would be possible to have 23 UNBOUND forms and have VBA code open a single recordset and cycle through the recordset and populate each of the forms with one record's data.

That might be faster loading.

However, seems that 23 queries pulling only 1 record each should run relatively fast. The real issue might be your network. Might not be pulling the data is issue but compiling the graphical user interface - the forms. Try removing all queries and just opening UNBOUND form/subforms. How fast? Make copy of db first and test with copy.

[thopp23c]

I like the idea, thanks for the input. Should the temp table be set as the record source for the main form? I'm unclear if this would help load the data faster. As for the Query Def Obj, I'll create one for each sub then assign it as the record source in the code?

[June7]

Why does main form even need a RecordSource?

Did you see post 11?

[thopp23c]

June7, yes, they're display only. This is just an info board. Sorry for the confusion. Yes this is the same form shown 23 times. They're unbound and get a record source assigned during the main form load (which happens first via override with code.) This the part that takes the longest. Then I call the subforms code for each subform to show/hide icons.

As for cycling though: I'll open a query with all the records for each bay (already have one like this) and then assign one to each subform's record source?

You would think the queries would be faster but they're fairly complex (ifs, ors, ands, and join). I'll test the load with no queries to see if its the GUI.

[ItsMe]

I like the idea, thanks for the input. Should the temp table be set as the record source for the main form? I'm unclear if this would help load the data faster. As for the Query Def Obj, I'll create one for each sub then assign it as the record source in the code?

It does not quite work that way. Your main form will manage code that will execute various queries. The first thing it would do would be to delete records from a local Access table and then append new records. Apparently, there will only be 23 records to delete/append.

With your temp table in hand, each subform would need it recordsource assigned. The reason you will still want 23 different queries will be for performance reasons. Alternatively, you would use the subform's Filter property. However, the Filter property would retrieve all of the data.

Rather than creating 23 query objects, you could use one query object and assign a new SQL statement to it before execution of a specific subform's requery. I would suggest using DAO and Query Defs. So the query will be a virtual object held in memory, not an object(s) visible in the Navigation pane. Basically, VBA will manage most everything. A user could click a button to take a trip to the data and refresh the screen as well as having periodic re-queries automated.