Form Combo Box Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 3 tables that are accessed on 1 form for Product entry (Products table). The user uses a combo box to select the Asset Type (2nd table). I want the Asset SubType combo box (3rd table) to only pull records from the Asset SubType table that match the selected Asset Type from the form

This is probably pretty straightforward, but I'm lost. Any help is greatly appreciated.
 
I have 3 tables that are accessed on 1 form for Product entry (Products table). The user uses a combo box to select the Asset Type (2nd table). I want the Asset SubType combo box (3rd table) to only pull records from the Asset SubType table that match the selected Asset Type from the form.

This is probably pretty straightforward, but I'm lost. Any help is greatly appreciated.
Click to expand...

It's a bit harder than it might appear. There are a couple of ways to
do it, but both involve at least a little bit of VBA code.

Method 1: Base the Asset SubType combo box on a Query which references
the Asset Type combo. E.g.

SELECT AssetID, AssetName
FROM AssetSubtypes
WHERE [AssetSubtypes].[AssetTypeID] =
[Forms]![YourFormName]![cboAssetType]
ORDER BY AssetName;

You will then need to Requery the Asset Subtype combo
(cboAssetSubtype) in the AfterUpdate event of the Asset Type combo:

Private Sub cboAssetType_AfterUpdate()
Me!cboAssetSubtype.Requery
End Sub

Method 2:

Actually construct a SQL Query in the AfterUpdate event of the first
combo, and assign it as the second combo's RowSource:

Private Sub cboAssetType_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT AssetID, AssetName FROM AssetSubtypes WHERE"
strSQL = strSQL & " [AssetTypeID] = " & Me!cboAssetType
strSQL = strSQL & " ORDER BY AssetName;"
Me!cboAssetSubtype.RowSource = strSQL
End Sub
 
Back
Top