Subforms and Many-to-Many relationships

[Joshua6007]

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.

 

[Veritas]

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]

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]

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 Datasheet 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

 

[Marshall Barton]

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.

To display the description, change the combo box's RowSource
query to:
SELECT productID, productanme, productdescription
FROM Products
ORDER BY productanme

Set the combo box's ColumnCount to 3
Set the ColumnWidths property to something like 0";1";0" or
0";1";3" as desired.

Now add your text box and set its control source to an
expression like:
=combobox.Column(2)

 

[Joshua6007]

Thanks.

What I am trying to do now is to allow my users to search for the customer's
record based on the customer's name. In other words, when the form loads
there are no records displayed. Then as the user types in the customer's name
and/or phone number, the application searches and displays the records
matching the criteria.

I would also like to create 2 buttons - one to display all the records if
the user so chooses, and the other for creating new customer records.

Is this doable?

Thanks for your help.

 

[Joshua6007]

Thanks, Veritas.

My product name and description are two different fields. However I have
since removed the description field because it was too long and couldn't be
conveniently views on the form.

As for the Delete button, when I tried to create it, it gave me the option
to create a Delete button specifically, which is what I did and it worked
like a charm.

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 Datasheet 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

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.

 

[Marshall Barton]

Add a text box or combo box to the form's header where users
can enter or select the customer and/or phone. You can use
a separate button to search or you can use those control's
AfterUpdate event. either way, the code would be something
like:

