Multiple subforms using one query

[thopp23c]

Sorry I was typing my other reply. The main form doesn't need one now but I thought it could help to just query one time then somehow push the data to the subforms.

[thopp23c]

Thanks! I'll give it a shot. May take a few days to get to it but I'll let you guys know if it works.

[ItsMe]

Thanks! I'll give it a shot. May take a few days to get to it but I'll let you guys know if it works.

I am not quite sure what approach you are going to try to implement. It seems June7 has ideas that are different from mine. I do not see a way of avoiding a temp table. I would test the speed of your query that creates the temp table. This may still prove to be a bottleneck. If you have a chance to get with a DBA, they may be able to provide you with a stored procedure or a view that would speed up writing records to your temp table. Maybe a surgeon could sponsor a message to tech.

[thopp23c]

I'm going to load one query with all my bay data (patient or no) then step through each record and assign it to each subform. Not sure what property/method I need to use yet. If that doesn't speed it up enough then I create the temp table and use it as the data source to be stepped through. Even if my query or temp table that brings all my data back is slow it won't be as slow as loading 23! If this doesn't work I'm considering trying to use a different platform. I know access isn't the best for displaying this much at once. Any suggestions if I need to change to a different app?

[June7]

Maybe VB.Net as frontend. I played around a little with VB6 but VB.net has supplanted it. However, not sure it would load data any faster. But what you know about VBA language/syntax coding can carry over to VB6 and to some degree VB.net, at least should speed up learning curve.

[thopp23c]

stepped through my one query and used the getrows method to put all data in an array then into my subforms. I just loaded it only took like 15 seconds! Was 2+ MINS! That's such a huge performance increase! We can def implement this now. Thanks a ton guys!

[June7]

I don't think need the intermediate step of populating an array. However, probably doesn't take much time.

Glad you have solution.

[thopp23c]

I couldn't find another way to get it and assign it to my subforms. Any ideas? If not it's ok. Runs fast.

Getrows is the MS support suggested way to extract multiple rows of data.

[June7]

Never used GetRows, was not even aware of it. If you want to provide your code, will analyze.

[thopp23c]

This is just my sub that loads the data:

