copy data from form to table

  • Thread starter Thread starter D.J.
  • Start date Start date
D

D.J.

First and most important I am very new to Access (first project)
I am using access 2k I have created a frm based on a multiple tbl qry
all are bound. On the frm I have 6 fields 5 of which are making the
6 th using the control source to concatenate the others together into
a 14 char text sting I want this to update the primary key for the
table. The 14 char string creates a unique text string that will
relate to only one map. I,m not trying to set the primary key from the
form just get the info from the form to update the field in the table.
I have a table with a field that matches each field in the form. The
other fields in the table update as I input data into the form. The
problem is the one I create when I put the others together. The only
thing different is that I use the control source in this text box (on
the form) to get the information from the other five. If I get rid of
the control source code I've put in it works. What I've done is put
the line =[a] & ..etc ( for the control source for [f] ) to get [f]
to read those fields concatenated together on the form. I see the
result on the form but it is not updating the related field on the
table.When I finish entering the data in the form and press the save
button I'm getting a message that says primary key can not be nul.
The table is not updating with what I am seeing on the screen. Is
this update something I need to do with an after_update exp or the
like. If so how please. You may ask why not just enter the 14 char
string and be done with it. The answer is those people entering the
data (big finger effect)
 
D.J. said:
First and most important I am very new to Access (first project)
I am using access 2k I have created a frm based on a multiple tbl qry
all are bound. On the frm I have 6 fields 5 of which are making the
6 th using the control source to concatenate the others together into
a 14 char text sting I want this to update the primary key for the
table. The 14 char string creates a unique text string that will
relate to only one map. I,m not trying to set the primary key from the
form just get the info from the form to update the field in the table.
I have a table with a field that matches each field in the form. The
other fields in the table update as I input data into the form. The
problem is the one I create when I put the others together. The only
thing different is that I use the control source in this text box (on
the form) to get the information from the other five. If I get rid of
the control source code I've put in it works. What I've done is put
the line =[a] & ..etc ( for the control source for [f] ) to get [f]
to read those fields concatenated together on the form. I see the
result on the form but it is not updating the related field on the
table.When I finish entering the data in the form and press the save
button I'm getting a message that says primary key can not be nul.
The table is not updating with what I am seeing on the screen. Is
this update something I need to do with an after_update exp or the
like. If so how please. You may ask why not just enter the 14 char
string and be done with it. The answer is those people entering the
data (big finger effect)
Click to expand...


First let me warn you that it's not a good idea to create a calculated
primary key in the way you describe. It's a bad idea in general to save
calculated data, and it's also a bad idea to store multiple pieces of
information in one field. A better solution, from a database-design
point of view, would be to give the table in question a compound primary
key, composed of the 5 *individual fields* that uniquely identify the
record. You may not be aware of the fact that it's possible to select
several fields at once and designate them collectively as the table's
primary key.

That said, we as developers aren't always permitted by management to do
things the right way. If you really have no choice but to create and
save this calculated field, then you'll have to remove the expression
from the text box's controlsource, and instead set the controlsource to
the field where you want the value to be saved, thereby binding the
control to the field. Then you'll need code in the AfterUpdate event of
each of the contributing fields that concatenates all the relevant
fields together the way you want and assigns the result to the key
control. You'll also need to ensure that access to these records is
allowed *only* by way of this form, so that no one can change the value
of any of the contributing fields without changing the composite key.
You see why this is such a pain?
 
Dirk thank you I'll creat the after_updates. Thanks for the advise.


D.J. said:
First and most important I am very new to Access (first project)
I am using access 2k I have created a frm based on a multiple tbl qry
all are bound. On the frm I have 6 fields 5 of which are making the
6 th using the control source to concatenate the others together into
a 14 char text sting I want this to update the primary key for the
table. The 14 char string creates a unique text string that will
relate to only one map. I,m not trying to set the primary key from the
form just get the info from the form to update the field in the table.
I have a table with a field that matches each field in the form. The
other fields in the table update as I input data into the form. The
problem is the one I create when I put the others together. The only
thing different is that I use the control source in this text box (on
the form) to get the information from the other five. If I get rid of
the control source code I've put in it works. What I've done is put
the line =[a] & ..etc ( for the control source for [f] ) to get [f]
to read those fields concatenated together on the form. I see the
result on the form but it is not updating the related field on the
table.When I finish entering the data in the form and press the save
button I'm getting a message that says primary key can not be nul.
The table is not updating with what I am seeing on the screen. Is
this update something I need to do with an after_update exp or the
like. If so how please. You may ask why not just enter the 14 char
string and be done with it. The answer is those people entering the
data (big finger effect)
Click to expand...


First let me warn you that it's not a good idea to create a calculated
primary key in the way you describe. It's a bad idea in general to save
calculated data, and it's also a bad idea to store multiple pieces of
information in one field. A better solution, from a database-design
point of view, would be to give the table in question a compound primary
key, composed of the 5 *individual fields* that uniquely identify the
record. You may not be aware of the fact that it's possible to select
several fields at once and designate them collectively as the table's
primary key.

That said, we as developers aren't always permitted by management to do
things the right way. If you really have no choice but to create and
save this calculated field, then you'll have to remove the expression
from the text box's controlsource, and instead set the controlsource to
the field where you want the value to be saved, thereby binding the
control to the field. Then you'll need code in the AfterUpdate event of
each of the contributing fields that concatenates all the relevant
fields together the way you want and assigns the result to the key
control. You'll also need to ensure that access to these records is
allowed *only* by way of this form, so that no one can change the value
of any of the contributing fields without changing the composite key.
You see why this is such a pain?
Click to expand...
 
Back
Top