Automated field information does not go to the fields in the table

I have a form that has automated fields for putting the address in when a
customer is selected. I use a combobox for the customer with columns
CustomerID / CustomerName / Address / Phone . Then linking the controlsource
of an unbound text control to it by = cbocustomername.Column (2). But the
problem i hace now is that the address and customername does not go into the
table but the customerID and all the other information does. Does anyone know
why the customer and address info isnt going into the fields in the table
and what can i do to fix it.

Natasha

Natasha wrote:
I have a form that has automated fields for putting the address in
when a customer is selected. I use a combobox for the customer with
columns CustomerID / CustomerName / Address / Phone . Then linking
the controlsource of an unbound text control to it by =
cbocustomername.Column (2). But the problem i hace now is that the
address and customername does not go into the table but the
customerID and all the other information does. Does anyone know why
the customer and address info isnt going into the fields in the
table and what can i do to fix it.

Natasha

You need to think about how bound forms and controls work.

A form is bound to a table by having the name of that table as its RecordSource
property (or the name of a query based on that table).

A control is bound to a field by having the name of that field as its
ControlSource property.

Your address, phone, etc., controls do not have the names of fields in their
ControlSource properties so they are NOT going to save their data to the table.
How would Access know where to put the data? It needs to get that information
from the ControlSource and you have expressions in there instead of field names.

So, controls can either be bound to fields (and save their data there) OR
contain an expression. They cannot do both at once.

Now to solve your problem. In most cases like this you do not want to save the
customer address data into the table that your form is bound to. It is already
stored in the Customer table and you should not make multiple copies of such
data all over your database. All you need is the CustomerID and then you use
Queries joining the two tables together or other lookup mechanisms (like your
ComboBox columns) to *display* the additional customer data when you need it.

Reference or "Lookup" tables should not be thought of as places to go get data
so it can be copied, but rather places where you go get the data and use it
directly from that table. The advantage of this approach is that if any of the
customer data should change in the customer table those changes will
automatically be shown in your current form because they are being looked up
from the source table not from a copy of the data that might be out of date now.

There are two common exceptions to this "everything in one place" strategy.

Sometimes you want the lookup data to be used as the default entry in a form,
but need the option to change it. Shipping address data is a good example of
this. A customer table might include shipping data that is pulled into your
form when the CustomerID is entered, but for any given order they might specify
that it be shipped someplace else. In this scenario using lookup references
back to the customer table does not work so you do need fields in the order
table for ship-to data and the data from the customer table does need to be
copied into those fields. Then the user is free to overide that data with
manual entries.

The other exception is time-sensitive data. A good example of this would be a
customer's discount rate. When creating an order one needs to include the
customer's discount rate that is stored in the customer table. But that rate
could be changed and the order I am creating today needs to reflect the discount
rate at the time the order was placed. Again I have to copy the data rather
than merely reference it if I need to store the state of that discount rate "as
of today".

So if in your case you need the record you are creating to use the customer
address data "as a default" with the option to override or you need this record
to record the customer address "as of today" and still want this record to show
the old data should their address change then you SHOULD be copying the data
into this new table. If you do not need either of these then you should delete
those fields from the table your form is bound to and just use the lookup
strategy you are now.

If you DO need to copy the data then change the ControlSource properties of
those address controls so that they contain the names of the fields in the
table. Then you need to use the AfterUpdate event of your CustomerID ComboBox
so that it will "push" the values from those additional columns into the other
TextBoxes. That code would look like...

Me!CustomerAddress = Me!cbocustomername.Column(2)
Me!CustomerCity = Me!cbocustomername.Column(3)


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Rick, I love your writeup and description.

Clear, simple and to the point.

Rick ,
I read your explanation while looking for the answer to my problem.
I am working a fleet maintenance database. I have three tables,
Vehicle,Parts, Work.
I have a mainform Vehicle, and a subform, Work (one to many
relationships, many Work records to each Vehicle record). Forms work
find but I also have the problem not saving data. Since part costs
change frequently I do need to save the data from the parts table into
the work table.
In my Work subForm I use a combo drop down and query to find the
PartsUsed1 from the parts database, I use AfterUpdate to requery the
PartsSell1 field which is a (listBox) with a query to "match" the data
in the PartsUsed1 field with the ItemDesc field in Parts with the
ItemSell in the Parts database:

SELECT Parts.ItemSell, Parts.ID FROM Parts WHERE
(((Parts.ItemDesc)=Forms!Vehicle!Work.form.PartsUs ed1));
This works well and "displays" the correct cost for the parts selected.

My problem is that I can't total the PartsSell1 (thru 5) fields on in
the subform, nor can I save the data in the Work table so I can do
reports and have vehicle parts cost history.

Is there a way I can continue to show this data , sumarize the fields
locally, then save the data with 'todays' cost ?
I'm only a hobbyist, I do not do commerical database building work.
My questions are for my own personal use. Thank you very much for any
help you can provide.

