Solved

Limit Combo Box values on  of 2 other Combo Boxes

Posted on 2015-01-20
3
215 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
Comment Utility
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
Comment Utility
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
Comment Utility
Solved it. Was missing "FROM tblFedEx Services"   !!!

Thank You
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 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