Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Location
    Colusa, CA
    Posts
    13

    Arrow Unanswered: MS Access question

    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!

  2. #2
    Join Date
    Feb 2004
    Posts
    137
    Let's say in the Order Subform you have two combo boxes: CategoryName and ProductName. Paste the following code into the Code Module for the form:

    Private Sub ProductName_GotFocus()
    Me.ProductName.RowSource = "SELECT DISTINCTROW [ProductID], [ProductName],[Discontinued] " _
    & "FROM Products " _
    & "WHERE [ProductName]='" & Me.Category _
    & "' ORDER BY [ProductName];"
    Me.ProductName.Requery
    End Sub


    This should apply the filtering method you're looking for.

  3. #3
    Join Date
    Apr 2004
    Location
    Colusa, CA
    Posts
    13
    I tried that, and I am not sure I did it corecctly because nothing happened. Do I have to have the two combo boxes bound by something. Can you please give me a alittle more direction. Thanks

  4. #4
    Join Date
    Feb 2004
    Posts
    137
    Perhaps if you post a copy of the mdb file you are working with, I can take a look at it and let you know.

  5. #5
    Join Date
    Feb 2004
    Posts
    137
    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.

  6. #6
    Join Date
    Feb 2004
    Posts
    137
    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];"
    Me.ProductID.Requery
    End Sub


    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.



    See if this works for you.

  7. #7
    Join Date
    Apr 2004
    Location
    Colusa, CA
    Posts
    13
    Mr. Reeves,


    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!
    Attached Files Attached Files

  8. #8
    Join Date
    Feb 2004
    Posts
    137
    Whoops! My bad! That line should read:

    & "WHERE [Category]='" & Me.CategoryName _


    ...because "CategoryName" is the name of the renamed Combo Box (you did rename it, didn't you?)


    With that one change, it should work.

  9. #9
    Join Date
    Apr 2004
    Location
    Colusa, CA
    Posts
    13
    Dear Mr. Reeves.


    After I inserted your code I got went to got select a category and when I went to click on the Product I got a box that says enter parameter Value

    Category.

    I am not sure why this is coming up, up when I canel it, it gives me a null Value in the product combo box and when I type in a Category Name it gives me all the products.

    What did I do wrong?

    Thanks
    Attached Files Attached Files

  10. #10
    Join Date
    Feb 2004
    Posts
    137
    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:

    from
    & "WHERE [Category]='" & Me.CategoryName _
    & "' ORDER BY [ProductName];"

    to
    & "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.

  11. #11
    Join Date
    Apr 2004
    Location
    Colusa, CA
    Posts
    13
    Mr. Reeves,

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •