manually incrementing number code does not work in a2003

G

Guest

Hello,

I am trying to write the code to increment a number with 2 alpha characters
and a dash in front as follows ES-00001. The number should increment by one
each time the user inputs a new record using the form.

I am using the following code, and it works great when tested on a db
created in A2000. However will not increment at all when using in a db
created in A2003 SP1

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!Estimate_Number) Then
Me!Estimate_Number = "ES-" & (Format(CLng(Right(Nz(DMax("Estimate_Number",
"Table1"), 0), 5) + 1), "00000"))
End If

End Sub

Is there additional code needed to make it work for A2003?

Thank you in advance

Sherman Wells
 
G

Guest

Does it return any value?
If it doesn't, try this incase the field is empty and not Null

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!Estimate_Number) Or Trim(Me!Estimate_Number)="" Then
Me!Estimate_Number = "ES-" & (Format(CLng(Right(Nz(DMax("Estimate_Number",
"Table1"), 0), 5) + 1), "00000"))
End If

End Sub
 
G

Guest

Thank you for the quick reply,

yes it returns ES-00001, and returns that for each and every new record
entered, I cannot get it to increment by 1,

In the mean time, I will look at your suggestion

thanks again
 
G

Guest

I tried the added code you suggested, and the value returned remains as
ES-00001 and will not increment by 1
 
B

BruceM

This is an untested variation of something I have used:

If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "Estimate_Number Like """ & "ES-" & "*"""
varResult = DMax("Estimate_Number", "Table1", strWhere)

If IsNull(varResult) Then
Me.Estimate_Number = "ES-00001"
Else
Me.Estimate_Number = Left(varResult, 3) & _
Format(Val(Right(varResult, 5)) + 1, "00000")
End If
End If

The code is for a new record, but could be modified to suit your needs. If
IsNull could be left out if you already have a record with a properly
formatted Estimate_Number.
 
G

Guest

Mybe, and sorry if it will sound like a silly question, but does the dmax is
looking in a table called Table1, but are there any records in this table?
 
G

Guest

First of all, let me thank everyone for the replies, and offer my apologies
if my original post misled anyone---- I based my original post on an
assumption. I have now verified my information, and is as follows:

I built a sample database at home using Access 2003 office professional
edition running WXP, and the code shown at the bottom produces the perfect
result for me (ES-00001) and increments by 1 upon each new record

I returned to work to verify versions using, I am using Access 2003
Professional edition running windows 2000 professional edition

I opened the db I created at home using my work pc running W2PRO and the
code again worked just fine, with no issues.

I then built a sample db at work, and pasted in the same code FROM the
working db from home, and it will not increment by 1, it assigns each record
ES-00001

The ONLY difference I can see is at home I'm running XP and it works, but
when I do the same thing on W2PRO at work, it wont increment !!

OFER: I have tried the following code at work testing with an empty table
and with a table already populated with 1 record,(I believe) ..... which way
do you suggest so I might try again.

Here is the code that works on XP but wont work on W@PRO

If IsNull(Me!Estimate_Number) Then
Me!Estimate_Number = "ES-" & (Format(CLng(Right(Nz(DMax("Estimate_Number",
"Table1"), 0), 5) + 1), "00000"))
End If

I really dont want to build it at home , then try to bring it to work ;-)
This is driving me nuts !!!

In summary to clarify, I built 2 identical dbs, used the identical code in
each (one was built using XP, the other was built using w-2pro,) the one
built in XP works, the one in w-2pro will not increment

Would someone please check my sanity and try the code in Access 2003 (office
pro) running XP and see if they can get it to work

thanks again, and sorry if my original post misled anyone
 
G

Guest

I just noticed one other difference, at work -- SP1 has been installed, maybe
a flaw in the service pack ?
 
G

Guest

I tried the code, and it works, the only thing I can think of is that the
Dmax returns a null.
Open the Immidiate widow, and check what the Dmax returns

?DMax("Estimate_Number", "Table1")
 
G

Guest

Ofer, you tried it using A2003 on W2PRO and it worked hmmmm you lucky dog ;-),
forgive me, but I am not familiar with how to , or what you mean by :

"Open the Immidiate widow, and check what the Dmax returns

?DMax("Estimate_Number", "Table1")"

can you clarify this for me, and I will do it
 
G

Guest

Press Ctrl+G , you get to the code section, when the screen is split to two.
On the buttom screen type
?Dmax...
Press enter and see what value returned

And I'm sorry, but I should have said that, I'm using A2003 on WXP.
But that shouldn't make a different
I never got a problem with Dmax, transfering my MDB from Access2.0 to
Access97, and from there to Access2003, and I got alot of Dmax
 
G

Guest

I put in
?DMax("Estimate_Number", "Table1")

into the Immidiate window, hit enter and got the following message

Run-time error '2001':
You canceled the previous operation.
 
G

Guest

Thanks for all your help Ofer, I got it working, I dont want to mention how,
lets just call it U-S-E-R error !! but I couldnt have done it without your
help !!

happy holidays to you and yours
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top