Incrementing a number not using autonumber

[mondeoman]

For some time I have been trying to get a solution to creating a record number (displayed in a field) which does not use autonumber. To date with little luck. I have come accross this code which seems to meet my needsd but I get a Type Mismatch box:

On my form I have an unbound text box called txtPosition.

In the OnCurrent event procedure I have the following code:
---------------------------------------------------------------------------------------------------------------------------------
Private Sub Form_Current()
On Error GoTo Err_Form_Current
Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.Bookmark = Me.Bookmark
Me.txtPosition = rst.AbsolutePosition + 1

Exit_Form_Current:
Set rst = Nothing
Exit Sub

Err_Form_Current:
If Err = 3021 Then 'No current record
Me.txtPosition = rst.RecordCount + 1
Else
MsgBox Error$, 16, "Error in Form_Current()"
End If
Resume Exit_Form_Current
End Sub

------------------------------------------------------------------------------------------------------------------------

What I want is that a new number for each record that increments by 1. Why do I get a type mismatch and can any say how I can either get this to work or another solution.

Many thanks

 

[hwkranger]

Why don't you just do MAX(Field) + 1?

from the seat of my pants..

Private Function MyAutoNumber() as long
 dim rst as new adodb.recordset
 rst.open "Select MAX(YOURFIELD) From YOURTABLE", currentproejct.connection
 MyAutoNumber = nz(rst(0) + 1, 1)
End function

You would use this as such:

Me.txtPosition = MyAutoNumber

Personally, I'd stick with the Identity (Auto Increment)

Randall Vollen
Merrill Lynch

 

[mintjulep]

Seems like you should be able to do this with bookmarks and formula fields.

 

[mondeoman]

I have gotten myself into a right muddle.

Presumably your function code should be saved as a module. Where do I place the "Me.txtPosition = MyAutoNumber"? I am assuming that the main code is put in the Before Insert of the form.

The code you are offering - namely:
------------------------------------------------------

Private Function MyAutoNumber() as long
dim rst as new adodb.recordset
rst.open "Select MAX(Job_Purchase_Number) From tbl_Support_Request", currentproejct.connection
MyAutoNumber = nz(rst(0) + 1, 1)
End function

--------------------------------------------------------

assumes a currentproject.connection (please look at spelling in code provided). My db is however not a project but a simple .mdb file so I am not sure why I need the currentproject.connection input.

 

[mondeoman]

I have now managed to get a sort of increment by using:

Private Sub Form_BeforeInsert(Cancel As Integer)
txtPosition = Nz(DMax("Job_Purchase_Number", "tbl_Support_Request"), 0) + 10001
End Sub

--------------------------------------------------------------------------

However,when the first record is added it is 10001 which is just what I want but when the second record is added it jumps to 20002 then 30003 etc. How can I limit this to 10001, 10002, 10003 etc. Any ideas how I can prevent this please.

 

[PHV]

txtPosition = Nz(DMax("Job_Purchase_Number", "tbl_Support_Request"), 10000) + 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mondeoman]

Many thanks PHV - so easy when you know what you are doing!