cascading combo box in a subform

K

Kevin

I have a subform in datasheet view. One of the fields has a dropdown list so
a product can be selected for that field. That dropdown is from a table and
is beyond 200 selections. To make this easier for users, I created a
cascading combo box form. The trouble is, I don't know how to link that form
to that field, so that I can open the cascading combo box form, make a
selection, and insert into that field.

I have posted a similar question on these Discussion Groups twice previously
and got no responses, which leads me to believe this may not even be
possible. If that is the case, I would appreciate if someone would confirm
that. Thank you!
 
B

buced3846

Kevin,

You created a separate FORM to pop-up and have then select a CATEGOR
so the PRODUCT list can be limited? You can do this using CASCADIN
COMBOs, but I have never seen it done from a separate form. To do i
with cascading combos, put a combobox on the form to select distinc
categories from your table tblProducts, (assuming your table structur
contains fields like ProductID, ProductName, and ProductCategory).
Then in the AfterUpdate event of the ProdcutCategory dropdown you woul
use something like

Private Sub ProductCategory_AfterUpdate()
Dim Category As String
Dim cbosql As String

On Error GoTo Err_ProductCategory_AfterUpdate

If Not IsNull(Me.ProductCategory.Column(2)) Then
Category = Me.ProductCategory.Column(2)
cbosql = "SELECT * FROM WHERE supplierid = " + Category
'MsgBox cbosql
With Me.ProductName
.RowSource = cbosql
.ColumnCount = 2
.ColumnHeads = False
.ColumnWidths = "0cm; 2.5cm"
.Requery
End With
End If

Exit_ProductCategory_AfterUpdate:
Exit Sub

Err_ProductCategory_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductCategory_AfterUpdate

End Sub

Good Luck...

Bruce Davis
 
Top