Form Usage

[crowegreg]

I can't figure out the best way to do this. I have two tables, one table containing merchants and the other table containing services. On the form that contains the merchant info, I need to display all of the records within the services table. I need the user to be able to select the services that apply to this merchant. I've thought about a list box because it allows the user to select multiple items to be applied to this merchant. I will then create another table which has the merchantid and the servicesid. I'll use this new table to confirm that this merchant is approved for this type of service.

Any thoughts on the best way to do this?

[RuralGuy]

You need a MainForm with the Merchant as the RecordSource and this MainForm has a SubForm of the services for this Merchant with the new table you are talking about as the RecordSource for the SubForm. The the "Service" control on the SubForm will be a ComboBox with the Services table as rthe RowSource. If you set it up right then the new table will end up containing it's own PrimaryKey (PK), the PrimaryKey of the Merchant table as a ForeignKey (FK) and PrimaryKey of the Services table as a ForeignKey (FK). Properly set up, Access will do all of the detail work for you *and* enforce Referential Integrity.

[crowegreg]

Thanks. I never thought of that. I'll give it a shot later tonight.

[RuralGuy]

Post back here if you need further assistance after you give it a try.

[crowegreg]

I think what I've done is incorrect. Attached is a copy of the DB.

Merchant table - wp_ebyz_merchant_info
Merchant form - frm_Merchant_Info
Product table - wp_cart66_products
services sub-form - frm_Merchant_Info_subform
table with services selected - wp_ebyz_product_matrix_merchant

Per your instructions, I've created a sub form for the Merchant form. The sub-forms record source is the matrix table. Their is a combo box using the products table as the record source.

So what I'm trying to do is have all the products displayed on the merchant form, with the merchant being able to select multiple products. Then with these selection a record is created within the matrix table. The user should be open to open the merchant form, select a merchant, and it will show all services available, and the previous ones selected designated.

Thanks again!!

[RuralGuy]

Here's what I had in mind.

[crowegreg]

Very Nice. That works for me. Thanks!!

[RuralGuy]

Glad we could help.