SQL ComboBox row source question

[bozic12]

Hi all,

I have a combobox that shows multiple fields. I used the combobox wizard to create it because the source of these fields, 'Insert Table', is from a different table than the forms controlling table, 'Cutter Config Table', but I want the Insert_ID field from the recordset of the chosen value to populate the Form's controlling table's 'Insert ID' value.

The problem I am having is that the field showing the manufacturer from the 'Insert Table', 'mfgID', is just the ID number of the manufacturer's name from the 'Mfg Table'. I think I need to use an inner join in the ComboBoxes row source to pull the manufacturers name from 'Mfg Table' on 'MfgID' instead of showing the 'MfgID' from the 'Insert Table'

The current Row Source code is"
SELECT [Insert Table].[Insert ID], [Insert Table].[Catalog No], [Insert Table].[Nose Radius], [Insert Table].[Mfg ID] FROM [Insert Table] ORDER BY [Mfg ID];

I've unsuccessfully tried:
SELECT [Insert Table].[Insert ID], [Insert Table].[Catalog No], [Insert Table].[Nose Radius], [Mfg Table].[Mfg Name] FROM [Insert Table] INNER JOIN [Mfg Table] ON [Insert Table].[Mfg ID] = [Mfg Table].[Mfg ID] ORDER BY [Mfg Name];

I get an 'Enter Parameter Value: Mfg Table.Mfg Name' diaglog box.


Thanks for any help you can offer.

-Jeff

 

[bozic12]

Nevermind...just figured it out.

It was a stupid, stupid syntax error that took me 3+ hours to finally figure out!! Gotta love it...

Thank you to anyone who may have spent some time testing this out for me.

Jeff

 

[surfside1]

What was the resolution? I'm having the same problem.

Here's the code and one of the last commented out select statment is from someone else on tek-tips.
Surfside1

BELOW IS CODE, THANKS BUNCHES!

Private Sub combo_Facility_AfterUpdate()

If IsNull(Me.combo_Facility.Text) = True Then
MsgBox "Please Select a Facility from the Facility drop down list.", vbCritical
Me.combo_Facility.SetFocus
Exit Sub
End If
MsgBox "this is Me.combo_Facility.Text: " & Me.combo_Facility.Text & " Now what??"
Dim combo_FacilityValue
combo_FacilityValue = Me.combo_Facility.Text
MsgBox "made it to after update, facility value = " & combo_FaciltyValue & " the end"
Dim tbl_Name As String
tbl_Name = "tbl_2005" & combo_FacilityValue
MsgBox "table name and field: " & "tbl_2005" & combo_FacilityValue & " the end"
MsgBox " displaying string value to put in select statement for table : " & tbl_Name
Me.combo_Test.SetFocus

Me.combo_Test.RowSource = _
"SELECT tbl_Name.ID FROM tbl_Name ORDER BY tbl_Name.ID"

' Me.combo_Test.RowSource = _
' "SELECT tbl_Name.ID & _
' FROM & tbl_Name ORDER BY tbl_Name.ID
'
' Me.combo_Test.RowSource = _
' "SELECT [ID], [Commodity], [SupplierNumber], [Supplier] FROM " & "Tbl_2005" & Trim(combo_FacilityValue)

' Me.combo_Test.SetFocus
' Me.combo_Test.RowSource = _
' "SELECT ID, Commodity, SupplierNumber, Supplier FROM " & "Tbl_2005" & Trim(Me.combo_Facility.Text)
'Me.Combo2.RowSource = _
' "SELECT <<Field>> " & _
' FROM " & "Tbl_2005" & Trim(Me.Combo1.Text)
Me.combo_Test.Requery
Me.combo_Test.SetFocus
End Sub