Dynamically populate a combo box based on another combo box selection

[Pookiemeister]

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.

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

 

[diddi]

try
... WHERE AssetID CONTAINS ...

 

[Micron]

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

"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.

 

[Shafique]

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.

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

very easy way to understand and figure out it