Dynamic criteria Query

[ser01]

Hi all!


I am new to this forum and just as new to Access. I am working on creating a database for my company that would complement our existing order entry system and I finally got to a point where I am stock.
So at this point I have a form with some customers on it. Next to each customer on the form there is a Command Button. What I need to have happening is when that button is pressed, a new form would open that would contain some info about the customer in the header (if possible) but most importantly it would contain only the product that the particular customer orders (the same customers order the same products every week). I have 2 tables. One contains all the customers and each customers has a unique ID which is also the primary key for the table. The second table contains all the product that each customer orders. I can retrieve the data with a Select Query that has the customer ID as the criteria ( ie Like "*A001"). The problem is that I need this condition to be dynamic and be determined, when the above mentioned button on the form is pressed.
I would greatly appreciate any advise and hope that my explanation is clear!

[pbaldy]

It sounds like this technique would work:

http://www.baldyweb.com/wherecondition.htm

[ser01]

It sounds like this technique would work:

http://www.baldyweb.com/wherecondition.htm

Thank you for your speedy reply. Unfortunately in can not find a way to make the linked suggestions work for me

[pbaldy]

How about

DoCmd.OpenForm "SecondFormName", , , "CustomerID Like '*" & Me.ControlName & "'"

[ser01]

How about

DoCmd.OpenForm "SecondFormName", , , "CustomerID Like '*" & Me.ControlName & "'"

Well i found a snippet of code that does get me started:

Private Sub Command6_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = Application.CurrentDb
Set qdf = db.QueryDefs("qrySelectProduct")

strSQL = "SELECT tblCustomerProduct.[Prod Code], tblCustomerProduct.[Prod Description] FROM tblCustomerProduct WHERE (((tblCustomerProduct.[Cust Code]) Like '*A003'));"

qdf.SQL = strSQL
DoCmd.OpenForm "frmSelectProduct"

End Sub

Now I have 2 problems:
1. Even though i got the button to change the criteria it is still inputted in the code. How would i retrieve the above "A003" automatically. That is the customer ID that coresponds to the customer next to the Command Button? It is also not present the form, but it is in the forms query.
2. How do I make this code work for all the buttons on the form?

[pbaldy]

That's pretty much a more complicated way of accomplishing the same goal. Just as in my example, you'd concatenate a form reference into the SQL instead of hard-coding the value.

[ser01]

That's pretty much a more complicated way of accomplishing the same goal. Just as in my example, you'd concatenate a form reference into the SQL instead of hard-coding the value.

Solved! I actually got it to work. Tank you very much!

[pbaldy]

No problem, glad you got it working.