Speeding up the loading of multiple cascading combo boxes

[strager]

I have some forms that have multiple cascading combo boxes. When a user selects an item in the listbox then I fill the form. The problem is
Problem is... the screen flickers while loading, because there are so many combo boxes. I have tried hiding the fields while loading and setting the tabcontrol to another tab thinking it would take the focus off the first tab loading. I've also tried loading only the first page of the tab and then loading the rest when they click on a tab, Again, the first page holds all the main information.
I thought the maybe if I set the value and then requery in the query that would help, but no change.

This is a screen shot of one of the forms I have. There are 7 cascading combo boxes.

The way the load starts is

Each combo box has a rowsource similar to

SELECT tblBankTransactionTypes.BankTransTypeID,
tblBankTransactionTypes.DeductWithdrawID,
tblBankTransactionTypes.TransType, tblBankTransactionTypes.BankID
FROM tblDeductOrWithdraw INNER JOIN tblBankTransactionTypes ON
tblDeductOrWithdraw.DeductWithdrawID = tblBankTransactionTypes.DeductWithdrawID
WHERE (((tblBankTransactionTypes.DeductWithdrawID)=1) AND
((tblBankTransactionTypes.BankID)=[Forms]![frmCustomNav]![fSubForm].[Form]![cboBank]))
ORDER BY tblBankTransactionTypes.TransType;

All the combo boxes have their row source set using the query

When someone clicks on the listbox on the left the following procedure is called.

Private Sub lstSearch_Click()
Dim LBillID As Long

LBillID = Field2Long(Me.lstSearch)
Me.txtBillID = LBillID
If Not LBillID > 0 Then
    LBillID = Me.txtBillID  'needs to be set because its coming from doclickonsearch procedure
End If

If LBillID > 0 Then
    Me.fSubBillDueDates.Form.Requery
    LaunchBillForm LBillID
  
Else
     MsgBoxNoRecordSelected
End If
Me.txtInvoiceNumb.SetFocus
    
End Sub

Then the following is executed

On Error GoTo ErrorHandler



 Set sFrm = ReturnCustomNavForm
 If sFrm Is Nothing Then
    
    MsgBox "No form in launchbillform"
    Exit Sub
 End If


 sFrm.tbBills.Value = 3 'try to hide loading


