Dynamically populate a combo box based on another combo box selection

Pookiemeister

Active Member
Joined
Jan 6, 2012
Messages
489
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Currently I am working with two tables and one form. The first table is named tblActiveAssetID. This table has only one field called "AssetID". The second table is named tblActiveAssets. The only field I am interested in is named "Asset ID".

tblActiveAssetID content: tblActiveAssets
"AssetID" Field "Asset ID" field
4K00
4K01 4K00 LFT 101
4K02 4K00 LFT 102
.
'
'
4K10 4K00 LFT 110
6W00 6W00 LFT 100
6W01 6W01 LFT 100
. .
. .
6W05 6W05 LFT 100

On my form I have a combo box labeled cboLine (currently contains all the values from AssetID field ) and cboLocation(currently contains all the values from Asset ID field). I think the problem that I am having is that the AssetID field only has as an example 4K00 where as the "Asset ID" has 4K00 & LFT ###. Could this be the reason why I cannot populate my cboLocation? How can I make this work? Is there something I need to do to one of my tables? When I run the code below and click on the cboLocation I get a "Syntax error (missing operator) in query expression 'AssetID=4K00'." I know in Excel, to accomplish this, I would use "Select Case" but I want this to be more dynamic. Thank you.

VBA Code:
Option Compare Database
Option Explicit

Private Sub cboLine_AfterUpdate()
   Dim strSQL As String
   
   strSQL = "SELECT * FROM tblAssets WHERE AssetID = " & Me.cboLine
   
   Me.CboLocation.RowSource = strSQL
End Sub
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
try
... WHERE AssetID CONTAINS ...
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,185
Office Version
  1. 365
Platform
  1. Windows
Your current issue is likely that you don't encapsulate the combo value in single quotes, seeing that it has to be text and not a number (based on the values you show). Perhaps
SQL:
"SELECT * FROM tblAssets WHERE AssetID = '" & Me.cboLine & "'"
Afraid your table structure makes no sense to me, plus you seem to be storing the same data more than once.
 

Forum statistics

Threads
1,137,125
Messages
5,679,755
Members
419,855
Latest member
Eddier32

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top