Field Up Date

[dbl]

Hi, When I add a new record I need to up date my claim number field by 1 but
only if a certain criteria is met in my policy number field (which is the
policy number must start with CAR). The claim number field must not up date
if this criteria is not met. How do I go about this?

Thanks
--
dbl
email: (e-mail address removed)



email: (e-mail address removed)

or (e-mail address removed)

 

[Marshall Barton]

Hi, When I add a new record I need to up date my claim number field by 1 but
only if a certain criteria is met in my policy number field (which is the
policy number must start with CAR). The claim number field must not up date
if this criteria is not met.

Use the form's BeforeUpdate event:

If Me.NewRecord Then
If Left(Nz(Me.policynumber,""),3) = "CAR" Then
Me.claimnumber = DMax("claimnumber", "table") + 1
End If
End If

 

[Bob Line]

Marshall I have entered the following code

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Left(Nz(Me.Policy_Number, ""), 3) = "CAR" Then
Me.BJRefNo = DMax("BJRefNo", "CARNumbers") + 1
End If
End If
End Sub

But nothing happens can you see what I am doing wrong?

Policy_Number is my policy number field
BJRefNo is the field I want to update
CARNumbers is the table name

Thanks Bob

 

[Marshall Barton]

Marshall I have entered the following code

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Left(Nz(Me.Policy_Number, ""), 3) = "CAR" Then
Me.BJRefNo = DMax("BJRefNo", "CARNumbers") + 1
End If
End If
End Sub

But nothing happens can you see what I am doing wrong?

Policy_Number is my policy number field
BJRefNo is the field I want to update
CARNumbers is the table name

Make sure the form's Before Update property contains
[Event Procedure]

Make sure the CARNumbers table has at least one preexisting
record with CAR policy and BJRefNo properly filled in. This
issue can be avoided with a slight modification:
Me.BJRefNo = Nz(DMax("BJRefNo", "CARNumbers"), 0) + 1

Double check that the BJRefNo field is included in the
form's record source table/query.

Did you try placing breakpoints in the code so you can see
what is happening?

 

[Bob Line]

Marshall I have tried all the things that you have said but it still doesn't
work, I have copied the code again for you to see.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Left(Nz(Me.Policy_Number, ""), 3) = "CAR" Then
Me.BJRefNo = Nz(DMax("BJRefNo", "CARNumbers"), 0) + 1
End If
End If
End Sub

The Table name is CARNumbers and the field name in the table is CARNumbers
which is set to Numbers (Long Integer)

Could it be that the policy numbers begin with car but have six numbers
after the CAR i.e. CAR123456
or that the policy number is not selected until you find the policy holder
in a drop down list and therefore it would be after update?

Thanks for your help and patience

Bob

Marshall I have entered the following code

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Left(Nz(Me.Policy_Number, ""), 3) = "CAR" Then
Me.BJRefNo = DMax("BJRefNo", "CARNumbers") + 1
End If
End If
End Sub

But nothing happens can you see what I am doing wrong?

Policy_Number is my policy number field
BJRefNo is the field I want to update
CARNumbers is the table name

Make sure the form's Before Update property contains
[Event Procedure]

Make sure the CARNumbers table has at least one preexisting
record with CAR policy and BJRefNo properly filled in. This
issue can be avoided with a slight modification:
Me.BJRefNo = Nz(DMax("BJRefNo", "CARNumbers"), 0) + 1

Double check that the BJRefNo field is included in the
form's record source table/query.

Did you try placing breakpoints in the code so you can see
what is happening?
--
Marsh
MVP [MS Access]

1
but up
date

 

[Marshall Barton]

Comments inline.
--
Marsh
MVP [MS Access]

Marshall I have tried all the things that you have said but it still doesn't
work

"Doesn't work" sure doesn't provide much in the way of
clues.