Application.Echo False
 
 s = "select * from tblBills where billid = " & LBillID & " "
  
   Set rs = CurrentDb.OpenRecordset(s)

    If Not rs.EOF Then
        
        With rs

            sFrm.txtConName = Field2String(!ConName)
            sFrm.txtConEmail = Field2String(!ConEmail)
            sFrm.txtConPhone = Field2String(!ConPhone)
            sFrm.txtConExt = Field2String(!ConExt)
            
            sFrm.txtBillID = LBillID
            sFrm.cboPerson.Value
            SetComboBoxValueLong sFrm.cboPersonID, Field2Long(!PersonID)
            SetComboBoxValueLong sFrm.cboBillingPeriod, Field2Long(!BillingPeriodID)
            SetComboBoxValueLong sFrm.cboPaymentTypeID, Field2Integer(!PaymentMethodID)
            SetComboBoxValueLong sFrm.cboCategoryID, Field2Long(!BillCategoryID)
            'sFrm.cboBillName.SetFocus
            'sFrm.cboBillName.Requery
            SetComboBoxValueLong sFrm.cboBillName, Field2Long(!BillNameID)

            SetComboBoxValueLong sFrm.cboBank, Field2Long(!BankID)
            'sFrm.cboTransType.SetFocus
            'sFrm.cboTransType.Requery
            'sFrm.cboAccount.SetFocus
            'sFrm.cboAccount.Requery
            SetComboBoxValueLong sFrm.cboTransType, Field2Long(!BankTransTypesID)
            SetComboBoxValueLong sFrm.cboAccount, Field2Long(!AccountID)

            SetComboBoxValueLong sFrm.cboExpenseType, Field2Long(!ExpenseTypeID)
            
            sFrm.txtInvoiceNumb = Field2String(!InvoiceNumb)
            sFrm.txtAmt = !BillAmt

            sFrm.txtStartDate = !StartDate
            sFrm.txtRecurDateTo = !EndDate
            sFrm.txtDayToPayOn = !PayOnDay
            sFrm.txtNumbOfDays = !NumbOfDays
            SetComboBoxValueLong sFrm.cboDayOfWeek, Field2Long(!DayOfWeekID)
            SetComboBoxValueLong sFrm.cboWeekNumberID, Field2Long(!WeekNumberID)
            
            sFrm.txtAdd1 = !Address1
            sFrm.txtAdd2 = !Address2
            sFrm.txtCity = !City
            sFrm.txtZip = !Zip
            SetComboBoxValueLong sFrm.cboStateID, Field2Long(!StateID)

            sFrm.txtMailAdd1 = !MailAdd1
            sFrm.txtMailAdd2 = !MailAdd2
            sFrm.txtMailCity = !MailCity
            sFrm.txtMailZip = !MailZip
            SetComboBoxValueLong sFrm.cboMailStateID, Field2Long(!MailStateID)
            
            sFrm.chkAlert = !Alert
            sFrm.txtAlertNumb = Nz(!AlertNumber, 0)
            
            SetComboBoxValue sFrm.cboAlertDuration, Nz(!AlertNumber, 0)
            
            sFrm.chkRecur = !BillRecur
            If !BillRecur Then
                sFrm.txtRecurDateTo.Enabled = True
            End If
            sFrm.txtNotes = !BillNotes

            sFrm.txtBillAccountNumb = Field2String(!BillAccountNumb)
            sFrm.txtBillPinNumb = Field2String(!BillPinNumb)
            sFrm.txtBillPhonePass = Field2String(!BillPhonePass)
            sFrm.txtBillUserName = Field2String(!BillUserName)
            sFrm.txtBillPassword = Field2String(!BillPassword)
            sFrm.txtBillOther = Field2String(!BillOther)

        End With
  
    End If
  
ExitHandler:
    rs.Close
    Set rs = Nothing

    sFrm.tbBills.Value = 0  'set the tab back to the first tab

    Application.Echo True

Exit Sub

ErrorHandler:
'Application.Echo True

    clsErrorHandler.HandleError "modBills", "LaunchBillForm", True
    Resume ExitHandler

I am hoping someone has a way that I can load this information without the form flickering. Any suggestions etc. truly appreciated

 

[Pat Hartman]

Why is criteria for a specific bank included in the RowSource of the combo?

 

[strager]

Why is criteria for a specific bank included in the RowSource of the combo?

There are different transaction types with banks, i.e. deposit, credit, transfer, automatic deposit, payment etc. Also there are different account types for a bank... like savings account, checking, 401k etc. People table and the Bank table are the top tier tables.

 

[CarlettoFed]

You should attach a sample file to actually see what you have achieved and give a judgement.

 

[CJ_London]

One option is to turn painting off before you start updating the rowsources and then on again when finished - this only affects the form and not the whole screen

Not sure why you need a function field2string or field2long

And a lot of what you appear to be doing looks like it can be achieved with little or no code - review subform linkchild/master properties and using criteria in row sources to reference form controls, often called cascading combos

 

[strager]

One option is to turn painting off before you start updating the rowsources and then on again when finished - this only affects the form and not the whole screen

Not sure why you need a function field2string or field2long

And a lot of what you appear to be doing looks like it can be achieved with little or no code - review subform linkchild/master properties and using criteria in row sources to reference form controls, often called cascading combos

 

[strager]

The main form isn't bound, only the sub form. I made my own navigation form where all forms are hosted. I need to be able to handle the main form in code otherwise I get lots of errors.

The field2string, field2long, field2integer, field2single I use to check for null and empty values so that it doesn't error. There may be a better way. I am from the old school and haven't used access in about 10 years

Public Function Field2String(ByVal v As Variant) As String

