Restrict values on two combo boxes

[delaikhi]

I have 2 combo boxes: Order_type and Order_Description (both come from 1 table) and displayed on 1 form. I want to restrict the values in Order_Description to related records in Order_type. I.e when an user select "Stationary" in Order_type, values in Order_Description would be restricted to: "pencil, eraser, pens."
Please tell me how to this?

 

[pr2-eugin]

What you are asking for is something that is called as Cascading Comboboxes.. Google the keywords there should be several hits..

http://fontstuff.com/access/acctut10.htm
http://www.fmsinc.com/microsoftaccess/forms/combo-boxes/cascading.html
http://www.599cd.com/tips/access/two-combo-boxes/
http://www.utteraccess.com/wiki/index.php/Cascading_Combo_Boxes

 

[delaikhi]

Thanks pr2-eugin, here is what I put in the after update of combo box: Order_Criteria:
Private Sub Order_Criteria_AfterUpdate()
Dim strSource As String
strSource = "SELECT Order_Criteria " & _
"FROM Order_Criteria " & _
"WHERE Order_Criteria = '" & Me.Order_Criteria & "' ORDER BY Order_Name"
Me.Order_Name.RowSource = strSource
Me.Order_Name = vbNullString
End Sub
And always end up with error. Can you please tell me what's wrong?

 

[pr2-eugin]

What is the Error you are getting (Error Number and Description). Have you debugged the error?

 

[delaikhi]

pr2_eugin, actually this is what happening:
After inserted the code in the afterupdate even of Order_Criteria combo box, whenever I choose any thing from this combo box, nothing happens with the Order_name, even worse, I cannot choose any thing in Order_criteria, much less about Order_name.

 

[delaikhi]

I put this code on the Order Form:
Private Sub Order_Product_AfterUpdate()
On Error Resume Next
Order_product_Name.RowSource = "Select Orders query.Product_Type" & _
"FROM Orders query " & _
"WHERE Orders query.Product_Type = '" & Order_product_Name.Value & "' " & _
"ORDER BY Orders query.Product_Code;"
End Sub
All I got is and error message: <Message> in query expression <expression>. (Error 3075). Please tell me whats wrong? Thanks

 

[pr2-eugin]

You still have not shown if you have debugged the error.. What is the Error?

On Error Resume Next is a very bad programming habit.. 3075 normally is syntax error.. Try assigning the Query to a String variable and use Debug.Print to see if there is any error in the Query.. http://www.baldyweb.com/ImmediateWindow.htm

You need a Requery statement following setting of the ComboBox recordsource..

Did you go through the links I provided in Post#2 completely?

 

[delaikhi]

Yeah, actually something is wrong with my designing of the table and queries. May be in the relationship.

 

[pr2-eugin]

This reply..

Yeah, actually something is wrong with my designing of the table and queries. May be in the relationship.

does not address anything I mentioned in Post#7

 

[delaikhi]

pr2-eugin, actually I visited all the links that you provided above, but don't actually know where I got lost
Seems to me, [URL="http://www.599cd.com/tips/access/two-combo-boxes/"/URL] is good gor my case, but problem is I am not sure why it comes up with no result

 

[pr2-eugin]

Post a dummy version of your DB..

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)