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?