First, what's all this talk about putting the option group's code in the BEFORE UPDATE event? You should be putting this in the AFTER UPDATE event. You want to change the combobox's rowsource immediately after an option button is chosen. If you put it in BEFORE UPDATE, you have not chosen an option yet so how will it know which one you want? Your code will look something like this:


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Oops... I forgot two closing parentheses before the "ORDER BY" in the rowsource statement. I edited them in.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

That code doesn't cause an error but it doesn't populate the combo box either...

I wish I had a better understanding of VBA

What Braxis meant is that
"SELECT Product.Product_Name" & _

should be
"SELECT Product.Product_Name " & _

Basically, add an extra space at the end or the line before the " (Except for the last line).

Have you tried to put the SQL String on a query to see what it returns?
By the way, use " double quotes for string literals.



Sal

yeah, the queries work great. I can write SQL all day. Just don't know VBA very well

Here is my latest attempt at failure...



Private Sub Frm_InvUpd_Category_AfterUpdate()

Select Case Frm_InvUpd_Category.Value

Case 1
     Me.Frm_InvUpd_Category.Value = 1
     Me.Cmb_InvUpd_Prod.RowSource = "SELECT Product.Product_Name " & _
                                     " FROM Product " & _
                                     " WHERE (((Product.Product_Category) = 'Long Range')) " & _
                                     " ORDER BY Product.Product_Name;"

Case 2
    Me.Frm_InvUpd_Category.Value = 2
    Me.Cmb_InvUpd_Prod.RowSource = "SELECT Product.Product_Name " & _
                                 "FROM Product " & _
                                 "WHERE (((Product.Product_Category) = " 'Mid Range'")) " & _
                                 "ORDER BY Product.Product_Name;"

Case 3
Case 4
Case 5

End Select
End Sub

I've also tried it this way to no avail...

Private Sub Frm_InvUpd_Category_AfterUpdate()
Dim strCategory As String
Select Case Me.Frm_InvUpd_Category.Value
     Case 1
         strCategory = "Long Range"
     Case 2
         strCategory = "Mid Range"
     Case 3
         strCategory = "Putt & Approach"
     Case 4
         strCategory = "Specialty"
     Case 5
         strCategory = "Recreational"
End Select
Me.Cmb_InvUpd_Prod.RowSource = "SELECT Product.Product_Name " & _
      "FROM Product WHERE (((Product.Product_Category) = '" & _
      strCategory & "')) ORDER BY Product.Product_Name;"

End Sub

Have you tried stepping through your code?
Do this, put this line anywhere

MsgBox "MESSAGE"

Does it display when you run the sub?

Do you have data in the table?

when you open the form in design view and check the properties, under Events, do you have an [Event Procedure] by the elipsys next to the After update event for the frame control?

I keep getting mixed up with this name Frm_InvUpd_Category for a frame (it is not a frm it is a cbo). the name should be something close to cboSomething.




Sal

 If there is no error and the combobox won't populate then are you sure that the table you're referencing, namely Product, has any data that meets the criteria? Perhaps it's giving you no items because there are no items that meet your criteria. Check your criteria, field names, and table closely to see if there is data in there that matches what you want.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

I just recreated all my tables and dropped all my queries and now the form works great!

You guys rock!!!

Oh No! Can you believe I'm adding even MORE to this topic? :)

Here's a couple of pointers that I would recommend to keep in
mind when you're trying to do what you mentioned in the first panel.

 - Build your SQL in the AfterUpdate() for your option group if you're
   using option buttons - that way you KNOW that the refresh will
   take place at the moment that the button is selected.
 - Don't forget to do a requery on your combo box as soon as you
   rebuild the SQL and set the rowsource. Something like this:

Select Case Frm_InvUpd_Category.Value
  Case 1
    Me.Frm_InvUpd_Category.Value = 1
    Me.Cmb_InvUpd_Prod.RowSource = "SELECT Product.Product_Name " & _
                                     " FROM Product " & _
                                     " WHERE (((Product.Product_Category) = 'Long Range')) " & _
                                     " ORDER BY Product.Product_Name;"

  Case 2
    Me.Frm_InvUpd_Category.Value = 2
    Me.Cmb_InvUpd_Prod.RowSource = "SELECT Product.Product_Name " & _
                                   "FROM Product " & _
                                   "WHERE (((Product.Product_Category) = " 'Mid Range'")) " & _
                                   "ORDER BY Product.Product_Name;"

 Case 3
 Case 4
 Case 5

Me.Cmb_InvUpd_Prod.Requery

End Select


If you need to have the combo box's rowsource updated for an existing
record is displayed with a selected value for an option button, you can put the SQL build statements in a shared function within the form and call it from the AfterUpdate on the buttons and from the Form_Current(). Just be sure that the Form_Current() will only call the function if Me.NewRecord = False.

I'll be happy to clarify if any of this seems a little fuzzy or confusing.

Best Wishes to All,

Warren
:D