Hi, I am having problems with an auto numbering I have set up on a form in the On Current event.
I would like that each time I click New Record the next sequential number is inserted automatically starting at 6930.

The code I am using is below but I am not sure where I am going wrong.
I have also attached a sample to show what it is doing.

Private Sub Form_Current()
If Me.NewRecord Then
On Error Resume Next 'It should never occur, just to be sure...
Me!lblProjectNo.DefaultValue = Nz(DMax("[lblProjectNo]", "tblquote"), 6930) + 1
End If
End Sub


Hope someone can help.

Tks

There is a problem with your attachment. I can't download it.

Roccoau,

I would recommend you put this code on the BeforeInsert event of the form.

In which case you would not need to check for NewRecord - the Before insert event only ever happens with a new record.

Also, the DefaultValue property of the control is not really applicable here. It's the actual value of the control you want to affect.

So...

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!lblProjectNo = Nz(DMax("[lblProjectNo]", "tblquote"), 6930) + 1
End Sub

THen the autonumber datatype is not the way to go. Autonumbers are not intended to have any specific meaning other to uniquely identify a record, and cannot be garaunteed to be sequential.
I suggest you use the DMAX function. An example of this can be found at www.utterangel.com (look for the demo db "autonumber without autonumber".

HTH
Matt

OK thanks for the help guy's I will try them out and see how I go.

Not sure why attachement did not work.
Here it is again so you can see what I am trying to do.

I would also suggest that you assign a Primary key Autonumber to your tblType and tblDivision

John,

Not sure to what degree Roccoau's sample database reflects the final reality.

But it looks to me that his tblType and tblDivision are just simple lookup tables, wit the purpose to facilitate the entry of data into the core tables.

Would you really put an Autonumber field in such tables? Why?

Steve,

Just a personal preference you might say as down the road the OP may way to relate the Divisions and or types in reporting,linking etc. and as I see it that would facilitate that need much better in assigning than getting x number of records and then coming to the realization that an autonumber was needed rather than merely a listing of the Divisions and Types.

It was merely a suggestion not a hard fast requirement

John,

Ok, thanks. I did see that you used the word "suggest"!

Nevertheless, I still can't really imagine where an AutoNumber field would ever be useful in a table like that. He appears to be relating the data in the tblTypes table, for example, to the ProposalType field in the tblQuotes table, etc, which seems fine.

So, just wondering, but not a biggie.