Sub loadData()
    Dim rsBays As DAO.Recordset
    Dim strSQL As String
    Dim intI As Integer
    Dim strBay As String
    Dim varRecords As Variant
    Dim intNumReturned As Integer
    Dim intNumColumns As Integer
    Dim intColumn As Integer
    Dim intRow As Integer
    
    
    'On Error GoTo ErrorHandler
    On Error Resume Next
   'Open a recordset on all records from the bays query
   Set rsBays = CurrentDb.OpenRecordset("SDS Board Bays")
   'If the recordset is empty, exit.
   If rsBays.EOF Then Exit Sub
    'get rows, put into array
    varRecords = rsBays.GetRows(23)
        intNumReturned = UBound(varRecords, 2) + 1
        intNumColumns = UBound(varRecords, 1) + 1
 
        For intRow = 0 To intNumReturned - 1
            For intColumn = 0 To intNumColumns - 1
                'Debug.Print varRecords(intColumn, intRow)
            Next intColumn
        Next intRow
        
    'fill subforms with data
    'varRecords(intField, intRecord)
    Debug.Print varRecords(3, 0)
    Debug.Print varRecords(4, 0)
    Debug.Print varRecords(5, 0)
    Debug.Print varRecords(7, 0)
    Debug.Print varRecords(8, 0)
    Debug.Print varRecords(9, 0)
    Me![Child1].Form.[Pat Name] = varRecords(3, 0)
    Me![Child1].Form.[Start Time] = varRecords(4, 0)
    Me![Child1].Form.[Procedure] = varRecords(5, 0)
    Me![Child1].Form.[PATNO] = varRecords(7, 0)
    Me![Child1].Form.[appt_id] = varRecords(8, 0)
    Me![Child1].Form.[birthdate] = varRecords(9, 0)
    
    Me![Child2].Form.[Pat Name] = varRecords(3, 1)
    Me![Child2].Form.[Start Time] = varRecords(4, 1)
    Me![Child2].Form.[Procedure] = varRecords(5, 1)
    Me![Child2].Form.[PATNO] = varRecords(7, 1)
    Me![Child2].Form.[appt_id] = varRecords(8, 1)
    Me![Child2].Form.[birthdate] = varRecords(9, 1)
    
    Me![Child3].Form.[Pat Name] = varRecords(3, 2)
    Me![Child3].Form.[Start Time] = varRecords(4, 2)
    Me![Child3].Form.[Procedure] = varRecords(5, 2)
    Me![Child3].Form.[PATNO] = varRecords(7, 2)
    Me![Child3].Form.[appt_id] = varRecords(8, 2)
    Me![Child3].Form.[birthdate] = varRecords(9, 2)
    
    Me![Child4].Form.[Pat Name] = varRecords(3, 3)
    Me![Child4].Form.[Start Time] = varRecords(4, 3)
    Me![Child4].Form.[Procedure] = varRecords(5, 3)
    Me![Child4].Form.[PATNO] = varRecords(7, 3)
    Me![Child4].Form.[appt_id] = varRecords(8, 3)
    Me![Child4].Form.[birthdate] = varRecords(9, 3)
    
    Me![Child5].Form.[Pat Name] = varRecords(3, 4)
    Me![Child5].Form.[Start Time] = varRecords(4, 4)
    Me![Child5].Form.[Procedure] = varRecords(5, 4)
    Me![Child5].Form.[PATNO] = varRecords(7, 4)
    Me![Child5].Form.[appt_id] = varRecords(8, 4)
    Me![Child5].Form.[birthdate] = varRecords(9, 4)
    
    Me![Child6].Form.[Pat Name] = varRecords(3, 5)
    Me![Child6].Form.[Start Time] = varRecords(4, 5)
    Me![Child6].Form.[Procedure] = varRecords(5, 5)
    Me![Child6].Form.[PATNO] = varRecords(7, 5)
    Me![Child6].Form.[appt_id] = varRecords(8, 5)
    Me![Child6].Form.[birthdate] = varRecords(9, 5)
    
    Me![Child7].Form.[Pat Name] = varRecords(3, 6)
    Me![Child7].Form.[Start Time] = varRecords(4, 6)
    Me![Child7].Form.[Procedure] = varRecords(5, 6)
    Me![Child7].Form.[PATNO] = varRecords(7, 6)
    Me![Child7].Form.[appt_id] = varRecords(8, 6)
    Me![Child7].Form.[birthdate] = varRecords(9, 6)
    
    Me![Child8].Form.[Pat Name] = varRecords(3, 7)
    Me![Child8].Form.[Start Time] = varRecords(4, 7)
    Me![Child8].Form.[Procedure] = varRecords(5, 7)
    Me![Child8].Form.[PATNO] = varRecords(7, 7)
    Me![Child8].Form.[appt_id] = varRecords(8, 7)
    Me![Child8].Form.[birthdate] = varRecords(9, 7)
    
    Me![Child9].Form.[Pat Name] = varRecords(3, 8)
    Me![Child9].Form.[Start Time] = varRecords(4, 8)
    Me![Child9].Form.[Procedure] = varRecords(5, 8)
    Me![Child9].Form.[PATNO] = varRecords(7, 8)
    Me![Child9].Form.[appt_id] = varRecords(8, 8)
    Me![Child9].Form.[birthdate] = varRecords(9, 8)
    
    Me![Child10].Form.[Pat Name] = varRecords(3, 9)
    Me![Child10].Form.[Start Time] = varRecords(4, 9)
    Me![Child10].Form.[Procedure] = varRecords(5, 9)
    Me![Child10].Form.[PATNO] = varRecords(7, 9)
    Me![Child10].Form.[appt_id] = varRecords(8, 9)
    Me![Child10].Form.[birthdate] = varRecords(9, 9)
    
    Me![Child11].Form.[Pat Name] = varRecords(3, 10)
    Me![Child11].Form.[Start Time] = varRecords(4, 10)
    Me![Child11].Form.[Procedure] = varRecords(5, 10)
    Me![Child11].Form.[PATNO] = varRecords(7, 10)
    Me![Child11].Form.[appt_id] = varRecords(8, 10)
    Me![Child11].Form.[birthdate] = varRecords(9, 10)
    
    Me![Child12].Form.[Pat Name] = varRecords(3, 11)
    Me![Child12].Form.[Start Time] = varRecords(4, 11)
    Me![Child12].Form.[Procedure] = varRecords(5, 11)
    Me![Child12].Form.[PATNO] = varRecords(7, 11)
    Me![Child12].Form.[appt_id] = varRecords(8, 11)
    Me![Child12].Form.[birthdate] = varRecords(9, 11)
    
    Me![Child13].Form.[Pat Name] = varRecords(3, 12)
    Me![Child13].Form.[Start Time] = varRecords(4, 12)
    Me![Child13].Form.[Procedure] = varRecords(5, 12)
    Me![Child13].Form.[PATNO] = varRecords(7, 12)
    Me![Child13].Form.[appt_id] = varRecords(8, 12)
    Me![Child13].Form.[birthdate] = varRecords(9, 12)
    
    Me![Child14].Form.[Pat Name] = varRecords(3, 13)
    Me![Child14].Form.[Start Time] = varRecords(4, 13)
    Me![Child14].Form.[Procedure] = varRecords(5, 13)
    Me![Child14].Form.[PATNO] = varRecords(7, 13)
    Me![Child14].Form.[appt_id] = varRecords(8, 13)
    Me![Child14].Form.[birthdate] = varRecords(9, 13)
    
    Me![Child15].Form.[Pat Name] = varRecords(3, 14)
    Me![Child15].Form.[Start Time] = varRecords(4, 14)
    Me![Child15].Form.[Procedure] = varRecords(5, 14)
    Me![Child15].Form.[PATNO] = varRecords(7, 14)
    Me![Child15].Form.[appt_id] = varRecords(8, 14)
    Me![Child15].Form.[birthdate] = varRecords(9, 14)
    
    Me![Child16].Form.[Pat Name] = varRecords(3, 15)
    Me![Child16].Form.[Start Time] = varRecords(4, 15)
    Me![Child16].Form.[Procedure] = varRecords(5, 15)
    Me![Child16].Form.[PATNO] = varRecords(7, 15)
    Me![Child16].Form.[appt_id] = varRecords(8, 15)
    Me![Child16].Form.[birthdate] = varRecords(9, 15)
    
    Me![Child17].Form.[Pat Name] = varRecords(3, 16)
    Me![Child17].Form.[Start Time] = varRecords(4, 16)
    Me![Child17].Form.[Procedure] = varRecords(5, 16)
    Me![Child17].Form.[PATNO] = varRecords(7, 16)
    Me![Child17].Form.[appt_id] = varRecords(8, 16)
    Me![Child17].Form.[birthdate] = varRecords(9, 6)
    
    Me![Child18].Form.[Pat Name] = varRecords(3, 17)
    Me![Child18].Form.[Start Time] = varRecords(4, 17)
    Me![Child18].Form.[Procedure] = varRecords(5, 17)
    Me![Child18].Form.[PATNO] = varRecords(7, 17)
    Me![Child18].Form.[appt_id] = varRecords(8, 17)
    Me![Child18].Form.[birthdate] = varRecords(9, 17)
    
    Me![Child19].Form.[Pat Name] = varRecords(3, 18)
    Me![Child19].Form.[Start Time] = varRecords(4, 18)
    Me![Child19].Form.[Procedure] = varRecords(5, 18)
    Me![Child19].Form.[PATNO] = varRecords(7, 18)
    Me![Child19].Form.[appt_id] = varRecords(8, 18)
    Me![Child19].Form.[birthdate] = varRecords(9, 18)
    
    Me![Child20].Form.[Pat Name] = varRecords(3, 19)
    Me![Child20].Form.[Start Time] = varRecords(4, 19)
    Me![Child20].Form.[Procedure] = varRecords(5, 19)
    Me![Child20].Form.[PATNO] = varRecords(7, 19)
    Me![Child20].Form.[appt_id] = varRecords(8, 19)
    Me![Child20].Form.[birthdate] = varRecords(9, 19)
    
    Me![Child21].Form.[Pat Name] = varRecords(3, 20)
    Me![Child21].Form.[Start Time] = varRecords(4, 20)
    Me![Child21].Form.[Procedure] = varRecords(5, 20)
    Me![Child21].Form.[PATNO] = varRecords(7, 20)
    Me![Child21].Form.[appt_id] = varRecords(8, 20)
    Me![Child21].Form.[birthdate] = varRecords(9, 20)
    
    Me![Child22].Form.[Pat Name] = varRecords(3, 21)
    Me![Child22].Form.[Start Time] = varRecords(4, 21)
    Me![Child22].Form.[Procedure] = varRecords(5, 21)
    Me![Child22].Form.[PATNO] = varRecords(7, 21)
    Me![Child22].Form.[appt_id] = varRecords(8, 21)
    Me![Child22].Form.[birthdate] = varRecords(9, 21)
    
    Me![Child23].Form.[Pat Name] = varRecords(3, 22)
    Me![Child23].Form.[Start Time] = varRecords(4, 22)
    Me![Child23].Form.[Procedure] = varRecords(5, 22)
    Me![Child23].Form.[PATNO] = varRecords(7, 22)
    Me![Child23].Form.[appt_id] = varRecords(8, 22)
    Me![Child23].Form.[birthdate] = varRecords(9, 22)
    
    rsBays.Close
    CurrentDb.Close
    Set rsBays = Nothing
    Erase varRecords