If IsNull(v) Or IsEmpty(v) Or v = "" Then
    Field2String = ""
    Exit Function
ElseIf Len(v) > 0 Then
    Field2String = CStr(v)
End If

End Function

 

[CJ_London]

The main form isn't bound, only the sub form

not sure what your point is. I can't see your design so not clear what is the main form and what is the subform, I assumed your listbox (below 'find bill') is on main form and you want to display related data to the right in a subform.

not sure about isEmpty (would think if it was your isnull would fail) but you can use the nz function

nz(myfield,"") or to return a numeric value nz(myfield,0)

 

[strager]

not sure what your point is. I can't see your design so not clear what is the main form and what is the subform, I assumed your listbox (below 'find bill') is on main form and you want to display related data to the right in a subform.

not sure about isEmpty (would think if it was your isnull would fail) but you can use the nz function

nz(myfield,"") or to return a numeric value nz(myfield,0)

The last pic I posted is the navigation form with the form inside the subform control. I tried using nz but when dealing with dates and empty strings I get a lot of errors. Only a few fields are required for data entry. I did make this procedure to avoid requeries. It did help some but not enough.

It is called from same module. I think I will use variables for some of the records where I am cascading the combo boxes. I am also going to take the field2... out of some and instead use nz

SetComboBoxValue2Values sFrm.cboTransType, Field2Long(!BankTransTypesID), Field2Long(!BankID), 3

Public Function SetComboBoxValue2Values(cnt As Control, ByVal LID1 As Long, ByVal LID2 As Long, ByVal iCol As Integer)
Dim iCount As Integer
Dim LVal1 As Long, LVal2 As Long
Dim sName As String
Dim i As Integer, i2 As Integer
Dim bSet As Boolean

bSet = False

If TypeOf cnt Is ComboBox Then
    sName = cnt.Name
    For i = 0 To cnt.ListCount - 1
       LVal1 = Field2Long(CVar(LID1))
        
       ' LVal1 = Field2Long(cnt)
            If LVal1 = LID1 Then
                For i2 = i To cnt.ListCount - 1
                        LVal2 = Field2Long(cnt.Column(iCol, i2))
                            If LVal2 = LID2 Then
                                cnt.SetFocus
                                cnt.Selected(i2) = True
                                cnt.Value = LID1
                                bSet = True
                                Exit For
                                
                            End If
                Next i2
            End If
             If bSet Then
                Exit For
            End If
                
    Next i
  
End If
    
End Function

 

[strager]

I just tried changing the field2... to nz and it made no difference.

Public Sub LaunchBillForm(ByVal LBillID As Long)
Dim s As String
Dim sName As String
Dim sFrm As Access.Form
Dim rs As DAO.Recordset
Dim fPB As Form
Dim iPayMethodID As Integer
Dim LID As Long, LBankID As Long

On Error GoTo ErrorHandler

 Set sFrm = ReturnCustomNavForm
 If sFrm Is Nothing Then
    
    MsgBox "No form in launchbillform"
    Exit Sub
 End If
sFrm.tbBills.Value = 3

