Queries and Combo Box

[AndyEduardo]

Hello!

I have a form to make the invoice. It has one cbo (Information abou
Clients) and inside this form I have a subform whit a cbo (Products)
One client can to buy several products by an agreement.

I made a query to view the clients related with the products and
established this criteria
in the Clients field.....=Forms.....CboClients

I want this:

When I expand the second cbo, the list of products shows just th
products in the agreement based in the client of first cbo...

Thanks for your help

I apologize about mi english, I'm not a english-speake

 

[Allen Browne]

Andy, it sounds like you have an agreement with a client regarding products,
and you want to prevent the user from sellling products that are not covered
in an agreement.

If so, you will need an Agreement table that defines which products are
associated with which clients. The table will have fields:
ClientID foreign key to Client.ClientID
ProductID foreign key to Product.ProductID
You can therefore limit RowSource of the combo to products for the client.

This example goes in the AfterUpdate event of the ClientID combo in the main
form. Adjust the code to suit. The Current event of the main form needs to
call the same code, so it reloads the combo as you move record in the main
form.

Private Sub ClientID_AfterUpdate()
Dim strWhere As String
Const strcStub = "SELECT Agreement.ProductID, Product.ProductName FROM
Agreement INNER JOIN Product ON Agreement.ProductID = Product.ProductID
WHERE "
Const strcTail = " ORDER By ProductName;"

If IsNull(Me.ClientID) Then
strWhere = "(False)"
Else
strWhere = "ClientID = " & Me.ClientID
End If

Me.[YourSubformNameHere].Form!ProductID.RowSource = strcStub & strWhere
& strcTail
End Sub
Private Sub Form_Current()
Call ClientID_AfterUpdate
End Sub

Notes:
====
1. If ClientID is a Text field (not a Number field), you need extra quotes:
strWhere = "ClientID = """ & Me.ClientID & """"

2. If the ProductID combo's bound column is zero-width, it will appear blank
if it contains a value that is not in the list. For example, if you no
longer have an agreement to sell product 99 to a client, but you are looking
at one of their old orders where you did supply 99 to them, the combo won't
have the name for that product in its list, and so it will show a blank. (If
the ProductID's bound column is NOT zero-width, it might be more efficient
to use the Enter event of the ProductID combo instead of the events
suggested above.)

3. If you are not sure how to get the SQL statement right, create a mock
query into the 2 tables, and use any literal number in the Criteria under
your ClientID. Then switch to SQL View (View menu, in query design) for an
example.