AutoNumber problem 2

[Hargreavesk]

Hi,

I have a form which uses an AutoNumber. I would like to have the AutoNumber automatically show when I open the form rather than having to add data first. Is there any way to do this?

Many thanks

Kate )

 

[Craig0201]

Kate,

Simple answer. Don't show it on the form. It's designed as an internal reference, not for the user to see.

Craig

 

[Hargreavesk]

Carl,

Unfortunately, I need to show the autonumber on my form as it will show a cheque number. This will then be printed on to a cheque.

Kate

 

[Windshpere]

two ways to get this done.
one. write in VBA on load to add a new record then add a value to a txtbox or another object. **remember this will only work on open of the form. you may need to reference this again when anytime you go to a new record.
example(frmcool, one autonumber field, and one text box named "1&quot

VBA
Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec 'starts with a new record
Me![1] = 1 'put "1" in the text box named [1]
End Sub

----------

Second
write in VBA to calc the new autonumber.

example(form = frmcool,tbl = tblcool )

Function AutoNumberCool() As Long ' ref this function on load.

Dim rst As Object, sql As String, lngMaxNum As Long
Dim lngNewNum As Long

sql = "SELECT Max(tblcool.at) AS MaxNumber" & _
" FROM tblcool;"
' this sql get the max number out of the at field in a table _
[at] was the auto number that would have been used.

Set rst = CurrentDb.OpenRecordset(sql) ' this is different between versions of access
If rst.EOF Then 'check to see if the table has any records
lngNewNum = 1 ' no records start with #1
Else
lngMaxNum = rst!MaxNumber 'with records get max number and
lngNewNum = lngMaxNum + 1 ' start add 1
End If
Me![at] = lngNewNum ' puts the record in the form

End Function

use this code to generate an autonumber in the form every time it's opened, ** remember to ref this when ever a new record is requested to be added to the form.

let me know if this helps or you need more info.

WS

 

[Hargreavesk]

WS,

You are a star )... It works brilliantly.

Many thanks

Kate

 

[JimHorton]

Kate,
Your best bet is to forget autonumber altogether. If you have time and really want to avoid this and the numerous other headaches that you *will* get with autonumber, then do the following:

Create a table, with one record, and two fields. One field called RowID. Put a 1 in this , make it primarykey, and in validation rule, put 1. This ensures one and only one record here. Next put a field called LastNum. This will be the the source of your 'autonumber'. You can optionally put many other fields for different tables' autonumber, ie LastNumCust, LastNumOrder, etc. Call the table what you want, ie. tblNum.

Then write function:
Function GetNextNum() as long
dim rst as Recordset,lTrys as long,i as integer
set rst = currentdb.Openrecordset("tblNum",dbopendynaset,dbDenyWrite,dbPessimistic)
rst.edit
rst!LastNum = rst!LastNum + 1
GetNextNum = rst!LastNum
rst.Update
exGet:
exit Function

errGet:
'This error will occur at the Set rst= line if another user is fetching # at this time.
if err.Number = 3260 or err.number = 3261 then
If lTrys > 2 Then 'Give it 3 trys and then out
GetNextNum = 0
Else
lTrys = lTrys + 1
For i = 1 To 1000000
'Below is really more to take time than anything, don't use Doevents to take time!
If i Mod 100000 = 0 Then
Debug.Print "Waiting for unlock of Last Number table"
End If
Next i
Resume
End If
Else 'any other error exits with fail
GetNextNumber = 0
end if
End Function

If you add more fields to the tblNum, then use an argument in the function call to condition the field to read/update for the next number. This guarantees a unique number, but you have total flexibility over where to start the sequence and you can even add increment options, such as incrementing by 5, 10, whatever, depending on your need. Call this on BeforeInsert of your forms, and set the field you need this # for to the return value.
--Jim

 

[JimHorton]

Excuse my typos...I typed from memory and didn't put in the On Error GoTo errGet line, and in that handler, I mistyped the assignment, it should be GetNextNum =
Instead of GetNextNumber =...
--Jim

 

[Hargreavesk]

Jim,

Thankyou for your comments. I will give it a try and let you know.

Regards

Kate