Hi guys,
You will have to forgive my access ignorance, but I am what the kids call a noob to access & the world of database design (inc VBA, Macros etc) - so I do apologise in advance if the answer lies within these forum walls or if I'm posting in the wrong place.
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 an ORDER_ID and the subform is for the CARDORDER, where I have a combo box to either enter a new card number or chose an existing number (I have used a not-in-list code to submit in case its not listed).
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...
SELECT card#
FROM cardorder
WHERE cardorder.order_id = (SELECT order_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
You will have to forgive my access ignorance, but I am what the kids call a noob to access & the world of database design (inc VBA, Macros etc) - so I do apologise in advance if the answer lies within these forum walls or if I'm posting in the wrong place.
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 an ORDER_ID and the subform is for the CARDORDER, where I have a combo box to either enter a new card number or chose an existing number (I have used a not-in-list code to submit in case its not listed).
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...
SELECT card#
FROM cardorder
WHERE cardorder.order_id = (SELECT order_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
Last edited:
