RE: Subforms and Many-to-Many relationships

Veritas,

Thanks. I got the subform working with both the combo box for the list of
Products and the "Committed Funds" field. However, I am not sure how I can
get the product description to appear on the subform. the product description
is in the Product table, while my subform is based on CustomerProduct. I
tried adding a textbox to hold the description but could not find a Row
Source property for Text boxes, like the one for Combos.

Additionally, I would like to add a Delete button on the subform that would
allow the user to delete the current record. Before the delete takes place
though, I would like to ask the user for a confirmation.

Thanks for your help.






"Veritas" wrote:

Joshua,

Typically, a form shares a one-to-many relationship with a subform...
in your case, Customer & CustomerProduct.

Your main form Customer should be a bound form (i.e. it is linked to the
Customer table) so that when one updates the form... the data is being fed to
the underlying table, Customer...

Next, you would want to create a subform, you can use your toolbox to do
this... while in the design view of the parent form (Customer)... and base it
on the CustomerProduct table...

In the subform, you can create a combo-box that is based off a query...
something like SELECT Product.ID, Product.Name From Product;

This combo-box properties should have the first column product.id as ithe
column that you want to store or use in this database...

Check "Store that value in this field" and choose product_id...

Right click on the Combo box, go to Properties, Column Count should be 2
Column widths: 0"; 2.5"... this allows your user to be able to see the
product name without having to look at the code that stands for that product
(but the database will be storing the code in the underlying table)

Now in your subform your combobox replaces your product_id... (so you could
delete or hide the old product_id... ) I would hide customerproduct.id and
the customerproduct.customer_id as well... because the information in the
subform is correlated to whoever the customer is in the Main form... so there
is no need to see these codes

Now you must have a separate form for updating the Products... you can have
a link (utilizing the button functionality out of your toolbox) on your
Customer form that opens the Product form.... given my explanation of form
relationships above... and that the Product form would be based on the
Product table... so when the form is updated our query above will pull
whatever current information is in the Product table... On the Product
form, you can add a Close Form button... and by closing that form... you go
back to the previous form (which was never really closed)

Hope that helps,

Veritas



"Joshua6007" wrote:

Hi,

I am trying to build a form based on a many-to-many data model. There are 3
tables - Customer, Product and CustomerProduct which relates the first two
and also has a field for storing some information about that specific
combination (in this case it's a financial product and we want to track how
much money the customer has committed to the product, say a certain type of
portfolio).

Customer
------------
id
name
phone

Product
-----------------
id
name

Customer_product
---------------------------
id
product_id
customer_id
funds_committed

So I need a form for the Customer records which would have a subform that
contains the list of products and the appropriate "committed funds" amount
that the customer has purchased (or nothing if this is a new customer).
The subform will have a drop down box for the Product name, a non-editable
text/label box for Product Description ( both from the Product table) and a
text box for the committed funds (CustomerProduct).

I would like my users to use this form to potentially create a new Customer
record,

(when user does this, a new record should be inserted in the Customer table)

then to add a list of products for the newly created customer by selecting a
product from the Products dropdown and entering the amount of money that this
customer is committing to the product

(when the user does this, a new record should be inserted into the
CustomerProduct table with the id of the newly created user, the product id
from the product chosen in the Products dropdown in the subform, and the
value of the "funds committed" text box on the subform as well).

Additionally, I would like my users to use the same form for both updating
and deleting the list of existing products and their respective "committed
funds" fields, in addition to just adding new products.

While I am quite familiar with how to do this in a web based environment, I
am not sure how to accomplish this in Access. I would appreciate any tips or
suggestions you may have on how to approach/solve this problem. I built a
form that correctly displayed the customers and their corresponding list of
services, but the Insert/Update/Delete part didn't quite work.

Thanks.

.