Creating my own autonumber

[graeme34 via AccessMonster.com]

Hi Could anybody help me?
I am quite new to Access programming and I was wondering if anybody could
tell me where I am going wrong?
I am currently trying to develop a database in Access and originally thought
of using the access autonumber but have now decided I would have more contorl
over my own auotnumber.

Here is the code I am trying to use on the on open property of the sales
order form.

Private Sub Form_Open(Cancel As Integer)

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblSalesOrder")

' If first record set value to 1
' Else Add 1 to last order number
If rs.RecordCount = 0 Then
Me.txtSalesOrderNumber = 1
Else
rs.MoveLast
Me.txtSalesOrderNumber = rs!SalesOrderNumber + 1
End If

End Sub

The code is running to the rs.Movelast line then stopping creating the no
current record error. The seems to be 19 records from when I was
experimenting with Accesses autonumber. Although I have deleted these, they
still seem to be in the background somewhere, unseen in the datasheet view of
the table and having no values they are there, as the record count is showing
19 records...any suggestions??
Thanks in advance Graeme.

 

[Guest]

There is a better way:
Me.txtSalesOrderNumber = Nz(DMax("[SalesOrderNumber]", "tblSalesOrder"),0)+1

 

[graeme34 via AccessMonster.com]

Hi Klatuu

You are right, thank you it did work better.

Thanks.

There is a better way:
Me.txtSalesOrderNumber = Nz(DMax("[SalesOrderNumber]", "tblSalesOrder"),0)+1

Hi Could anybody help me?
I am quite new to Access programming and I was wondering if anybody could

[quoted text clipped - 29 lines]

19 records...any suggestions??
Thanks in advance Graeme.

 

[Brian Bastl]

In addition to what Klatuu suggests, if you ever plan on using this in a
multi-user environment, then you may want to move this code to your form's
BeforeUpdate event.

Brian

Hi Klatuu

You are right, thank you it did work better.

Thanks.

There is a better way:
Me.txtSalesOrderNumber = Nz(DMax("[SalesOrderNumber]", "tblSalesOrder"),0)+1

Hi Could anybody help me?
I am quite new to Access programming and I was wondering if anybody

could
[quoted text clipped - 29 lines]

19 records...any suggestions??
Thanks in advance Graeme.

 

[BruceM]

Another way of handling this (by trapping the error) may be found here:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

In addition to what Klatuu suggests, if you ever plan on using this in a
multi-user environment, then you may want to move this code to your form's
BeforeUpdate event.

Brian

Hi Klatuu

You are right, thank you it did work better.

Thanks.

There is a better way:
Me.txtSalesOrderNumber = Nz(DMax("[SalesOrderNumber]", "tblSalesOrder"),0)+1

Hi Could anybody help me?
I am quite new to Access programming and I was wondering if anybody could
[quoted text clipped - 29 lines]
19 records...any suggestions??
Thanks in advance Graeme.