Application.Echo False
 
 s = "select * from tblBills where billid = " & LBillID & " "
  
   Set rs = CurrentDb.OpenRecordset(s)

    If Not rs.EOF Then
        
        With rs

            sFrm.txtConName = Field2String(!ConName)
            sFrm.txtConEmail = Field2String(!ConEmail)
            sFrm.txtConPhone = Field2String(!ConPhone)
            sFrm.txtConExt = Field2String(!ConExt)
            
            sFrm.txtBillID = LBillID
          
            SetComboBoxValueLong sFrm.cboPersonID, Nz(!PersonID, 0)
            SetComboBoxValueLong sFrm.cboBillingPeriod, Nz(!BillingPeriodID, 0)
            SetComboBoxValue sFrm.cboPaymentTypeID, Nz(!PaymentMethodID, 0)
            LID = Nz(!BillCategoryID, 0)
            SetComboBoxValueLong sFrm.cboCategoryID, LID
            
            SetComboBoxValue2Values sFrm.cboBillName, Nz(!BillNameID, 0), LID, 1
            LBankID = Nz(!BankID, 0)
            SetComboBoxValueLong sFrm.cboBank, LBankID
        
            SetComboBoxValue2Values sFrm.cboTransType, Field2Long(!BankTransTypesID), LBankID, 3
            SetComboBoxValueLong sFrm.cboAccount, Nz(!AccountID, 0)

            SetComboBoxValueLong sFrm.cboExpenseType, Nz(!ExpenseTypeID, 0)
            
            sFrm.txtInvoiceNumb = Field2String(!InvoiceNumb)
            sFrm.txtAmt = Nz(!BillAmt, 0)

            sFrm.txtStartDate = Field2Date(!StartDate)
            sFrm.txtRecurDateTo = Field2Date(!EndDate)
            sFrm.txtDayToPayOn = Nz(!PayOnDay, 0)
            sFrm.txtNumbOfDays = Nz(!NumbOfDays, 0)
            SetComboBoxValueLong sFrm.cboDayOfWeek, Nz(!DayOfWeekID, 0)
            SetComboBoxValueLong sFrm.cboWeekNumberID, Nz(!WeekNumberID, 0)
            
            sFrm.txtAdd1 = !Address1
            sFrm.txtAdd2 = !Address2
            sFrm.txtCity = !City
            sFrm.txtZip = !Zip
            SetComboBoxValueLong sFrm.cboStateID, Nz(!StateID, 0)

            sFrm.txtMailAdd1 = !MailAdd1
            sFrm.txtMailAdd2 = !MailAdd2
            sFrm.txtMailCity = !MailCity
            sFrm.txtMailZip = !MailZip
            SetComboBoxValueLong sFrm.cboMailStateID, Nz(!MailStateID, 0)
            
            sFrm.chkAlert = !Alert
            sFrm.txtAlertNumb = Nz(!AlertNumber, 0)
            
            SetComboBoxValue sFrm.cboAlertDuration, Nz(!AlertNumber, 0)
            
            sFrm.chkRecur = !BillRecur
            If !BillRecur Then
                sFrm.txtRecurDateTo.Enabled = True
            End If
            sFrm.txtNotes = !BillNotes

            sFrm.txtBillAccountNumb = Field2String(!BillAccountNumb)
            sFrm.txtBillPinNumb = Field2String(!BillPinNumb)
            sFrm.txtBillPhonePass = Field2String(!BillPhonePass)
            sFrm.txtBillUserName = Field2String(!BillUserName)
            sFrm.txtBillPassword = Field2String(!BillPassword)
            sFrm.txtBillOther = Field2String(!BillOther)

        End With
  
    End If
  
ExitHandler:

    rs.Close
    Set rs = Nothing
    sFrm.tbBills.Value = 0  'set the tab back to the first tab

    Application.Echo True

Exit Sub

ErrorHandler:

    clsErrorHandler.HandleError "modBills", "LaunchBillForm", True
    Resume ExitHandler

End Sub

 

[CJ_London]

still seems overly complex to me - why not just bind the controls to the recordset? either by setting the form recordsource to the sql string or by setting the form recordset to rs

As others have suggested, think you need to upload an example of the db with the relevant forms and some example data

 

[strager]

still seems overly complex to me - why not just bind the controls to the recordset? either by setting the form recordsource to the sql string or by setting the form recordset to rs

As others have suggested, think you need to upload an example of the db with the relevant forms and some example data

It is complicated. I don't get much flexibility using a bound form. I will usually bind sub forms but not the main form. I can do a lot more things when coding.

 

[strager]

It is complicated. I don't get much flexibility using a bound form. I will usually bind sub forms but not the main form. I can do a lot more things when coding.

I tried not painting both the main form and subform, a little better but still flickers

 

[Pat Hartman]

There are different transaction types with banks, i.e. deposit, credit, transfer, automatic deposit, payment etc. Also there are different account types for a bank... like savings account, checking, 401k etc. People table and the Bank table are the top tier tables.