Dim strWhere As String
If Not IsNull(customersearch) Then
strWhere = " And CustomerID = " & customersearch
End If
If Not IsNull(phonesearch) Then
strWhere = " And PhoneNum = """ & phonesearch & """"
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

To get the form to open with no records, use the form's Open
event, set the filter to an impossible value:
Me.Filter = "CustomerID = 0"
Me.FilterOn = True

Note that there are bugs with the Filter property when you
try to use it on both the main form and a subform. If at
some point in the future you try to do this, you will need a
slightly differnet approach.

As for your "All" and "New" buttons, I have to ask Why?
Displaying more than a few records is almost always
nonproductive for users to deal with and forms normally
display a new record after the existing records.

If you really feel the need for these, the "All" button
would be:
Me.FilterOn = False
and the "New" button would be:
DoCmd.GoToRecord acForm, Me.Name, acNewRecord

 

[Joshua6007]

Thanks, Marshall.

It works, sort of. Instead of creating a separate text box for doing the
search, I used the existing boxes for account_name and phone_number. In
other words, when the form opens, it has no records. Once the user types in
the account_name, I invoke the procedure. So that works, except that on the
form, it still shows 4 records, the first one being the correct record, the
other ones being blank with only the account_name populated with the user's
input.
I am not sure this is the bug you were referring to, cause my form does have
a subform.

The reason I would need a new button is that I would like my users to create
a new record using this form. I am thinking that the OnUpdate trigger for
account_name should be reset when inserting a new record, so that the form
actually saves the record and doesn't try to search for it. Is this correct?

Finally, on this form I need to display a calculated number based on the set
of products that customer is buying. What would be a good trigger for this
code. To remind you, I have a main form with customer information and a
subform with a list of products that the customer has purchased. I remember
reading something about the records not being inserted into the table until
after a form is closed. How would this affect my form? And also how would
this work, then the customer is inserting a new customer/product record?

Again, thanks for your help.

Add a text box or combo box to the form's header where users
can enter or select the customer and/or phone. You can use
a separate button to search or you can use those control's
AfterUpdate event. either way, the code would be something
like:

Dim strWhere As String
If Not IsNull(customersearch) Then
strWhere = " And CustomerID = " & customersearch
End If
If Not IsNull(phonesearch) Then
strWhere = " And PhoneNum = """ & phonesearch & """"
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

To get the form to open with no records, use the form's Open
event, set the filter to an impossible value:
Me.Filter = "CustomerID = 0"
Me.FilterOn = True

Note that there are bugs with the Filter property when you
try to use it on both the main form and a subform. If at
some point in the future you try to do this, you will need a
slightly differnet approach.

As for your "All" and "New" buttons, I have to ask Why?
Displaying more than a few records is almost always
nonproductive for users to deal with and forms normally
display a new record after the existing records.

If you really feel the need for these, the "All" button
would be:
Me.FilterOn = False
and the "New" button would be:
DoCmd.GoToRecord acForm, Me.Name, acNewRecord
--
Marsh
MVP [MS Access]

What I am trying to do now is to allow my users to search for the customer's
record based on the customer's name. In other words, when the form loads
there are no records displayed. Then as the user types in the customer's name
and/or phone number, the application searches and displays the records
matching the criteria.

I would also like to create 2 buttons - one to display all the records if
the user so chooses, and the other for creating new customer records.

 

[Marshall Barton]

The search text box's ***MUST*** be separate, unbound
controls. If they are bound, your search values will be
added to your table as new records, just as you are seeing.
I put them in the form header section and set their
BackColor to a pale green so users can see that they are
different from the usual data bound controls.

No, that's not the Filter bug I was warning about. The bug
gets in the way when you try to use the Filter property on
the main form AND one or more of its subforms at the same
time.

If a form has it's AllowAdditions property set to Yes
(True), then users can just navigate to the new record and
enter the data. There is no need for any additional
mechanisms. Your attempt to combine the search and data
entry controls is just twisting your form all out of shape.
I really recommend that you put the New button idea on hold
until you become familiar with how bound forms normally deal
with this issue. Actually, if you have the form's
NavigationButtons property set to Yes, Access will
automatically provide a New button [ * ].

You need to reset your brain and stop looking for a code
solution to everything. Only use code when there is no
built in feature to do what you need. To calculate a total
over all the records in a form (or subform), you can use a
text box with an expression in it's ControlSource property.
For example, you can calculate a total of invoice line with
an expression like =Sum(Quantity * Price). The functions
that operate on a collection of records are called Aggregate
Functions (Count, Sum, Avg, etc). Under normal
circumstancem, calculated values should NOT be saved to a
table. A total should be recalculated whenever you need to
see it.

A modified or created record on a bound form is
automatically saved to its table whenever you do anything
that might compromise the records values. A modified record
must certinly be saved when you close a form, and a modified
record must also be saved when you navigate to a different
record or just by moving to focus from a main form to a
subform or vice versa. (Another way to think about it is
that a main form and all of it's subforms can never have
more than one unsaved record between them.) There are also
several things you can do in code that will either directly
or indirectly cause a record to be saved.

 

[Joshua6007]

The reason that I do need to write code to do the calculation is that some of
the data required for the calculation resides in tables not related to the
form/subform directly.
The products I am working with are complex and could have several different
types of costs associated with them. Moreover, the cost can change based on
the amount. If you think there is an easy way to do this, do let me know. For
now I am just planning to write some code to pull the data (not on the form)
from the appropriate tables and do the calculations. That's why I was asking
you whether the data in my subform should be accessed directly, or whether I
should go to the underlying table and get it from there (just like the data
not displayed in the from/subform). I guess you are saying that those should
be accessed directly and would not be available in the table (unless the user
goes to a different record, etc).

If you could elaborate on how to put a text box on the footer of the main
form that calculates something (e.g. Sum) on the data displayed in the
subform, that would be great.
My calculation would probably require additional work because as I mentioned
not all the data is in the form or the subform, but at least it will give me
an idea on how to access subform data(set) from my code.

The search text box's ***MUST*** be separate, unbound
controls. If they are bound, your search values will be
added to your table as new records, just as you are seeing.
I put them in the form header section and set their
BackColor to a pale green so users can see that they are
different from the usual data bound controls.

No, that's not the Filter bug I was warning about. The bug
gets in the way when you try to use the Filter property on
the main form AND one or more of its subforms at the same
time.

If a form has it's AllowAdditions property set to Yes
(True), then users can just navigate to the new record and
enter the data. There is no need for any additional
mechanisms. Your attempt to combine the search and data
entry controls is just twisting your form all out of shape.
I really recommend that you put the New button idea on hold
until you become familiar with how bound forms normally deal
with this issue. Actually, if you have the form's
NavigationButtons property set to Yes, Access will
automatically provide a New button [ * ].

You need to reset your brain and stop looking for a code
solution to everything. Only use code when there is no
built in feature to do what you need. To calculate a total
over all the records in a form (or subform), you can use a
text box with an expression in it's ControlSource property.
For example, you can calculate a total of invoice line with
an expression like =Sum(Quantity * Price). The functions
that operate on a collection of records are called Aggregate
Functions (Count, Sum, Avg, etc). Under normal
circumstancem, calculated values should NOT be saved to a
table. A total should be recalculated whenever you need to
see it.

A modified or created record on a bound form is
automatically saved to its table whenever you do anything
that might compromise the records values. A modified record
must certinly be saved when you close a form, and a modified
record must also be saved when you navigate to a different
record or just by moving to focus from a main form to a
subform or vice versa. (Another way to think about it is
that a main form and all of it's subforms can never have
more than one unsaved record between them.) There are also
several things you can do in code that will either directly
or indirectly cause a record to be saved.
--
Marsh
MVP [MS Access]

It works, sort of. Instead of creating a separate text box for doing the
search, I used the existing boxes for account_name and phone_number. In
other words, when the form opens, it has no records. Once the user types in
the account_name, I invoke the procedure. So that works, except that on the
form, it still shows 4 records, the first one being the correct record, the
other ones being blank with only the account_name populated with the user's
input.
I am not sure this is the bug you were referring to, cause my form does have
a subform.

The reason I would need a new button is that I would like my users to create
a new record using this form. I am thinking that the OnUpdate trigger for
account_name should be reset when inserting a new record, so that the form
actually saves the record and doesn't try to search for it. Is this correct?

Finally, on this form I need to display a calculated number based on the set
of products that customer is buying. What would be a good trigger for this
code. To remind you, I have a main form with customer information and a
subform with a list of products that the customer has purchased. I remember
reading something about the records not being inserted into the table until
after a form is closed. How would this affect my form? And also how would
this work, then the customer is inserting a new customer/product record?

 

[Marshall Barton]

If you can use a query across all the needed tables, it will
probably be a lot faster than doing it all with loops and
searches through recordsets, but your problem may or may not
be ameanable to a straight query approach. This kind of
query can get more than a little tricky so you might want to
hang out in the queries group while you get something put
together. Just to give you a vague idea od what can be done
with a single query, I had one project that had a query with
criteria of material grade and total square feet and from
that it returned the total cost from the lowest cost
supplier (including quantity discounts, weight minimums,
etc) along with the total weight and shipping costs from the
supplier's location.

Aggegate functions in the header or footer section of a form
can only operate on fields in the form's record source
table/query. they are unaware of controls on the
form/report. you can get around that restriction by using a
user defined function that uses one or more fields as
arguments. This means that you must use the subform's
header/footer section to calculate the subform total. To
display that total on the main form/report, you need a main
form text box that refers to the subform total text box.
E.g. =subformcontrol.Form.totaltextbox
or if you need to see 0 when the subform has no records:
=IIf(IsError(subformcontrol.Form.totaltextbox), 0,
subformcontrol.Form.totaltextbox))
--
Marsh
MVP [MS Access]

The reason that I do need to write code to do the calculation is that some of
the data required for the calculation resides in tables not related to the
form/subform directly.
The products I am working with are complex and could have several different
types of costs associated with them. Moreover, the cost can change based on
the amount. If you think there is an easy way to do this, do let me know. For
now I am just planning to write some code to pull the data (not on the form)
from the appropriate tables and do the calculations. That's why I was asking
you whether the data in my subform should be accessed directly, or whether I
should go to the underlying table and get it from there (just like the data
not displayed in the from/subform). I guess you are saying that those should
be accessed directly and would not be available in the table (unless the user
goes to a different record, etc).

If you could elaborate on how to put a text box on the footer of the main
form that calculates something (e.g. Sum) on the data displayed in the
subform, that would be great.
My calculation would probably require additional work because as I mentioned
not all the data is in the form or the subform, but at least it will give me
an idea on how to access subform data(set) from my code.

The search text box's ***MUST*** be separate, unbound
controls. If they are bound, your search values will be
added to your table as new records, just as you are seeing.
I put them in the form header section and set their
BackColor to a pale green so users can see that they are
different from the usual data bound controls.

No, that's not the Filter bug I was warning about. The bug
gets in the way when you try to use the Filter property on
the main form AND one or more of its subforms at the same
time.

If a form has it's AllowAdditions property set to Yes
(True), then users can just navigate to the new record and
enter the data. There is no need for any additional
mechanisms. Your attempt to combine the search and data
entry controls is just twisting your form all out of shape.
I really recommend that you put the New button idea on hold
until you become familiar with how bound forms normally deal
with this issue. Actually, if you have the form's
NavigationButtons property set to Yes, Access will
automatically provide a New button [ * ].

You need to reset your brain and stop looking for a code
solution to everything. Only use code when there is no
built in feature to do what you need. To calculate a total
over all the records in a form (or subform), you can use a
text box with an expression in it's ControlSource property.
For example, you can calculate a total of invoice line with
an expression like =Sum(Quantity * Price). The functions
that operate on a collection of records are called Aggregate
Functions (Count, Sum, Avg, etc). Under normal
circumstancem, calculated values should NOT be saved to a
table. A total should be recalculated whenever you need to
see it.

A modified or created record on a bound form is
automatically saved to its table whenever you do anything
that might compromise the records values. A modified record
must certinly be saved when you close a form, and a modified
record must also be saved when you navigate to a different
record or just by moving to focus from a main form to a
subform or vice versa. (Another way to think about it is
that a main form and all of it's subforms can never have
more than one unsaved record between them.) There are also
several things you can do in code that will either directly
or indirectly cause a record to be saved.

It works, sort of. Instead of creating a separate text box for doing the
search, I used the existing boxes for account_name and phone_number. In
other words, when the form opens, it has no records. Once the user types in
the account_name, I invoke the procedure. So that works, except that on the
form, it still shows 4 records, the first one being the correct record, the
other ones being blank with only the account_name populated with the user's
input.
I am not sure this is the bug you were referring to, cause my form does have
a subform.

The reason I would need a new button is that I would like my users to create
a new record using this form. I am thinking that the OnUpdate trigger for
account_name should be reset when inserting a new record, so that the form
actually saves the record and doesn't try to search for it. Is this correct?

Finally, on this form I need to display a calculated number based on the set
of products that customer is buying. What would be a good trigger for this
code. To remind you, I have a main form with customer information and a
subform with a list of products that the customer has purchased. I remember
reading something about the records not being inserted into the table until
after a form is closed. How would this affect my form? And also how would
this work, then the customer is inserting a new customer/product record?


:

Add a text box or combo box to the form's header where users
can enter or select the customer and/or phone. You can use
a separate button to search or you can use those control's
AfterUpdate event. either way, the code would be something
like:

Dim strWhere As String
If Not IsNull(customersearch) Then
strWhere = " And CustomerID = " & customersearch
End If
If Not IsNull(phonesearch) Then
strWhere = " And PhoneNum = """ & phonesearch & """"
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

To get the form to open with no records, use the form's Open
event, set the filter to an impossible value:
Me.Filter = "CustomerID = 0"
Me.FilterOn = True

Note that there are bugs with the Filter property when you
try to use it on both the main form and a subform. If at
some point in the future you try to do this, you will need a
slightly differnet approach.

As for your "All" and "New" buttons, I have to ask Why?
Displaying more than a few records is almost always
nonproductive for users to deal with and forms normally
display a new record after the existing records.

If you really feel the need for these, the "All" button
would be:
Me.FilterOn = False
and the "New" button would be:
DoCmd.GoToRecord acForm, Me.Name, acNewRecord


Joshua6007 wrote:
What I am trying to do now is to allow my users to search for the customer's
record based on the customer's name. In other words, when the form loads
there are no records displayed. Then as the user types in the customer's name
and/or phone number, the application searches and displays the records
matching the criteria.

I would also like to create 2 buttons - one to display all the records if
the user so chooses, and the other for creating new customer records.


Joshua6007 wrote:
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.


:
To display the description, change the combo box's RowSource
query to:
SELECT productID, productanme, productdescription
FROM Products
ORDER BY productanme

Set the combo box's ColumnCount to 3
Set the ColumnWidths property to something like 0";1";0" or
0";1";3" as desired.

Now add your text box and set its control source to an
expression like:
=combobox.Column(2)

 

[Joshua6007]

Well, I have about 10 years of RDBMS experience, so I am not that new to
queries The challenge with Access is figuring out where to write those
queries especially in situations where half the data needed for the
calculation is in the form (and isn't yet in the table) while the other half
is in the tables.

I am also having a lot of issues with being able to reference one subform
from the other. In other words, while code that allows me reference subform2
from subform1, does not quite work when referencing subform1 from subform2.

The syntax I am using in subform1 is:

Me.Parent.CustomerProductSubform2.Form.(...)

This doesn't work initially (it's triggered by On Current) because I guess
Subform2 hasn't loaded yet, but once you get passed the initial load, it does
work.
So the question is how to get this to work initially?

The reason I need this to work is that when the first subform is loaded, the
cursor is on a certain product. Based on this product's type, the second
subform will need to be populated with the correct set of parameters for this
product type. I am trying to do this by displaying all the possible
parameters in datasheet view, then making the parameters not relevant to this
product invisible.

So to solve this issue, I decided to code something for subform2's On Load
event so that it fixes itself when it is loaded (while also figuring out a
way to stop the code from executing in subform1 when subform2 hasn't yet
loaded). Which is where I can't reference subform1.

So my other question is when I try to use the same code to access subform1
from subform2, I get the following error - "Application-defined or
object-defined error" (of all the useless error messages I have seen, this
one probably takes the prize)

Me.Parent.CustomerProductSubform2.Form.Controls.Count

Any ideas would be appreciated.

 

[Marshall Barton]

Well, I have about 10 years of RDBMS experience, so I am not that new to
queries The challenge with Access is figuring out where to write those
queries especially in situations where half the data needed for the
calculation is in the form (and isn't yet in the table) while the other half
is in the tables.

I am also having a lot of issues with being able to reference one subform
from the other. In other words, while code that allows me reference subform2
from subform1, does not quite work when referencing subform1 from subform2.

The syntax I am using in subform1 is:

Me.Parent.CustomerProductSubform2.Form.(...)

This doesn't work initially (it's triggered by On Current) because I guess
Subform2 hasn't loaded yet, but once you get passed the initial load, it does
work.
So the question is how to get this to work initially?

The reason I need this to work is that when the first subform is loaded, the
cursor is on a certain product. Based on this product's type, the second
subform will need to be populated with the correct set of parameters for this
product type. I am trying to do this by displaying all the possible
parameters in datasheet view, then making the parameters not relevant to this
product invisible.

So to solve this issue, I decided to code something for subform2's On Load
event so that it fixes itself when it is loaded (while also figuring out a
way to stop the code from executing in subform1 when subform2 hasn't yet
loaded). Which is where I can't reference subform1.

So my other question is when I try to use the same code to access subform1
from subform2, I get the following error - "Application-defined or
object-defined error" (of all the useless error messages I have seen, this
one probably takes the prize)

Me.Parent.CustomerProductSubform2.Form.Controls.Count

Alright! Good experience, just new to Access ;-)

You have already figured out that subforms are loaded before
the main form and that you can not refer to a subform until
it is loaded. One thing that may (or may not) help is that
the subforms are loaded in the order that they were created
in the main form. If that could be useful, you can Cut the
one you want to be loaded last, then Paste it back. If that
doesn't resolve that issue, then try trapping the error and
ignoring it or just exiting the procedure.

Are you sure that each subform must reference the other one?
Can't you arrange it so that the second subform pulls the
information from the first subform (instead of the first
subform trying to push the data into the not yet loaded
subform)?

If the reason for all this is to set up criteria for a
query, it may be unnecessary. You can use the appropriate
references in the query. E.g.
Forms!mainform.subform.Form.textbox