none
Two multi-list combo boxes on a form RRS feed

  • Question

  • I am using Microsoft Access 2010. I have a two combo boxes, each with a multi-list, on a form. One combo box is called Client Name and the other combo box is called Premium Invoice Number. The two combo boxes are dependent on each other i.e. I click on the first combo box called Client Name to select a client name and then select on the second combo box called Premium Invoice Number to select an invoice number for that particular client. 

    Clicking the Data tab of the Property Sheet of the Client Name combo box, the following details are noted: -

    Record Source: SELECT [tblClient].[ClientID], [tblClient].[ClientName] FROM tblClient ORDER BY [ClientName];

    Row Source Type: Table/Query

    Bound Column: 1

     The Data tab of the Property Sheet of the PremiumInvoiceNumber, the following details are noted:

    SELECT [qryBillingsPremium].[PremiumInvoiceNumber], [qryBillingsPremium].[BillingID] FROM qryBillingsPremium ORDER BY [BillingID];

    Row Source Type: Table/Query

    Bound Column: 2

    My VB code is:

    Option Compare Database
    Option Explicit
    
    Private Sub cboClientName_AfterUpdate()
    Call SearchCriteria
    End Sub
    
    Private Sub cboPremiumInvoiceNumber_AfterUpdate()
    Call SearchCriteria
    End Sub
      Function SearchCriteria()
      Dim ClientName, strPremiumInvoiceNumber As String
      Dim task, strCriteria As String
    
      If IsNull(Me.CboClientName) Then
          ClientName = "[ClientID] like '*'"
      Else
          ClientName = "[ClientID] = " & Me.CboClientName
      End If
    
     If IsNull(Me.CboPremiumInvoiceNumber) Then
         strPremiumInvoiceNumber = "[PremiumInvoiceNumber] like '*'"
     Else
         strPremiumInvoiceNumber = "[PremiumInvoiceNumber] = '" & Me.CboPremiumInvoiceNumber & "'"
     End If
     strCriteria = ClientName & "And" & strPremiumInvoiceNumber
        task = "Select * from qryBillingsPremium where " & strCriteria
        Forms!frmInvoicePremiumBalance!frmInvoicePremiumBalanceSubform.RecordSource = task
        Forms!frmInvoicePremiumBalance!frmInvoicePremiumBalanceSubform.Requery
        
     End Function
    

    When I perform a search on the first combo box called Client Name, I receive an error message "run time error 438 object doesn't support this property or method". On clicking the Debug option of the error message, line 18 in the VB code is highlighted in yellow. On inputting "?task" in the Immediate Window, I receive the following message: -

    Select * from qryBillingsPremium where [ClientID] = 3And[PremiumInvoiceNumber] like '*'

    I  am requesting some assistance in fixing these problems. Thank you in advance of your help.

    Monday, August 24, 2015 1:32 AM

Answers

  • Ok, again your form is corrected.  I'm going to have to start charging you for my services... :)

    Cleaned up your form and sub-form controls.  One of your problems was that your cboPremiumInvoiceNumber control was defined as two column when only one was needed and the bound column was set to 2.

    There were a few other issues, but too many and too trivial to go into.  All is corrected now.

    Here is your link to your new version:

    Link Deleted



    • Marked as answer by wirejp Monday, August 24, 2015 4:05 PM
    • Edited by RunningManHD Monday, August 24, 2015 4:06 PM
    Monday, August 24, 2015 3:47 PM
  • Thank you very much for your help. I appreciate your time and effort. Can I request that you delete this link, due to the sensitive information in the database.
    • Marked as answer by wirejp Monday, August 24, 2015 4:06 PM
    Monday, August 24, 2015 4:05 PM