What did you learn from placing a break point and stepping
through the code?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Left(Nz(Me.Policy_Number, ""), 3) = "CAR" Then
Me.BJRefNo = Nz(DMax("BJRefNo", "CARNumbers"), 0) + 1
End If
End If
End Sub

The Table name is CARNumbers and the field name in the table is CARNumbers
which is set to Numbers (Long Integer)

If the **field** in the table is named CARNumbers, then
shouldn't it be DMax("CARNumbers", "CARNumbers")
???

Could it be that the policy numbers begin with car but have six numbers
after the CAR i.e. CAR123456

The Left function takes care of this issue.

or that the policy number is not selected until you find the policy holder
in a drop down list and therefore it would be after update?

We're using the Form's BeforeUpdate event so all controls
have already been set.

Make sure the form's Before Update property contains
[Event Procedure]

Make sure the CARNumbers table has at least one preexisting
record with CAR policy and BJRefNo properly filled in. This
issue can be avoided with a slight modification:
Me.BJRefNo = Nz(DMax("BJRefNo", "CARNumbers"), 0) + 1

Double check that the BJRefNo field is included in the
form's record source table/query.

Did you try placing breakpoints in the code so you can see
what is happening?

Hi, When I add a new record I need to up date my claim number field by 1
but
only if a certain criteria is met in my policy number field (which is the
policy number must start with CAR). The claim number field must not up
date
if this criteria is not met.

:
Use the form's BeforeUpdate event:

If Me.NewRecord Then
If Left(Nz(Me.policynumber,""),3) = "CAR" Then
Me.claimnumber = DMax("claimnumber", "table") + 1
End If
End If

 

[Bob Line]

Marsh it is now putting the number in but it doesn't up date it i.e. it
keeps putting in 3000 how do I get the table to up date the number and save
it?

It was the field name in the table that was wrong.

Thanks again for your help.

Bob

Comments inline.
--
Marsh
MVP [MS Access]

Marshall I have tried all the things that you have said but it still doesn't
work

"Doesn't work" sure doesn't provide much in the way of
clues.

What did you learn from placing a break point and stepping
through the code?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Left(Nz(Me.Policy_Number, ""), 3) = "CAR" Then
Me.BJRefNo = Nz(DMax("BJRefNo", "CARNumbers"), 0) + 1
End If
End If
End Sub

The Table name is CARNumbers and the field name in the table is CARNumbers
which is set to Numbers (Long Integer)

If the **field** in the table is named CARNumbers, then
shouldn't it be DMax("CARNumbers", "CARNumbers")
???

Could it be that the policy numbers begin with car but have six numbers
after the CAR i.e. CAR123456

The Left function takes care of this issue.

or that the policy number is not selected until you find the policy holder
in a drop down list and therefore it would be after update?

We're using the Form's BeforeUpdate event so all controls
have already been set.

Bob Line wrote:
Marshall I have entered the following code

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Left(Nz(Me.Policy_Number, ""), 3) = "CAR" Then
Me.BJRefNo = DMax("BJRefNo", "CARNumbers") + 1
End If
End If
End Sub

But nothing happens can you see what I am doing wrong?

Policy_Number is my policy number field
BJRefNo is the field I want to update
CARNumbers is the table name

Make sure the form's Before Update property contains
[Event Procedure]

Make sure the CARNumbers table has at least one preexisting
record with CAR policy and BJRefNo properly filled in. This
issue can be avoided with a slight modification:
Me.BJRefNo = Nz(DMax("BJRefNo", "CARNumbers"), 0) + 1

Double check that the BJRefNo field is included in the
form's record source table/query.

Did you try placing breakpoints in the code so you can see
what is happening?


dbl wrote:
Hi, When I add a new record I need to up date my claim number field

by
1

but
only if a certain criteria is met in my policy number field (which

is
the

policy number must start with CAR). The claim number field must

not
up

date
if this criteria is not met.

:
Use the form's BeforeUpdate event:

