How to apply filter to combo box?

[joycey]

Hi guys,


I have designed a client database which works, but am stuck on one area involving a combo box on a subform and its relation to the record displayed on the main form. I believe this may involve VBA as I cant imagine a standard SQL script doing what I need.


There are 4 tables linked...

CLIENT > ORDER > CARDORDER > CARDS and the linked fields...
client_id.. order_id.. order_id...... card#
..............client_id.. card#


My main form displays details for the ORDER Table with an ORDER_ID and the subform is for the CARDORDER (where the combo box is located to either enter a new card number or chose an existing number.


My issue is that all card numbers are displayed (obviously) when clicking on the combo box, whereas I need it to just display the card numbers that have been previously issued for the client on the order (a card can only be issued to one client, but a client can have multiple cards, aswell as multiple orders). In my childs mind, I would imagine a sql statement to be something like this BUT I don't know how to populate the client_id (but I know this is wrong)...


SELECT card#
FROM cardorder
WHERE order.client_id = (SELECT client_id
..................................from order
..................................WHERE order.client_id = client.client_id)


If I have designed my database incorrectly (although it appears to work), I would be grateful of anyones input, alternatively if someone out there can advise, how to filter the results on my combo box with direct relation to the main form (order_id) & the client_id within i would be eternally grateful.


Cheers in advance


Paul

 

[Rx_]

See attached:
There are several ways to do this.
Attached: partial shot of a query - the filter on the [ID_Wells] is what links the parent form to the sub-form.
There is a list box on the parent's form named [Home_2], the first (hidden) column is the [ID_Wells]. So, the selected value in the list box is [ID_]Wells]
Now, the sub form query is linked to the value in the parent list box.

This could just as easy be a text box or other object on the parent form.
Check out the Sample Databases in this site.
There are several good examples of sub-forms that show more than one way to do the same thing.

 

[joycey]

Hi Rx.

You are a star - thank you.

Paul

 

[joycey]

Hi Rx,

As per my previous post... yes it worked but in the process destroyed a not-in-list event that I have for the combo box to work.

What happens now is that when you click on the combo box - it now only brings up previous cards issued to that person, but on typing a new card (not previously entered), I'm prompted to Add the number in to the CARD table, so I click YES and get the "The Text you entered isn't an item in the list" - however the card IS entered in to the CARD table (but a record isn't created in the CARDORDER table along with the ORDER_REF from the ORDER Table).

The code that I use is...

Private Sub CARD_NO_NotInList(NewData As String, Response As Integer)
Dim strTmp As String

'Get confirmation that this is not just a spelling error.
strTmp = "Add '" & NewData & "' as a new Travelex Card?"
If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then

'Append the NewData as a record in the Categories table.
strTmp = "INSERT INTO CP_CARDS ( CARD_NO ) " & _
"SELECT """ & NewData & """ AS CP_CARDS;"
DBEngine(0)(0).Execute strTmp, dbFailOnError

'Notify Access about the new record, so it requeries the combo.
Response = acDataErrAdded
End If
End Sub


Any suggestions?

Cheers

Paul

 

[joycey]

The SQL statement is this...

SELECT CP_CARDS.CARD_NO
FROM (CLIENT INNER JOIN CP_ORDER ON CLIENT.CLIENT_ID = CP_ORDER.CLIENT_ID) INNER JOIN (CP_CARDS INNER JOIN CP_ORDERCARDS ON CP_CARDS.CARD_NO = CP_ORDERCARDS.CARD_NO) ON CP_ORDER.ORDER_REF = CP_ORDERCARDS.ORDER_REF
WHERE (((CLIENT.CLIENT_ID)=[Forms]![CP_ORDER]![ClientIDcombo]))
ORDER BY CP_CARDS.CARD_NO;

Cheers

Paul