COMBO BOX

[cole_mac]

I have a combo box where the value list is built by query result. I need the WHERE clause to reference a field on the form. What is the syntax? I have included my attempt below. Thank you in advance!

SELECT Attribute_Value.Attribute_Type_Id,
FROM Attribute_Value
WHERE (((Attribute_Value.Attribute_Type_Id)=[Forms]![Product_Attribute]![Attribute_Type_Id]));

 

[BarkerD]

You need to set this dynamically.
The source of the Combobox only gets set when you load the form. What happens if the user keeps changing the value in the selected field, and expects a different ComboBox rowsource?

I would write some code for the GotFocus Event of the ComboBox control that sets the rowsource.

Dim strRowSource as String
If isnull(me.[Attribute_Type_Id]) = True then

'You don't want to set the rowsource if this value is null.
Docmd.CancelEvent
Exit Sub

Else

strRowSource="SELECT [Attribute_Value].[Attribute_Type_Id] FROM [Attribute_Value]
WHERE [Attribute_Value].[Attribute_Type_Id] = """ & me.[TypeIDControlName] & """;"

'If the Attribute type is numeric, remove 2 " from around the control name on both sides.

me.Rowsource=strRowSource

EndIf

 

[cole_mac]

Thank you for your help. This partially solves my problem but the root of the problem is that the combo box is not referencing the form field at all let alone when it changes. In order for me to populate the combo box i have to manually enter in a parameter for the query.

The code below is what specifically is not working:
(((Attribute_Value.Attribute_Type_Id)=[Forms]![Product_Attribute]![Text12]));

 

[TGHockett]

cole_mac -- I'd be interested to know why using the combo box wizard doesn't achieve your objectives? The ones I have created work like a charm, though they are all 'required' to be used to validate data or select a requested record to be edited.

TGH

 

[BarkerD]

If you bind the combobox to the control you need, and don't even bother setting it's Rowsource until you trigger the GotFocus Event of the Combobox, it should work fine. Here is a sample of code that I used to set the rowsource of a combobox based on a value.

If IsNull(Me.TagNumber) Then
Me.TagNumber.SetFocus
Else
If IsNull(Me.InvCode) Then
MsgBox$ ("You must enter a valid Inventory Code")
DoCmd.CancelEvent
Else
Dim strCases, strSQL As String
strCases = Me.InvCode

Select Case strCases
Case "RM"
strSQL = "SELECT [Raw Material].[invpart#],[Raw Material].[ListName] FROM [Raw Material] ORDER BY [Raw Material].[invpart#];"
Me.cmbPartNumber.RowSource = strSQL
Me.cmbPartNumber.ColumnWidths = "1 cm; 10cm"
Case "WIP"
strSQL = "SELECT [Prod-Operations].AnconNumber, [Prod-Operations].[invpart#] FROM Products INNER JOIN [Prod-Operations] ON Products.[InvPart#] = [Prod-Operations].[InvPart#] WHERE [Products].[Current]=True and [Prod-Operations].[anconnumber] is not null ORDER BY [Prod-Operations].[anconnumber];"
Me.cmbPartNumber.RowSource = strSQL
Me.cmbPartNumber.ColumnWidths = "2 cm; 6cm"
Case "FG"
strSQL = "SELECT [Products].[invpart#],[Products].[PartDesc] FROM Products WHERE [Products].[Current]=true ORDER BY [Products].[invpart#];"
Me.cmbPartNumber.RowSource = strSQL
Me.cmbPartNumber.ColumnWidths = "4 cm; 4cm"
Case "VOID"
Me.cmbPartNumber.Value = "VOID"
Case Else

End Select
Me.cmbPartNumber.RowSource = strSQL
End If
End If

This checks for an Inventory code, and changes the rowsource based on that value. it would be a simple thing to add a field name parameter to the SQL string

Duane Barker