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
ExpressMan1Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor 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
All Courses

From novice to tech pro — start learning today.