I have a form that consists of about 60 fields that all update when choosing a different customer from a drop down. I feel like my current way is not the best way as it takes about 30 seconds to update when changing customers. Currently I have:
Private Sub ClientSelection_AfterUpdate()
On Error GoTo errhandler
Dim dbTemp As DAO.Database
Dim rsTemp As DAO.Recordset
Dim strSQL As String
DoCmd.Hourglass True
sYear = Me.YearSelection.Value
Me.CustomerID = Me.CustomerSelection.Column(0)
CustomerID = Mid(Me.CustomerID.Value, 2, 36)
Set dbTemp = CurrentDb()
Set rsTemp = dbTemp.OpenRecordset("SELECT Field1 FROM Table where CustomerID = '" & CustomerID & "' and MonthYear = #01/01/" & sYear & "#")
Set rsTemp2 = dbTemp.OpenRecordset("SELECT Field1 FROM Table where CustomerID = '" & CustomerID & "' and MonthYear = #02/01/" & sYear & "#")
Me.JanCharges.Value = rsTemp("Charges")
Me.FebCharges.Value = rsTemp2("Charges")
rsTemp.Close
rsTemp2.Close
Set rsTemp = Nothing
Set rsTemp2 = Nothing
Set dbTemp = Nothing
Exit_ClientSelection_AfterUpdate:
Exit Sub
errhandler:
MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
Resume Exit_ClientSelection_AfterUpdate
End Sub
Both of these commands basically happen 60 times (5 categories consisting of 12 fields(months)). Then even further down I close all the recordsets and set them to Nothing. All of this works just takes a little longer than I want. What is a more efficient to accomplish this?