Solved

Limit Combo Box values on  of 2 other Combo Boxes

Posted on 2015-01-20
3
219 Views
Last Modified: 2015-01-20
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
Comment
Question by:ExpressMan1
  • 2
3 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40560296
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
 

Author Comment

by:ExpressMan1
ID: 40560386
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
 

Author Comment

by:ExpressMan1
ID: 40560441
Solved it. Was missing "FROM tblFedEx Services"   !!!

Thank You
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes some very basic things about SQL Server filegroups.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now