Hi RainLover, and thank you for your quick reply!
I appreciated very much your advice but I am trying to figure out Access capabilities with lookup fields and so on.
I know I can (better: I should) use a 3rd table to make "many-to-many" associations between tables, but in my case I am not trying to link customers with products, but customers with purchasing options, depending on the product they have choosen. Example:
product#1 can be purchased using Visa, Paypal and Check
product#2 can only be purchased using Visa
So I already use a table (ORDERS) to link customers with products (using as FK their own PKs) but I have also tried to use a multi value field in PRODUCTS (ALLOWED_OPTIONS) so that, when a customer place an order, he can choose only among the options flagged for that particular product.
Following your advice, I should create a 4th table:
ID_P_OPTION (PK, autonumber)
2 American Express
Then I would need a 5th table:
PRODUCT_CODE (FK, long integer)
OPTION_CODE (FK, long integer)
(PK on the two field as a pair)
So, if product#7 can be purchased using Visa and PayPal, the former table would contain:
The question remains quite the same: in the Form used to let a customer choose which product he wants to purchase, how to populate the combobox with the payment options, basing on the product choosen in that form, but not yet committed? Should I use some VBA event like "onExit" on the products combobox to reload the payment options combobox's items when the users changes the product? Or is there a simpler way? I just don't know what happens behind the scenes when the user types something into a field and goes to the next one without leaving the record yet. Is that value accessible and readable?
At work I use Oracle, and I know its triggers fire on events happening on the entire record (BeforeUpdate, AfterDelete,...). Access seems to me to mix Database and Application stuff, so for example it can trigger a validation routine as soon as the user leaves a field pressing TAB (Validation Rule field property). I am so confused!
If I am forced to rely on some VBA code to load the payment options items as soon as the user changes the order product, I could think it would be better to avoid creating 2 more tables just to represent 4 or 5 choices: I would parse the semicolon separated string ("Visa";"Am.Express";...) and split the values...
I hope someone could hep me to understand Access' logic...
Thank you again,