If Me.NewRecord Then
If Left(Nz(Me.policynumber,""),3) = "CAR" Then
Me.claimnumber = DMax("claimnumber", "table") + 1
End If
End If

 

[Marshall Barton]

Is the BJRefNo text box bound to the BJRefNo field (or
whatever the field's name is now) in the table.
--
Marsh
MVP [MS Access]

Marsh it is now putting the number in but it doesn't up date it i.e. it
keeps putting in 3000 how do I get the table to up date the number and save
it?

Comments inline.

Marshall I have tried all the things that you have said but it still doesn't
work

"Doesn't work" sure doesn't provide much in the way of
clues.

What did you learn from placing a break point and stepping
through the code?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Left(Nz(Me.Policy_Number, ""), 3) = "CAR" Then
Me.BJRefNo = Nz(DMax("BJRefNo", "CARNumbers"), 0) + 1
End If
End If
End Sub

The Table name is CARNumbers and the field name in the table is CARNumbers
which is set to Numbers (Long Integer)

If the **field** in the table is named CARNumbers, then
shouldn't it be DMax("CARNumbers", "CARNumbers")
???

Could it be that the policy numbers begin with car but have six numbers
after the CAR i.e. CAR123456

The Left function takes care of this issue.

or that the policy number is not selected until you find the policy holder
in a drop down list and therefore it would be after update?

We're using the Form's BeforeUpdate event so all controls
have already been set.

Bob Line wrote:
Marshall I have entered the following code

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Left(Nz(Me.Policy_Number, ""), 3) = "CAR" Then
Me.BJRefNo = DMax("BJRefNo", "CARNumbers") + 1
End If
End If
End Sub

But nothing happens can you see what I am doing wrong?

Policy_Number is my policy number field
BJRefNo is the field I want to update
CARNumbers is the table name

"Marshall Barton"> wrote:
Make sure the form's Before Update property contains
[Event Procedure]

Make sure the CARNumbers table has at least one preexisting
record with CAR policy and BJRefNo properly filled in. This
issue can be avoided with a slight modification:
Me.BJRefNo = Nz(DMax("BJRefNo", "CARNumbers"), 0) + 1

Double check that the BJRefNo field is included in the
form's record source table/query.

Did you try placing breakpoints in the code so you can see
what is happening?


dbl wrote:
Hi, When I add a new record I need to up date my claim number field by
1
but
only if a certain criteria is met in my policy number field (which is
the
policy number must start with CAR). The claim number field must not
up
date
if this criteria is not met.

:
Use the form's BeforeUpdate event:

If Me.NewRecord Then
If Left(Nz(Me.policynumber,""),3) = "CAR" Then
Me.claimnumber = DMax("claimnumber", "table") + 1
End If
End If

 

[Bob Line]

Marsh, Yes the BJRefNo text box is bound to the BJRefNo field, but this is
not in the CARNumbers table it is in a table called IncidentData. Hope this
helps.

Bob

Is the BJRefNo text box bound to the BJRefNo field (or
whatever the field's name is now) in the table.
--
Marsh
MVP [MS Access]

Marsh it is now putting the number in but it doesn't up date it i.e. it
keeps putting in 3000 how do I get the table to up date the number and save
it?

Comments inline.

Bob Line wrote:
Marshall I have tried all the things that you have said but it still doesn't
work

"Doesn't work" sure doesn't provide much in the way of
clues.

What did you learn from placing a break point and stepping
through the code?


Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Left(Nz(Me.Policy_Number, ""), 3) = "CAR" Then
Me.BJRefNo = Nz(DMax("BJRefNo", "CARNumbers"), 0) + 1
End If
End If
End Sub

The Table name is CARNumbers and the field name in the table is CARNumbers
which is set to Numbers (Long Integer)

If the **field** in the table is named CARNumbers, then
shouldn't it be DMax("CARNumbers", "CARNumbers")
???


Could it be that the policy numbers begin with car but have six numbers
after the CAR i.e. CAR123456

The Left function takes care of this issue.


or that the policy number is not selected until you find the policy holder
in a drop down list and therefore it would be after update?

We're using the Form's BeforeUpdate event so all controls
have already been set.



Bob Line wrote:
Marshall I have entered the following code

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Left(Nz(Me.Policy_Number, ""), 3) = "CAR" Then
Me.BJRefNo = DMax("BJRefNo", "CARNumbers") + 1
End If
End If
End Sub

But nothing happens can you see what I am doing wrong?

Policy_Number is my policy number field
BJRefNo is the field I want to update
CARNumbers is the table name

"Marshall Barton"> wrote:
Make sure the form's Before Update property contains
[Event Procedure]

Make sure the CARNumbers table has at least one preexisting
record with CAR policy and BJRefNo properly filled in. This
issue can be avoided with a slight modification:
Me.BJRefNo = Nz(DMax("BJRefNo", "CARNumbers"), 0) + 1

Double check that the BJRefNo field is included in the
form's record source table/query.

Did you try placing breakpoints in the code so you can see
what is happening?


dbl wrote:
Hi, When I add a new record I need to up date my claim number

field
by

1
but
only if a certain criteria is met in my policy number field

(which
is

the
policy number must start with CAR). The claim number field must not
up
date
if this criteria is not met.

:
Use the form's BeforeUpdate event:

If Me.NewRecord Then
If Left(Nz(Me.policynumber,""),3) = "CAR" Then
Me.claimnumber = DMax("claimnumber", "table") + 1
End If
End If

 

[Marshall Barton]

Bob, the DMax function has to look in the same table that
records are being added to. How else can it find out what
the highest number that's been used so far? Is your use of
the CARNumbers table causing all this confusion?

You've pretty much lost me on which tables have what data,
how they are related and how the result you want to achieve
is supposed to be derived from all that data.
--
Marsh
MVP [MS Access]

Marsh, Yes the BJRefNo text box is bound to the BJRefNo field, but this is
not in the CARNumbers table it is in a table called IncidentData. Hope this
helps.

Is the BJRefNo text box bound to the BJRefNo field (or
whatever the field's name is now) in the table.

Marsh it is now putting the number in but it doesn't up date it i.e. it
keeps putting in 3000 how do I get the table to up date the number and save
it?

Comments inline.

Bob Line wrote:
Marshall I have tried all the things that you have said but it still
doesn't
work

"Doesn't work" sure doesn't provide much in the way of
clues.

What did you learn from placing a break point and stepping
through the code?


Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Left(Nz(Me.Policy_Number, ""), 3) = "CAR" Then
Me.BJRefNo = Nz(DMax("BJRefNo", "CARNumbers"), 0) + 1
End If
End If
End Sub

The Table name is CARNumbers and the field name in the table is
CARNumbers
which is set to Numbers (Long Integer)

If the **field** in the table is named CARNumbers, then
shouldn't it be DMax("CARNumbers", "CARNumbers")
???


Could it be that the policy numbers begin with car but have six numbers
after the CAR i.e. CAR123456

The Left function takes care of this issue.


or that the policy number is not selected until you find the policy
holder
in a drop down list and therefore it would be after update?

We're using the Form's BeforeUpdate event so all controls
have already been set.



Bob Line wrote:
Marshall I have entered the following code

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Left(Nz(Me.Policy_Number, ""), 3) = "CAR" Then
Me.BJRefNo = DMax("BJRefNo", "CARNumbers") + 1
End If
End If
End Sub

But nothing happens can you see what I am doing wrong?

Policy_Number is my policy number field
BJRefNo is the field I want to update
CARNumbers is the table name

"Marshall Barton"> wrote:
Make sure the form's Before Update property contains
[Event Procedure]

Make sure the CARNumbers table has at least one preexisting
record with CAR policy and BJRefNo properly filled in. This
issue can be avoided with a slight modification:
Me.BJRefNo = Nz(DMax("BJRefNo", "CARNumbers"), 0) + 1

Double check that the BJRefNo field is included in the
form's record source table/query.

Did you try placing breakpoints in the code so you can see
what is happening?


dbl wrote:
Hi, When I add a new record I need to up date my claim number field
by
1
but
only if a certain criteria is met in my policy number field (which
is
the
policy number must start with CAR). The claim number field must
not
up
date
if this criteria is not met.

:
Use the form's BeforeUpdate event:

If Me.NewRecord Then
If Left(Nz(Me.policynumber,""),3) = "CAR" Then
Me.claimnumber = DMax("claimnumber", "table") + 1
End If
End If

 

[Bob Line]

Marshall, thanks for all your help and your patience it works !!!!!

Thanks again Bob

Bob, the DMax function has to look in the same table that
records are being added to. How else can it find out what
the highest number that's been used so far? Is your use of
the CARNumbers table causing all this confusion?

You've pretty much lost me on which tables have what data,
how they are related and how the result you want to achieve
is supposed to be derived from all that data.
--
Marsh
MVP [MS Access]

Marsh, Yes the BJRefNo text box is bound to the BJRefNo field, but this is
not in the CARNumbers table it is in a table called IncidentData. Hope this
helps.

Is the BJRefNo text box bound to the BJRefNo field (or
whatever the field's name is now) in the table.


Bob Line wrote:
Marsh it is now putting the number in but it doesn't up date it i.e. it
keeps putting in 3000 how do I get the table to up date the number and save
it?

Comments inline.

Bob Line wrote:
Marshall I have tried all the things that you have said but it still
doesn't
work

"Doesn't work" sure doesn't provide much in the way of
clues.

What did you learn from placing a break point and stepping
through the code?


Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Left(Nz(Me.Policy_Number, ""), 3) = "CAR" Then
Me.BJRefNo = Nz(DMax("BJRefNo", "CARNumbers"), 0) + 1
End If
End If
End Sub

The Table name is CARNumbers and the field name in the table is
CARNumbers
which is set to Numbers (Long Integer)

If the **field** in the table is named CARNumbers, then
shouldn't it be DMax("CARNumbers", "CARNumbers")
???


Could it be that the policy numbers begin with car but have six numbers
after the CAR i.e. CAR123456

The Left function takes care of this issue.


or that the policy number is not selected until you find the policy
holder
in a drop down list and therefore it would be after update?

We're using the Form's BeforeUpdate event so all controls
have already been set.



Bob Line wrote:
Marshall I have entered the following code

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Left(Nz(Me.Policy_Number, ""), 3) = "CAR" Then
Me.BJRefNo = DMax("BJRefNo", "CARNumbers") + 1
End If
End If
End Sub

But nothing happens can you see what I am doing wrong?

Policy_Number is my policy number field
BJRefNo is the field I want to update
CARNumbers is the table name

"Marshall Barton"> wrote:
Make sure the form's Before Update property contains
[Event Procedure]

Make sure the CARNumbers table has at least one preexisting
record with CAR policy and BJRefNo properly filled in. This
issue can be avoided with a slight modification:
Me.BJRefNo = Nz(DMax("BJRefNo", "CARNumbers"), 0) + 1

Double check that the BJRefNo field is included in the
form's record source table/query.

Did you try placing breakpoints in the code so you can see
what is happening?


dbl wrote:
Hi, When I add a new record I need to up date my claim number field
by
1
but
only if a certain criteria is met in my policy number field (which
is
the
policy number must start with CAR). The claim number field must
not
up
date
if this criteria is not met.

:
Use the form's BeforeUpdate event:

If Me.NewRecord Then
If Left(Nz(Me.policynumber,""),3) = "CAR" Then
Me.claimnumber = DMax("claimnumber", "table") + 1
End If
End If