Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

Limit Combo Box values on of 2 other Combo Boxes

Small sample db attached.

I have a form "frmRateTool" with multiple Combo Boxes. I am try to set the RowSource of cboFedExServices if the value of 2 other Combo Boxes are "Canada"

Private Sub cboDestinationCountryID_AfterUpdate()

Dim strServiceTypeCanada As String
Dim strServiceClass As String

strServiceClass = "Domestic"

If Me.cboOriginCountryID.Column(1) = "Canada" And Me.cboDestinationCountryID.Column(1) = "Canada" Then

strServiceTypeCanada = "SELECT [tblFedExServices].[ServiceTypeID], [tblFedExServices].[ServiceTypeDescription], " & _
                    "[tblFedExServices].[ServiceClass] WHERE [ServiceClass] = " & Chr$(39) & strServiceClass & Chr$(39)
                   
                                       
                    Me.cboServiceTypeID.RowSource = strServiceTypeCanada
                    Me.cboServiceTypeID.Requery

End If

End Sub
ComboBoxSelection.accdb
0
ExpressMan1
Asked:
ExpressMan1
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You're referring to the SECOND column of your combo. The Columns collection for a combo is zero-based.

Are you sure you want to refer to that column?

If instead you want to refer to the FIRST column, then use Column(0).

That said: Your code should work, assuming you've spelled everything correctly, and your "strServiceTypeCanada" statement is formed correctly.

Note too you don't need to Requery cboServiceTypeID. Setting the RowSource is sufficient.
0
 
ExpressMan1Author Commented:
Column(0) is the ID and Column(1) is the text description of the service. I need the service description to show in the Combo Box.

I am getting the error;

Syntax error (missing operator) in query expression '[tblFedExServices].[ServiceClass] WHERE [ServiceClass] = 'Domestic".

Tried this but no luck;

Dim strServiceTypeCanada As String
Dim strServiceClass As String

strServiceClass = "Domestic"

If Me.cboOriginCountryID.Column(0) = 1 And Me.cboDestinationCountryID.Column(0) = 32 Then

strServiceTypeCanada = "SELECT [tblFedExServices].[ServiceTypeID], [tblFedExServices].[ServiceTypeDescription], " & _
                    "[tblFedExServices].[ServiceClass] WHERE [ServiceClass] =  '" & strServiceClass & "'"
                   
                   
                                       
                    Me.cboServiceTypeID.RowSource = strServiceTypeCanada
0
 
ExpressMan1Author Commented:
Solved it. Was missing "FROM tblFedEx Services"   !!!

Thank You
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now