Updating a field based on other fields

[Tom]

Hi,

I am using Access 2000, and I would like to be able to have a field updated
based on the entries in other fields. I have a field (Tracking Code), which
I would like to be built based on other information I have already entered.
I can create a Text Box and have it work just fine, but I need for it to
store the restult in the [Tracking Code] field.

Here is the formula I am using:
=Format([Created Date],"yyyymmdd") & "." & [Batch Number] & "." &
IIf([Policy Number] Is Null,0,[Policy Number]) & "." & IIf([Policy Holder]
Is Null,[Company],[Policy Holder])

Here is an example of the result:
20030905.07.410001268000001.QuickNGo

I have used this code in the "On Got Focus" Event field, and it yeilds no
results. I have tried using the Default Value as well, and it will only
update when I create a new record (an of course everything is blank at that
point). I can get it to behave exactly like I want if I create a Text box
and enter the formula in the Default Value.

Your help is appreciated!

Thanks,

Tom

 

[Matthew de Rouville]

Tom--

I have done a similar thing, with one field being the product of two other
fields. I simply put an On Update event on each of the two fields that are
multiplied together that would change the total field when any change was
made to one of the input fields.

Matt

 

[John Vinson]

I am using Access 2000, and I would like to be able to have a field updated
based on the entries in other fields. I have a field (Tracking Code), which
I would like to be built based on other information I have already entered.

This is called an "Intelligent Key" - and that's not a compliment.
You're storing data redundantly; there is almost NEVER a good reason
to do so!

I'd strongly suggest just storing the base fields, and concatenating
the values whenever they're needed, either in a Query or in the
control source of a form or report control.

That said... you'll need to write VBA code in the AfterUpdate event of
each control which contributes to this composite field; the code
should check the values of all the other controls, and if they all
exist should "push" the value into the field. Something like:

If IsNull(Me![Created Date]) OR IsNull(Me![Batch Number]) Then
' do nothing
Else
Me![Tracking Code] = Format([Created Date],"yyyymmdd") & "." & _
[Batch Number] & "." & NZ([Policy Number]) & "." & _
NZ([Policy Holder],[Company])
End If

This will be confusing to the user if they enter the Batch Number
first (I hope the Created Date defaults to Date()!) and then add the
Policy Holder or Policy Number, since the Tracking Code will change
each time they add new data.

 

[Tom]

John,

I think you have a point about the need for that field. It's really only
used for email automation tracking. So I am going to take your advise and
get rid of it, and just have the form generate the value in an unbound text
box for use in the email.

Thanks,

Tom

I am using Access 2000, and I would like to be able to have a field updated
based on the entries in other fields. I have a field (Tracking Code), which
I would like to be built based on other information I have already

entered.

This is called an "Intelligent Key" - and that's not a compliment.
You're storing data redundantly; there is almost NEVER a good reason
to do so!

I'd strongly suggest just storing the base fields, and concatenating
the values whenever they're needed, either in a Query or in the
control source of a form or report control.

That said... you'll need to write VBA code in the AfterUpdate event of
each control which contributes to this composite field; the code
should check the values of all the other controls, and if they all
exist should "push" the value into the field. Something like:

If IsNull(Me![Created Date]) OR IsNull(Me![Batch Number]) Then
' do nothing
Else
Me![Tracking Code] = Format([Created Date],"yyyymmdd") & "." & _
[Batch Number] & "." & NZ([Policy Number]) & "." & _
NZ([Policy Holder],[Company])
End If

This will be confusing to the user if they enter the Batch Number
first (I hope the Created Date defaults to Date()!) and then add the
Policy Holder or Policy Number, since the Tracking Code will change
each time they add new data.