Form field not storing in table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form in Access 97 which contains a field which pulls together data
from other fields. ICB Tracking Number=[ICB Number] & [Region] &
Left([Vertical],1) & [Auto Number]. ICB Number is a dlookup field (julian
date - I couldn't figure out the sql to determine julian date base on the
request date), Region is a dlookup field, Vertical is a combo box w/value
list. The number makes up what will be a unique number for each record.
Once the number is determined, I will append to other tables. The problem is
the ICB tracking Number is stored in the forms datasheet view but does not
store in the table. How do I get this unique number in the table?

Any help is appreciated. Keep in mind I am sql handicapped.
jennifer
 
Maybe the field that stores your ICB Tracking number is unbound then the
data will not be stored in your table.
 
JenniferMc said:
I have a form in Access 97 which contains a field which pulls
together data from other fields. ICB Tracking Number=[ICB Number] &
[Region] & Left([Vertical],1) & [Auto Number]. ICB Number is a
dlookup field (julian date - I couldn't figure out the sql to
determine julian date base on the request date), Region is a dlookup
field, Vertical is a combo box w/value list. The number makes up
what will be a unique number for each record. Once the number is
determined, I will append to other tables. The problem is the ICB
tracking Number is stored in the forms datasheet view but does not
store in the table. How do I get this unique number in the table?

Any help is appreciated. Keep in mind I am sql handicapped.
jennifer
Click to expand...

As I understand it, [ICB Tracking Number] is a calculated control; that
is, one whose controlsource is an expression beginning with "=". The
value displayed by such a control isn't stored in the table for several
reasons. Normally, it's not a good idea to save calculatable values in
tables. That's a major consideration, though it may not apply in your
specific case. Aside from that, since the controlsource is not the name
of a field in the form's recordsource, Access has no idea what field you
might want to store this calculated value in.

Since you presumably have external business constraints that require you
to store this calculated value, I won't try to talk you out of it.
Here's one way to get Access to store it.

1. Change the name of the text box from "ICB Tracking Number" to
"txtCalcICBTrackingNumber". I just picked that name arbitrarily, but
the important point is that it must be different from the name of the
table field you want to store the value in.

2. Create an event procedure for the form's BeforeUpdate event, similar
to this (warning, it's air code):

'----- start of code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.[ICB Tracking Number] = Me.txtCalcICBTrackingNumber

End Sub
'----- end of code -----

Provided that the field [ICB Tracking Number] is included in the form's
recordsource, that ought to do it. You *may* want to add an invisible
text box to the form and bind it to that field. I would, but I don't
think it's required.
 
Back
Top