Numbering problems/VBA Code

[venus as a boy]

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

I'm getting error 2001: You canceled the previous action

I'm having a problem of skipping numbers in my record entry. (I don't want my boss to see my finished work as Record 1, 2, 5, 6, 10, 14, 15....)

I want my records in sequential order.
Thankyou

 

[M.L. Sco Scofield]

I usually use the form's BeforeUpdate event for things like this.

Give that a try.

Also, I wouldn't use the Nz function like this. If the DMax fails, this will
keep returning 1, which I assume was used long ago.

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.

Post back and let us know if this works or if you need some more
suggestions.

Gook luck.

Sco

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

I'm getting error 2001: You canceled the previous action

I'm having a problem of skipping numbers in my record entry. (I don't

want my boss to see my finished work as Record 1, 2, 5, 6, 10, 14, 15....)

 

[Douglas J. Steele]

In BeforeUpdate, you'd need to check whether or not the field already has a
value. Otherwise, using the code as shown, you'd end up changing the record
number every time you updated anything in the record.

And while, as you say, the Nz shouldn't be required, I don't see any reason
why it shouldn't work. That way, you've got "library code" that you can
reuse in new projects.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I usually use the form's BeforeUpdate event for things like this.

Give that a try.

Also, I wouldn't use the Nz function like this. If the DMax fails, this will
keep returning 1, which I assume was used long ago.

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.

Post back and let us know if this works or if you need some more
suggestions.

Gook luck.

Sco

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

I'm getting error 2001: You canceled the previous action

I'm having a problem of skipping numbers in my record entry. (I don't

want my boss to see my finished work as Record 1, 2, 5, 6, 10, 14, 15....)

I want my records in sequential order.
Thankyou

 

[M.L. Sco Scofield]

Inline...

Sco

In BeforeUpdate, you'd need to check whether or not the field already has a
value. Otherwise, using the code as shown, you'd end up changing the record
number every time you updated anything in the record.

Excellent point Doug. It's something I do with out even thinking about. (For
a case like this I check the NewRecord property.) I have code that I always
copy and paste so I don't forget it.

And while, as you say, the Nz shouldn't be required, I don't see any reason
why it shouldn't work. That way, you've got "library code" that you can
reuse in new projects.

I do have to respectfully disagree with you on this Doug. Although
theoretically the DMax should never fail, using the Nz like this will make
this line *always* return one. One could already be in use which would throw
a duplicate error in a key field. I just think a test should be included to
see if the DMax returns a number or a Null and handle it differently than
blindly converting a Null to a zero. Just my $.05 worth.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I usually use the form's BeforeUpdate event for things like this.

Give that a try.

Also, I wouldn't use the Nz function like this. If the DMax fails, this will
keep returning 1, which I assume was used long ago.

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.

Post back and let us know if this works or if you need some more
suggestions.

Gook luck.

Sco

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

I'm getting error 2001: You canceled the previous action

I'm having a problem of skipping numbers in my record entry. (I don't

want my boss to see my finished work as Record 1, 2, 5, 6, 10, 14, 15....)

I want my records in sequential order.
Thankyou

 

[Douglas J. Steele]

I do have to respectfully disagree with you on this Doug. Although
theoretically the DMax should never fail, using the Nz like this will make
this line *always* return one. One could already be in use which would throw
a duplicate error in a key field. I just think a test should be included to
see if the DMax returns a number or a Null and handle it differently than
blindly converting a Null to a zero.

Valid point. At least the duplicate key error would prevent you from ruining
your data, but I can see that there are more graceful ways to handle it.
Since I don't care what my Primary Key values are, I never use that approach
anyhow.

Just my $.05 worth.

Damned exchange rate! <g>

 

[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.

 

[M.L. Sco Scofield]

Well, after some pondering and experimenting, I think I'll go along with
Doug and say that using the BeforeInsert event probably is the better
choice.

This is what I meant by wrapping the DMax. As long as "myRecordNo" is the
name of your field in the "Employee Main" table, you should be able to paste
and use this code with no problems.

<Code>
Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo ErrorRoutine

Dim lngNextRecordNumber As Long

lngNextRecordNumber = Nz(DMax("myRecordNo", "zztblSuppliers"), 0)

If lngNextRecordNumber = 0 Then
MsgBox "There was a problem looking up the last used employee number",
vbCritical, "Employee Number Error"
Cancel = True
Else
Me.myRecordNo = lngNextRecordNumber + 1
End If

ExitRoutine:
Exit Sub

ErrorRoutine:
MsgBox "Run-time Error '" & Err.Number & "'" & vbCrLf & vbCrLf & _
Err.Description, vbExclamation, "Insert Error"

Resume ExitRoutine

End Sub
</Code>

I even threw in some basic error handling for you. If you are going to write
code, you *must* include at least a basic error handler.

Good luck.

--

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

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.

 

[M.L. Sco Scofield]

OK venus,

I think it's time you dropped back to the beginning and bought a VBA book.
You need to do some studying before you try to tackle code for a production
database project.

I'd recommend "Beginning Access 2002 VBA" from Wrox publishing. (See Amazon
or your favorite book source.)

My comment, "...should be able to paste and use this code with no
problems..." meant *exactly* what it said. I had fully tested this code and
it worked perfectly.

The *ONLY* things you might have needed to do were:

1 - Change "myRecordNo" to your actual field name
2 - Change "zztblSuppliers" to your actual table name.

I don't know what you were thinking, but the code you posted below looks
nothing like the code I sent to you.

After looking at my post, the only potential problem was in the line:
MsgBox "There was a problem looking up the last used employee number",
vbCritical, "Employee Number Error"

This is a single line that was wrapped by the email program.

Anyone with a few minutes of reading a VBA book should have been able to
figure this out.

I am very sorry if this sounds a little harsh, but you need to spend some
time with a book and learn the basics, use code that is send to you without
screwing with it, or stop using code.

Start over with the code I sent you and do *NOT* change anything except the
two things I mentioned above above.

I spent a lot of time making sure this code worked and it is *very*
upsetting to see it mangled like this.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

hello

I played with your code a little, eg my primary is "New ID" and

changing/deleting other little stuff I saw didn't match (but was probably
important) the format of my other code.

 

[Venus as a Boy]

Sco,

I believe I initially tried your code with just 'myrecordno' and
'zztblsuppliers' changed, no love there, so I started playing with it, and
now we have what you saw... I'm all good now, but thanks for your effort. I
can imagine it took some work on your part and I admire that you assign a lot
of value to that work.

Your advice to buy a Visual Basic for beginners book is among the best I
have received here.

My advice...

I had posted earlier (otherthread) that 'me.undo' was setting off
Autonumber. If you hear that again, "me![fieldname].undo" might help

Thanks
Jason