Tom Rector




Rick Brandt wrote:
Natasha wrote:
I have a form that has automated fields for putting the address in
when a customer is selected. I use a combobox for the customer with
columns CustomerID / CustomerName / Address / Phone . Then linking
the controlsource of an unbound text control to it by =
cbocustomername.Column (2). But the problem i hace now is that the
address and customername does not go into the table but the
customerID and all the other information does. Does anyone know why
the customer and address info isnt going into the fields in the
table and what can i do to fix it.

Natasha

You need to think about how bound forms and controls work.

A form is bound to a table by having the name of that table as its RecordSource
property (or the name of a query based on that table).

A control is bound to a field by having the name of that field as its
ControlSource property.

Your address, phone, etc., controls do not have the names of fields in their
ControlSource properties so they are NOT going to save their data to the table.
How would Access know where to put the data? It needs to get that information
from the ControlSource and you have expressions in there instead of field names.

So, controls can either be bound to fields (and save their data there) OR
contain an expression. They cannot do both at once.

Now to solve your problem. In most cases like this you do not want to save the
customer address data into the table that your form is bound to. It is already
stored in the Customer table and you should not make multiple copies of such
data all over your database. All you need is the CustomerID and then you use
Queries joining the two tables together or other lookup mechanisms (like your
ComboBox columns) to *display* the additional customer data when you need it.

Reference or "Lookup" tables should not be thought of as places to go get data
so it can be copied, but rather places where you go get the data and use it
directly from that table. The advantage of this approach is that if any of the
customer data should change in the customer table those changes will
automatically be shown in your current form because they are being looked up
from the source table not from a copy of the data that might be out of date now.

There are two common exceptions to this "everything in one place" strategy.

Sometimes you want the lookup data to be used as the default entry in a form,
but need the option to change it. Shipping address data is a good example of
this. A customer table might include shipping data that is pulled into your
form when the CustomerID is entered, but for any given order they might specify
that it be shipped someplace else. In this scenario using lookup references
back to the customer table does not work so you do need fields in the order
table for ship-to data and the data from the customer table does need to be
copied into those fields. Then the user is free to overide that data with
manual entries.

The other exception is time-sensitive data. A good example of this would be a
customer's discount rate. When creating an order one needs to include the
customer's discount rate that is stored in the customer table. But that rate
could be changed and the order I am creating today needs to reflect the discount
rate at the time the order was placed. Again I have to copy the data rather
than merely reference it if I need to store the state of that discount rate "as
of today".

So if in your case you need the record you are creating to use the customer
address data "as a default" with the option to override or you need this record
to record the customer address "as of today" and still want this record to show
the old data should their address change then you SHOULD be copying the data
into this new table. If you do not need either of these then you should delete
those fields from the table your form is bound to and just use the lookup
strategy you are now.

If you DO need to copy the data then change the ControlSource properties of
those address controls so that they contain the names of the fields in the
table. Then you need to use the AfterUpdate event of your CustomerID ComboBox
so that it will "push" the values from those additional columns into the other
TextBoxes. That code would look like...

Me!CustomerAddress = Me!cbocustomername.Column(2)
Me!CustomerCity = Me!cbocustomername.Column(3)


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

wrote:
Rick ,
I read your explanation while looking for the answer to my problem.
I am working a fleet maintenance database. I have three tables,
Vehicle,Parts, Work.
I have a mainform Vehicle, and a subform, Work (one to many
relationships, many Work records to each Vehicle record). Forms work
find but I also have the problem not saving data. Since part costs
change frequently I do need to save the data from the parts table into
the work table.
In my Work subForm I use a combo drop down and query to find the
PartsUsed1 from the parts database, I use AfterUpdate to requery the
PartsSell1 field which is a (listBox) with a query to "match" the
data in the PartsUsed1 field with the ItemDesc field in Parts with the
ItemSell in the Parts database:

SELECT Parts.ItemSell, Parts.ID FROM Parts WHERE
(((Parts.ItemDesc)=Forms!Vehicle!Work.form.PartsUs ed1));
This works well and "displays" the correct cost for the parts
selected.

First off, your part ComboBox should be saving the ID from the Parts table into
the Work table. It looks like you are currently saving the part description.
It is the Key field that should always be stored in related tables. Non-key
fields like description can be what you display on the form, but should not be
what you store in the table.

Second, the query for a ListBox is a "display only" mechanism and you say you
need to save the part cost value to your table. Lose the ListBox and make this
a TextBox. In the AfterUpdate event of your ComboBox push the cost for that
part into the TextBox. Then it will be saved as part of the work record. You
do this by including PartSell in the ComboBox as a hidden column and then you
can use the Column(n) property of the ComboBox to grab the hidden value and copy
it into the TextBox.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com