Create a field where a new number appears when the form is opened

[Guest]

I am trying to create a field that will have a number and I want to set that
field to have the number change each time the form is opened. Each form must
have a unique number. I know how to do this with a date field but figure out
how to do this when the field just contains a number. Any ideas?

 

[ruralguy via AccessMonster.com]

Have you looked at the DMax() function?

I am trying to create a field that will have a number and I want to set that
field to have the number change each time the form is opened. Each form must
have a unique number. I know how to do this with a date field but figure out
how to do this when the field just contains a number. Any ideas?

 

[Guest]

It sounds like this number is NOT bound to a field in a table so you are not
talking about an autonumber key. It isn't clear if you want to increment
whenever the form is opened or you want a count of the open instances of the
same form.

Something like this in the form's onOpen event might work for the first
requirement:
lngSeqNum = Nz(DMax("SeqNum","tblSeqNum"),0) +1
CurrentDb.Execute "UPDATE tblSeqNum SET tblSeqNum.SeqNum = " & lngSeqNum & "
WHERE tblSeqNum.SeqNum = " & lngSeqNum-1, DAO.dbFailOnError

tblSeqNum has one row with one column named "SeqNum" with an initial value
of zero.

If you have multiple users each sharing the same database via linking then
you will have to deal with collisions and this simple solution will get
complicated.

Multiple instances of the same form in complex and counting them is another
level of complexity, but this can be done.