RE: Subforms and Many-to-Many relationships

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Joshua,

A text box is not going to facilitate what you are trying to do...
the combo-box is the key

Right click on the combo-box and go to the Data tab...
The control source should be set to Product_ID
(this is the CustomerProduct.Product_ID & CustomerProduct is what your
subform is based on)
Row Source Type: Table/Query
Row Source: SELECT Product.ID, Product.Name From Product;

(Where I assumed Product.Name is in fact the Product Description)
(Don't forget about the number of column headings and column widths
mentioned below)

Your subform... is based on the CustomerProduct table...
and one of the columns in that table is Product_ID...

Now your combo-box is essentially a query that says, show me every
Product.Id and the Product.Name from the table PRODUCT... (although if you
followed the instructions on the width of the columns... you will only be
able to see the Product name... and not the code (product.id)... but it is
still there, you just can't see it)

So the combo IS referencing what is in the PRODUCT table... and then when
you make a selection on that combo-box (of what is being purchased in the
subform[by whoever the customer in the main form]).... that product.id
number from the PRODUCT table is being placed into the CustomerProduct table

The CustomerProduct table exists for the sole purpose of matching customers
to the products that are being funded/bought... (an associative entity)

Customer to CustomerProduct is a one-to-many relationship
& Product to CustomerProduct is a one-to-many relationship

Customer-<CustomerProduct>-Product

So our combobox is a query looking up everything, i.e. ID and
Name/Description,
that is in the table Product, AND when a selection is made (for a customer
for a particular product) on that combobox... you are in fact adding (into
the CustomerProduct table) a record that matches the customer_ID to a
Product_ID...

As far as deleting records in the subform, I usually have my subform set to
a Data*** view... and keep my buttons in the main form...

I would create an action query that Deletes records FROM (the underlying)
CustomerProduct table WHERE CustomerProduct.ID =
Forms!Customer_frm!Linking_CustomerProduct_ID

where Customer_frm is whatever you named your parent form &
Linking_CustomerProduct_ID is a textbox you will have to add to your
parent-form


Right click on your text box & set the control source to the following
=[CustomerProduct_Subfrm].Form!ID

where CustomerProduct_Subfrm is whatever you named your subform


Once you have created a delete query for the CustomerProduct table... you
need
to create a macro... I like to SetWarningsOff in the first line (otherwise
you will get a pop-up of the number of records deleted... but if you want
you could leave it on as a safeguard)

Next line should be OpenQuery and near the bottom of your screen put in
whatever you named your Delete query

Last line should be SetWarnings On (if you turned them off)

and SAVE your Macro...

Next open your form in design view and add a button (from your toolbox) and
go to miscellaneous ->Run Macro
click Next & choose the macro that you created
now label your button (this what will be printed on your button [Delete
Product])
and name your button [Delete_Product]...

Right click on your button and go to the event tab


look at the "On Click" ... event procedure (should be in the box to the
RIGHT)
Click on the box with 3 dots (lookup button)
(You may have to click in the area where it says Event Procedure in order
for the lookup button to become visible)

Above where is says "DoCmd.RunMacro stDocName"


Dim prompt, buttons, title
prompt = "Are you sure you wish to delete the selected product?"
title = "WARNING"
buttons = vbYesNo
DeleteMessage = MsgBox(prompt, buttons, title)

If Response is No then
GoTo Exit_Delete_Product_Click
Else

Under where it says "DoCmd.RunMacro stDocName"
type the following
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

(this will refresh your subform without the data that our delete should have
removed from the underlying CustomerProduct table)

End If

Ctrl + S to save and X out... will take you back to your form... close the
form (it will ask you to save) save your changes & you're done!

Hope that helps...

Veritas


"Joshua6007" wrote:

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.

.

Quantcast