Doing a Select Statement for Combo based on another field on form

[surfside1]

I have a form with a comboBox that lists all values for a field, such as Facility1, Facility2. When a value is selected the next control is a combobox that will do a Select Statement for RowSource. I want to code the Select statement so that it goes to a certain table based on what they selected, Facility1 or 2. The table name will be tbl_2005Facility1 if they selected Facility1. All tables will start with "tbl_2005" then the facility name. Once that comboBox is filled and the user selects from the list the record they want, the click even will go to a new form that will only display the record they selected from the Facility table they selected in the first combo box.

I'm sure I'll have dim fields that contain the correct values and I want to put them in the Select Statement.
Anyone know the syntax?
Appreciate it,
Surfside1

 

[HandsOnAccess]

Private Sub Combo1_AfterUpdate()
     If IsNull(Me.Combo1.Text) = True Then
        MsgBox "Please Select A Value.", VbCritical
        Me.Combo1.SetFocus
        Exit Sub
     End If
     
     Me.Combo2.RowSource = "SELECT <<Field>> " & _
     " FROM " & "Tbl_2005" & Trim(Me.Combo1.Text) 
     
     Me.Combo2.Requery
     Me.Combo2.SetFocus
End Sub

 

[surfside1]

I tried what you suggested and thank you for your rapid response. I created a module that has the code, it is invoked from a macro, the after_update invokes the macro, and it says "the expression you entered has a function that MS Access can't find". Do you have to compile these or something for Access to recognize? I checked and double-checked the spelling of the module/macro/event and don't see where I misspelled. Any suggestions?
Surfside1

 

[HandsOnAccess]

No need to create a macro, just place the code in ComboBox <<Combo1>> after_update event in VBA Module.

Also check field names.

 

[surfside1]

I hate to do this to you, but I've tried this thing all morning and contine to have problems. Yesterday, I was confused about a module versus an event procedure, but I'm cool on that now.

Below is the code I'm stuck on. I have alot commented out, added some variables to replace the table name/field, and added some msgboxes to help me understand what was going on. Now I think the one select statement that is not commented out, is erroring out because I don't know the correct syntax that I need to translate the Select stmt for RowSource.

If you can spend the time, I'd appreciate it if you could help, you've been very responsive and kind.

Thanks,
Surfside1


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 [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

 

[PHV]

Something like this ?
Private Sub combo_Facility_AfterUpdate()
If Trim(Me!combo_Facility.Text & "") = "" Then
MsgBox "Please Select a Facility from the Facility drop down list.", vbCritical
Me!combo_Facility.SetFocus
Exit Sub
End If
Dim tbl_Name As String
tbl_Name = "tbl_2005" & Me!combo_Facility.Text
Me!combo_Test.RowSource = _
"SELECT ID,Commodity,SupplierNumber,Supplier" _
& " FROM [" & tbl_Name & "] ORDER BY ID"
Me!combo_Test.SetFocus
Me!combo_Test.DropDown
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HandsOnAccess]

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

  Dim combo_FacilityValue
  combo_FacilityValue = Me.combo_Facility.Text

  Dim tbl_Name As String
  tbl_Name = "tbl_2005" & combo_FacilityValue
    
  Me.combo_Test.RowSource = _
     "SELECT ID " & _
     "FROM " & tbl_Name & " ORDER BY 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.combo_Test.Requery
     Me.combo_Test.SetFocus
End Sub

 

[surfside1]

Thank you both so much! It was my syntax of select statement, naming the fields, as well as stupid me - the table name was "tbl_2005_Facility. Missed the second underscore!!!!

Anyway, you guys are won, won, won, wonderful!!!!!!
Surfside1