Combo box to find info for a specific contact

[MandyS]

I have 4 tables
Table Contacts - with a primary key of ContactID plus other contact information.

Table Adverts
AdvertID primary key autonumber
ContactID foreign key.
A contact can have many adverts

Table Payment Details
payment type ID primary autonumber key
foreign key of contactID
payment type (card cheque etc)
card number
expiry date etc.
One contact could have many payment methods - e.g. they might pay by cheque, Visa or Mastercard. We want to hold all their preferences, with card numbers and expiry dates, rather than overwriting this information when they change between payment methods

Table Orders
OrderID - autonumber primary key
AdvertID - foreign key (One advert can have multiple orders.)
Date of order
Payment method for this order (to be selected from the payment details table.

I have created a main form to enter adverts and orders (both sub forms). I want to have a combo box (or list) on the order sub form which allows me to select from a list of this customer's payment methods (only this customer). Currently my combo box contains all customers payment methods in a (very) long list

My relationships appear to be set up correctly, but I just can't work out what I need to do.
Thanks

 

[Rich_Lovina]

Your outline of tables looks somewhat similar to sample Northwinds dbs. Have you compared that layout with your own and their series of forms.

Sounds similar to a dbase i once had in which I found better to not try and subform all onto the one form, unless you are well into VB.

 

[MandyS]

Thanks for the pointers. I'm nearly finished and everything else works OK, apart from this one little combo box.
However,I'm barely a novice at Access let alone VB.
One of the problems I found with the Northwind database is that they handily avoid anything to do with payment information and only seem to work up to invoice stage.
I'll keep looking but I can't find an example of this to learn from. Have even got Access 2002 Bible

 

[MandyS]

I'm still stuck with this one. I have tried adding criteria to a query to restrict the results to where the contact ID in the sub form is equal to the main form - Forms![Form Name}.[contactID], but I'm being prompted for the parameter value.
I did find another post for the same problem from July 2000, but there was no answer to it. I'm convinced it is possible, but my lack of knowledge is holding me back.
Any help appreciated