Field Up Date

  • Thread starter Thread starter dbl
  • Start date Start 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: (e-mail address removed)



email: (e-mail address removed)

or (e-mail address removed)
 
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
 
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
 
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?
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
Marsh the BJRefNo field sometimes doesn't update, I have tried it on a test
version of the db but it works every time. Yet when its in use it properly
only up dates 70% of the time. If some else adds a new record before the
first new record is saved then it will definitely not up date.

Any Idea's

Regards Bob
Bob Line 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.

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?

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...
 
dbl said:
Marsh the BJRefNo field sometimes doesn't update, I have tried it on a test
version of the db but it works every time. Yet when its in use it properly
only up dates 70% of the time. If some else adds a new record before the
first new record is saved then it will definitely not up date.
Click to expand...


What is the code you're using now? What event is it running
in?
 
Marshall its running in the before update event and the code is as follows

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

Thanks Bob
 
Well, at least the code looks like it was supposed to.

I admit that the DMax approach is not 100% reliable,
especially in a multi user situation, but a 30% failure rate
is way beyond anything I've ever head of. Either you have
some horribly intense data entry activities or there is some
other issue getting involved.

To create a 99.999% reliable incrementing field, you have to
use a different approach. Create a one row table with one
field that hold the next available number. Then when you
need to assign a number to a record, open a recordset on
that table using dbDenyRead to prevent other users from
doing the same thing at the same time. Retrieve the value
of the one field, increment it, save it back to the table
and close the recordset. You have to use error handling to
trap the rare(?) case where another user is doing the same
thing. The error handler code should then wait a short(?)
but random amount of time before trying again. It's also a
good idea to keep count of how many times you retry so you
can bail out if something goes completely out of whack.

This is a fairly advanced issue that can easily go beyound
the scope of a newsgroup, so you'll have to do your homework
to understand all the coding issues. The Access 97
Developer's Handbook (by Litwin, Getz and Gilbert - from
Sybex) has a Custom AutoNumber section in the chapter on
Developing Multiuser Applications. I'm sure the later
editions of this very popular book will have it too.
Regardless of the price, they are worth every bit and more.
 
Back
Top