[ItsMe]

How can you be sure varRecords(3, 0) belongs to Child1 vs. belonging to Child12?

[thopp23c]

Good question. The query SDS Board Bays creates records for all 23 bays (in order) and only populates if a patient is in the bay.

[ItsMe]

OK, the other thing I would look out for is whether or not your Recordset contains all records prior to assigning varRecords.

When you do the following ...
Set rsBays = CurrentDb.OpenRecordset("SDS Board Bays")

Your recordset object will not be populated with all of the records at once. It will default to a dynaset which will only retrieve a portion of the records, at first. Things like .MoveNext or .Movelast will populate the recordset with additional records, as needed. IIRC, this is the opposite of a snapshot. The constant for a snapshot is dbOpenSnapshot

Having said that, the fact that you hardcoded 23 in your rsBays.GetRows(23) may force all available records into memory. I usually use rsBays.Movelast if I need a dynaset and all records available in memory.

You may want to consider not using the multidimensional array. You already have a recordset. Since you are hardcoding everything you could simply use the indexes of your existing recordset.

So you could do something like
rsBays.Movefirst
Me![Child1].Form.[Pat Name] = rsBays.Fields(0)
'or
Me![Child1].Form.[Pat Name] = rsBays.Fields("[Pat Name]")
'or
Pat NameMe![Child1].Form.[Pat Name] = rsBays![Pat Name]

...

rsBays.MoveNext


...

[June7]

Consider:

Sub loadData()
Dim rsBays As DAO.Recordset
Dim x As Integer
'Open a recordset on all records from the bays query
Set rsBays = CurrentDb.OpenRecordset("SDS Board Bays")
While Not rsBays.EOF
    x = x + 1
    With Me.Controls("Child" & x).Form
    .[Pat Name] = rsBays![Pat Name]
    .[Start Time] = rsBays![Start Time]
    .[Procedure] = rsBays![Procedure]
    .[PATNO] = rsBays![PATNO]
    .[appt_id] = rsBays![appt_id]
    .[birthdate] = rsBays![birthdate]
    End With
    rsBays.MoveNext
Wend
rsBays.Close
Set rsBays = Nothing
End Sub

Advise not to use spaces in naming convention.

[thopp23c]

Thanks much! I will implement the new code.