So, you're saying BankA only allows deposit and credit transactions but BankB only allows transfer transactions? I've never run into that type of situation. Rather than having to maintain a table of specific bank to each trans type allowed, I would categorize banks. and use a category code to say which transactions are allowed. The table will be far simpler to maintain and far smaller. It would be held in memory during any join which would make the process faster.

 

[strager]

So, you're saying BankA only allows deposit and credit transactions but BankB only allows transfer transactions? I've never run into that type of situation. Rather than having to maintain a table of specific bank to each trans type allowed, I would categorize banks. and use a category code to say which transactions are allowed. The table will be far simpler to maintain and far smaller. It would be held in memory during any join which would make the process faster.

I think you are right. I need to simplify this more. I'm going to work on it.

 

[strager]

I have finally significantly reduced screen flicker. Pat Harman suggested simplifying the design. I went through all the tables etc. and there was only one combo box that I could remove.
What I did to stop the flickering was...

1. Made sure all labels were connected with their related control. No noticeable difference.
2. Made sure no labels overlapped the control. No noticeable difference.
3. After my fill procedure I used to use setfocus on a particular control. I Removed setfocus and it made a big difference.
4. I already had Echo = false/True but decided to add painting = false/true. The painting=true was done before echo, and painting false after echo false. Made it better.
5. I made a procedure that would set cascading combo boxes without having to requery to get the correct results.

Public Function SetComboBoxValue2Values(cnt As Control, ByVal LID1 As Long, ByVal LID2 As Long, ByVal iCol As Integer)
Dim iCount As Integer
Dim LVal1 As Long, LVal2 As Long
Dim sName As String
Dim i As Integer, i2 As Integer
Dim bSet As Boolean

bSet = False

If TypeOf cnt Is ComboBox Then
    sName = cnt.Name
    For i = 0 To cnt.ListCount - 1
       LVal1 = Nz(CVar(LID1), 0)
        
       ' LVal1 = Field2Long(cnt)
            If LVal1 = LID1 Then
                For i2 = i To cnt.ListCount - 1
                        LVal2 = Nz(cnt.Column(iCol, i2), 0)
                            If LVal2 = LID2 Then
                                cnt.SetFocus
                                cnt.Selected(i2) = True
                                cnt.Value = LID1
                                bSet = True
                                Exit For
                                
                            End If
                Next i2
            End If
             If bSet Then
                Exit For
            End If
                
    Next i
  
End If
    
End Function

7. I removed any requery when setting combo and listbox values. Notice how I commented out the cnt.requery below. This also made a huge difference.

Public Function SetComboBoxValueLong(cnt As Control, LID As Long)
Dim iCount As Integer
Dim LCboVal As Long
Dim i As Integer
Dim sName As String
On Error Resume Next

sName = cnt.Name
If TypeOf cnt Is ComboBox Then
    If LID = 0 Then
        If cnt.Visible = True Then
            cnt.SetFocus
            cnt.Value = -1
            Exit Function
        End If
    End If
    
    For i = 0 To cnt.ListCount - 1
        LCboVal = Nz(cnt.Column(0, i), 0)
            If LCboVal = LID Then
            
                cnt.SetFocus
                cnt.Selected(i) = True
                cnt.Value = LID
                'cnt.Requery
                Exit For
            End If
                      
    Next i
  
End If
Exit Function

End Function

 

[Pat Hartman]

I don't understanding why you are looping through each row of each listbox.

 

[strager]

I don't understanding why you are looping through each row of each listbox.

Is there another way to select an item in a listbox? I am filling a form with selected information.

 

[CarlettoFed]

It would be easier and quicker to have an example file where you can see directly what you have created.

 

[cheekybuddha]

Is there another way to select an item in a listbox?

Yes:

Public Function SetComboBoxValueLong(cnt As Control, LID As Long)
  cnt.Value = IIf(LID > 0, LID, Null)
End Function

though a separate function to set the value of a combo seems overkill!