Numbering with VBA

[venus as a boy]

Private Sub Form_BeforeInsert(Cancel As Integer)
myNextRecordNo = Nz(DMax("[myRecordNo]", "Employee Main"), 0) + 1
End Sub

I forgot to mention I am new.

You need to wrap the DMax function in an If-Then-Else block so you can use

the value if it returns one and give an error message and stop if it fails.

This advice doesn't help, how do I 'wrap the dmax function in an if-then-else block'?

I need sequentially ordered records. And please remember I'm new. I played with QBasic in middle school, which is allowing me to hang in there.

 

[Wayne Morgan]

First, you should reply to the message you were reading, the person would
have been able to continue to help you. By starting a new message like this,
we are jumping into the middle of a conversation.

To "wrap" the DMax statement, you need to check to see if a possible error
state is detected and cancel the insert.

Example:
myNextRecordNo = Nz(DMax("[myRecordNo]", "Employee Main"), 0) + 1
If myNextRecordNo = 1 Then
strMsg = "If this is the first record entered into this table, click
Yes, otherwise there has been an error!"
If Msgbox(strMsg, vbYesNo + vbQuestion, "Possible Error") = vbNo Then
Cancel = True
End If
End If

This catches if myNextRecordNo is one and asks the user to verify if this
should be correct. It should only be correct for the very first record,
after that the record number should be greater than one. If it is correct,
then the answer from the message box was yes (vbYes). If the answer was no
(vbNo) then the next If statement cancels the insert. The record is still
sitting on the form, the user will have to undo (Esc key) the changes to the
form before they can proceed further (call you to see what went wrong). You
could also undo the changes your self by placing Me.Undo after the Cancel =
True statement.

--
Wayne Morgan
Microsoft Access MVP

Private Sub Form_BeforeInsert(Cancel As Integer)
myNextRecordNo = Nz(DMax("[myRecordNo]", "Employee Main"), 0) + 1
End Sub

I forgot to mention I am new.

You need to wrap the DMax function in an If-Then-Else block so you can use

the value if it returns one and give an error message and stop if it
fails.

This advice doesn't help, how do I 'wrap the dmax function in an
if-then-else block'?

I need sequentially ordered records. And please remember I'm new. I
played with QBasic in middle school, which is allowing me to hang in
there.

 

[Wayne Morgan]

There shouldn't be an error, but if there is it is better to catch it now.

1) You are correct, the numbers were being sorted as text.

2) You will need to change it to a Number field of Long Integer, AutoNumber
automatically fills in the numbers for you, don't do both in the same field.

3) If you put it in the BeforeUpdate event, it will change the ID every time
you make a change to the record. You either need to put it in the
BeforeInsert event or use another If statement to verify that you're at a
new record.

Example:
If Me.NewRecored Then
'code here
End If

--
Wayne Morgan
Microsoft Access MVP

Hello,

First, you should reply to the message you were reading, the person would
have been able to continue to help you. By starting a new message like
this,
we are jumping into the middle of a conversation.

Actually, I did post initially as a response, see "Numbering problems/VBA
Code"
However, I've been told (actually I was told by the guy I was responding
to) that they don't always check back, since you guys are busy or
something.

To "wrap" the DMax statement, you need to check to see if a possible
error
state is detected and cancel the insert.

Example:
myNextRecordNo = Nz(DMax("[myRecordNo]", "Employee Main"), 0) + 1
If myNextRecordNo = 1 Then
strMsg = "If this is the first record entered into this table, click
Yes, otherwise there has been an error!"
If Msgbox(strMsg, vbYesNo + vbQuestion, "Possible Error") = vbNo Then
Cancel = True
End If
End If

Why is there a possibility of error? Can't a code just take the highest
record+1 when a new record is created?

Maybe I have the wrong code?

A few questions...
1. When I initially imported from Excel, the numbering system seemed to
count from the left to right. This means 1, 10,11,12,100,1039, etc, are
all smaller than 2, and 200 and 2000 are smaller than 3, etc. Just trying
to avoid this problem which became fixed when I began using autonumber to
order my records, is it possibly because I had the field set as 'text'?

2. When I use this code, will I have to turn off Autonumber?

3. I believe this code is to be entered into the BeforeUpdate Event, I
already have a code there for dupe prevention