Option buttons, queries and combo boxes...

Anubis · November 18th, 2003, 04:09 PM

Here is the code that is working at the moment, it is in the .AfterUpdate event

Private Sub Frame_InvUpd_Category_AfterUpdate()

Dim strCategory As String
   Select Case Me.Frame_InvUpd_Category.Value
      Case 1
         strCategory = "Long Range"
      Case 2
         strCategory = "Mid Range"
      Case 3
         strCategory = "Putt & Approach"
      Case 4
         strCategory = "Recreational"
      Case 5
         strCategory = "Specialty"
   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

Quote:

quote: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.

*Watches the words fly over his head* eh?

wscheiman · November 19th, 2003, 10:20 AM

My main point that I was trying to make was utilizing the .Requery
method against the combo box. I did not see it mentioned in any of
the previous replies - this is one of the most common problems
that occurs when attempting to update the contents of a combo box's
rowsource. Unless this has been updated otherwise as newer versions
of MS-Access have been released, the Requery method was required
once a combo box's RowSource was updated if you want to change it
while the form was open on the same record. Sorry, I didn't take the
last comment too well, that's all.

Anubis · November 19th, 2003, 10:55 AM

Don't be sorry, I'm glad you clarified. I really wasn't sure what you meant.

I apologize for not being more VBA savvy...

wscheiman · November 19th, 2003, 02:04 PM

You're doing fine, Anubis. :)

Looks like I need to be just a little more careful when
trying to answer stuff when I'm in the middle of something
else.

It looks like we have an excellent community of people out
here with alot of great ideas. Makes me wish that I had the
web and forums like this when I started.

Keep on jammin!

Warren

Anubis · November 19th, 2003, 02:29 PM

I'm still struggling with the rest of the objects on my InvUpd form...

jaip2p · November 20th, 2003, 12:19 PM

This is just a Suggestion. Did you try the SQL query in the Queries first? Make sure the query you wrote works. Some times you assume that it's the problem with the code and it happens to be the query. Just a suggestion.

Anubis · November 20th, 2003, 12:45 PM

yeah, the queries work like a champ. I've actually moved on from this problem because it is working now.

unfortunately, this was one step in a rather complicated inventory update process. I'm going to post a link to a screen shot of my form and explain the logic behind it as best I can.

Perhaps someone can give me some pointers on form design or tell me I'm on the right track and help me with the code...