Field Up Date

D

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: [email protected]



email: [email protected]

or [email protected]
 
M

Marshall Barton

dbl said:
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.
Click to expand...

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
 
B

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
 
M

Marshall Barton

Bob said:
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
Click to expand...

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?
 
B

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 Barton said:
Bob said:
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
Click to expand...

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
Click to expand...
Click to expand...
 
M

Marshall Barton

Comments inline.
--
Marsh
MVP [MS Access]


Bob said:
Marshall I have tried all the things that you have said but it still doesn't
work
Click to expand...

"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)
Click to expand...

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
Click to expand...

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?
Click to expand...

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


Marshall Barton said:
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
Click to expand...
Click to expand...
Click to expand...
 
B

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

Marshall Barton said:
Comments inline.
--
Marsh
MVP [MS Access]


Bob said:
Marshall I have tried all the things that you have said but it still doesn't
work
Click to expand...

"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)
Click to expand...

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
Click to expand...

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?
Click to expand...

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
Click to expand...
Marshall Barton said:
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
Click to expand...
by
1
but
only if a certain criteria is met in my policy number field (which
Click to expand...
is
the
policy number must start with CAR). The claim number field must
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
M

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]



Bob said:
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?

Marshall Barton said:
Comments inline.

Bob said:
Marshall I have tried all the things that you have said but it still doesn't
work
Click to expand...

"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)
Click to expand...

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
Click to expand...

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?
Click to expand...

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
Click to expand...
Click to expand...
Click to expand...
 
B

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
Marshall Barton said:
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]



Bob said:
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?

Marshall Barton said:
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
Click to expand...
field
by
1
but
only if a certain criteria is met in my policy number field
Click to expand...
(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
Click to expand...
Click to expand...
Click to expand...
 
M

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]



Bob said:
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.

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


Bob said:
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
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Line

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

Thanks again Bob

Marshall Barton said:
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]



Bob said:
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.

Marshall Barton said:
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
Click to expand...
Click to expand...
Click to expand...
 
Top