All replies

  • Your where clause needs spacing between each criteria item and "AND"
    Monday, August 24, 2015 1:47 AM
  • Hi RunningManHD,

    Thank you for the quick response. Can you show me the spacing changes required for the code? I have tried different approaches but they are not working: -

    Option Compare Database
    Option Explicit
    
    Private Sub cboClientName_AfterUpdate()
    Call SearchCriteria
    End Sub
    
    Private Sub cboPremiumInvoiceNumber_AfterUpdate()
    Call SearchCriteria
    End Sub
    Function SearchCriteria()
    Dim ClientName, strPremiumInvoiceNumber As String
    Dim task, strCriteria As String
    
    If IsNull(Me.CboClientName) Then
        ClientName = "[ClientID] like '*'"
    Else
        ClientName = "[ClientID] = " & Me.CboClientName
    End If
    
    If IsNull(Me.CboPremiumInvoiceNumber) Then
        strPremiumInvoiceNumber = "[PremiumInvoiceNumber] like '*'"
    Else
        strPremiumInvoiceNumber = "[PremiumInvoiceNumber] = '" & Me.CboPremiumInvoiceNumber & "'"
    End If
    strCriteria = ClientName & "AND" & strPremiumInvoiceNumber
        task = "Select * from qryInvoicePremiumBalance where " & strCriteria
        Forms!frmInvoicePremiumBalance!frmInvoicePremiumBalanceSubform.RecordSource = task
        Forms!frmInvoicePremiumBalance!frmInvoicePremiumBalanceSubform.Requery
        
        
    End Function

    Monday, August 24, 2015 2:53 AM
  • Here ya go...

    Option Compare Database
    Option Explicit
    
    Private Sub cboClientName_AfterUpdate()
        
        SearchCriteria
        
    End Sub
    
    Private Sub cboPremiumInvoiceNumber_AfterUpdate()
        
        SearchCriteria
        
    End Sub
    
    Function SearchCriteria()
        
        Dim strCriteria1 As String
        Dim strCriteria2 As String
        Dim strSQL As String
        Dim strWHERE As String
        
        If IsNull(Me.CboClientName) Then
            strCriteria1 = "[ClientID] Like '*'"
        Else
            strCriteria1 = "[ClientID] = " & Me.CboClientName
        End If
        
        If IsNull(Me.CboPremiumInvoiceNumber) Then
            strCriteria2 = "[PremiumInvoiceNumber] Like '*'"
        Else
            strCriteria2 = "[PremiumInvoiceNumber] = '" & Me.CboPremiumInvoiceNumber & "'"
        End If
    
        strWHERE = "WHERE " & strCriteria1 & " AND " & strCriteria2
        strSQL = "SELECT * FROM qryInvoicePremiumBalance " & strWHERE
        
    'If the following is a subform, then you need to specify the Form object after the control name.
    'Furthermore, if this subform is located on the same form that you are working in, then it
    'isn't necessary to qualify the Forms collection and the form name. All you need is "Me."
    'Example: Me.frmInvoicePremiumBalanceSubform.Form.RecordSource = strSQL Forms!frmInvoicePremiumBalance!frmInvoicePremiumBalanceSubform.Form.RecordSource = strSQL 'You shouldn't need a requery after changing the form's recordsource 'Forms!frmInvoicePremiumBalance!frmInvoicePremiumBalanceSubform.Requery End Function

    A word of advice.  When you declare variables, you must define each declaration.  In your code example, ClientName and Task are defined as Variants by default because you did not specify their data type.  To have done this correctly, your declarations would look like the following:

    Dim ClientName As String, strPremiumInvoiceNumber As String
    Dim task As String, strCriteria As String

    I prefer not to define variables inline like this.  It is easier to read and less confusing if you define each on its own line.

    One other piece of advice.  You should consider shorter names for your objects.  Be descriptive, but brief.  For instance, your subform control is named "frmInvoicePremiumBalanceSubform," it would be better named "sfmInvoiceBalance."  Short and sweet!

    Also, try to maintain consistency when naming your variables.  Half of your variables were named with descriptors indicating data type, and the other half... just a variable name.

    Last thing, you should consider correcting your control / object name(s) regarding the client.  If you are referencing the client name, then name as such.  In this circumstance, your are referencing the client id, again, name your control as such.  Though it's a combobox looking up by client name, your are actually looking up the client id by client name.  So name your control "cboClientID." 

    As for your combobox for the InvoiceNumber, name it simply "cboInvoice" or "cboInvoiceNumber" or "cboPremInvoice."  Again, be descriptive, but keep it short, simple, and sweet!








    Monday, August 24, 2015 4:29 AM
  • Hi RunningManHD,

    I made the following changes:-

    corrected the control/object name (s): I have named the control on the combobox looking up by client name as "cboClientID"

    I have updated the row source on the PremiumInvoiceNumber as:

    SELECT DISTINCT qryInvoiceBilling.PremiumInvoiceNumber FROM qryInvoiceBilling WHERE (((qryInvoiceBilling.PremiumInvoiceNumber) Is Not Null)) ORDER BY qryInvoiceBilling.PremiumInvoiceNumber;

    and the VB code has been updated as: -

    Option Compare Database Option Explicit Private Sub cboClientName_AfterUpdate() SearchCriteria End Sub Private Sub cboPremiumInvoiceNumber_AfterUpdate() SearchCriteria End Sub Function SearchCriteria() Dim strCriteria1 As String Dim strCriteria2 As String Dim strSQL As String Dim strWHERE As String If IsNull(Me.cboClientID) Then strCriteria1 = "[ClientID] Like '*'" Else strCriteria1 = "[ClientID] = " & Me.cboClientID End If If IsNull(Me.CboPremiumInvoiceNumber) Then strCriteria2 = "[PremiumInvoiceNumber] Like '*'" Else strCriteria2 = "[PremiumInvoiceNumber] = '" & Me.CboPremiumInvoiceNumber & "'" End If strWHERE = "WHERE " & strCriteria1 & " AND " & strCriteria2 strSQL = "SELECT * FROM qryInvoicePremiumBalance " & strWHERE 'If the following is a subform, then you need to specify the Form object after the control name. 'Furthermore, if this subform is located on the same form that you are working in, then it 'isn't necessary to qualify the Forms collection and the form name. All you need is "Me." 'Example: Me.frmInvoicePremiumBalanceSubform.Form.RecordSource = strSQL Me.frmInvoicePremiumBalanceSubform.Form.RecordSource = strSQL 'You shouldn't need a requery after changing the form's recordsource 'Forms!frmInvoicePremiumBalance!frmInvoicePremiumBalanceSubform.Requery End Function

    I then tested these changes in the form and the following results happen: -

    When I clicked the ClientName combobox and I select a specific client name, but nothing happened (however, I expecting to see a search result which showed all of the records for the selected client).

    Afterwards, I clicked on the PremiumInvoiceNumber combobox and I selected a particular premium invoice number. The database returned a search result of all of the records for this particular client (however, I was expecting to see a search result which showed the selected premium invoice number for the selected client)

    Can you kindly help me with this problem?


    • Edited by wirejp Monday, August 24, 2015 1:28 PM grammatical errors
    Monday, August 24, 2015 12:39 PM
  • If you want to post your DB on OneDrive again, I will have a look to see what is happening.  Be sure to let me know which form we are working with this time.  I had to guess the last time.

    Thanks,

    Monday, August 24, 2015 1:46 PM
  • Ok, again your form is corrected.  I'm going to have to start charging you for my services... :)

    Cleaned up your form and sub-form controls.  One of your problems was that your cboPremiumInvoiceNumber control was defined as two column when only one was needed and the bound column was set to 2.

    There were a few other issues, but too many and too trivial to go into.  All is corrected now.

    Here is your link to your new version:

    Link Deleted



    • Marked as answer by wirejp Monday, August 24, 2015 4:05 PM
    • Edited by RunningManHD Monday, August 24, 2015 4:06 PM
    Monday, August 24, 2015 3:47 PM
  • Thank you very much for your help. I appreciate your time and effort. Can I request that you delete this link, due to the sensitive information in the database.
    • Marked as answer by wirejp Monday, August 24, 2015 4:06 PM
    Monday, August 24, 2015 4:05 PM