I am new to Access and have been working with the northwinds as a template, and was wondering if you could help me figure how to select a catory first then a product within the category in the order subform. I have it working right now where when you select a product it shows all products. I would like to be able to first select a category, then a product in the category.
Maybe I need to create another form, but I am not sure. I am new to this and any help would be Helpful to me!
Perhaps I should have been more explicit. Perhaps I should have said, "Perhaps if you post a copy of the mdb file you are working with, WHICH HAS THE MODIFICATIONS YOU ARE ATTEMPTING TO MAKE WORK, I can take a look at it and let you know."
What you attached does not indicate any such modifications as you mentioned in your original post. It does not even show a "Category" Combo Box added in the Orders Subform, nor do I see my code posted ANYWHERE in the entire database. It would appear that your work is nowhere to be located in the file.
If you do not show me exactly what it was that did not work, I won't have any scenario from which to duplicate the problems you are encountering. Thus, it will not be possible to help you.
Please forgive me if I seem overly cross on this point. Try to understand: in a free question/answer forum, you are likely to find help from many when you are seeking guidance in your efforts to make something work. In the same environment, you are likely to receive little or no help at all if you ask someone to "do it all for you".
If you could re-attach the file (with the attempted modifications, this time), I will try to help you.
Okay, time for some re-vamping if you want to get this to work.
First, open the table "Order Details" in design view. Insert a field named "CategoryName" as a text field, with a 15 character limit, and set index to "Yes (Duplicates OK)". Save the new table settings.
Next, open the query "Order Details Extended" in design view. Insert the newly added field "CategoryName" into the query. Save the new query settings.
Finally, open the form "Orders Subform" in design view and do the following:
* Rename your combo box currently named "Combo18" to "CategoryName".
* Set the combo box's Control Source to CategoryName.
* Set the combo box's Row Source to:
SELECT Categories.CategoryName, Categories.Description FROM Categories;
* Set the combo box's Column Width's to 1";2"
* Set the combo box's List Width to 3"
The following is my code, adapted for this specific instance, to be pasted into the form's Code Module:
Private Sub ProductID_GotFocus()
Me.ProductID.RowSource = "SELECT DISTINCTROW [ProductID], [ProductName],[Discontinued] " _
& "FROM Products " _
& "WHERE [Category]='" & Me.Category _
& "' ORDER BY [ProductName];"
Also, make sure, in datasheet view, that you have the Category combo box before the Product box, for the user's sake of selecting the category first.
I am not sure where to put your code. I put it in the code Module, and keep getting an error that says method or data memeber is not found. I am wondering if I am putting it in the wrong part. I attached a picture of the error.
I am putting the code at the top of the code that says General on the left and Delclarations on the right. Am I suppsed to put it somewhere else? Because when I got to change form code view to datasheet view The productID is on the left and gotfocus is on the right and I get that error.
I think I almost have other than that..... Thanks so much for your help. I am understanding these alot better now!
One of the problems with trying to make modifications to someone else's code is that there are too many things that are easy to overlook. If this has been a from-the-ground-up application, it would have been MUCH easier to do.
Try the following:
Open the table "Order Details" in design view. Change the field type for the field "CategoryName" to a Number. Save the new table settings.
Open the form "Orders Subform" in design view. Change the Row Source for the CategoryName ComboBox to:
SELECT DISTINCTROW [CategoryID], [CategoryName] FROM Categories ORDER BY [CategoryName];
Set the Column Widths to: 0";2.2";1"
In the Code Module, change the following lines in the sub I gave you:
& "WHERE [Category]='" & Me.CategoryName _
& "' ORDER BY [ProductName];"
& "WHERE [CategoryID]=" & Val(Nz(Me.CategoryName, 0)) _
& " ORDER BY [ProductName];"
See if this fixes the problem.
On another note, the database you chose to experiment with was not the best choice for attempting this type of modification. I'd try working with much simpler templates if I were you, or better yet, start learning to build your own from scratch.
I do not know how to thank you enough for helping me. I am very greatful. I know I picked a hardone, but it was one that had almost everything I need, so I thought it would be easy to fix. I was way wrong. You are an asset to this board and I thank you for all of your help!