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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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?
Click to expand...
 
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.
 
Back
Top