Forms assigned number

[Rpettis31]

I have a form that I intend to use to document issues. I would like the form
to autogenerate a number when accessed. For example today is 03-26-08
I would like the form to generate 082603xx, the xx is in case there is more
than one issue on the same day. So for issue one 08260301, etc.

Thanks

 

[mscertified]

Not a good idea.
One of the rules of good table design is to not have meaningful information
'hidden' in data fields.
Instead have a column called 'Issue Date' and a separate column called
'Issue number'
Of course you can combine these two columns on a form or on a report if
needed.

-Dorian

 

[Rpettis31]

You are misunderstanding the question. I have those fields but I want to use
a macro or something that is prefilled to assign the issue number once the
form is opened. The issue number is a combination of the date and a two
digit number.
Whereas the date is a seperate field anyhow.

 

[John Spencer]

If you have the two fields, you can combine them to show the value.

This expression will give you the sequence number for any day.

DCount("*","YourTableName", "YourDateField= #" & Date() & "#") + 1

This expression will display the identifier
Format(YourDateField."yymmdd") & Format(YourSequenceField,"00")

You can use the form's before insert event to fill the controls with the
correct values.
Assuming you have two controls on the form named txtSequenceNumber (with the
field Sequence number as its source) and txtTheDate (with the field TheDate
as its source)

Private Sub Form_BeforeInsert(Cancel As Integer)

Me.txtSequenceNumber = DCount("*","YourTableName", "YourDateField= #" &
Date() & "#") + 1
Me.txtTheDate = Date()

End Sub
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Ken Sheridan]

Also bear in mind that if this is in a multi-user environment you'd need to
handle the error if two users are inserting a new record simultaneously as
they'd both get the same sequence number. The first user to save their
record would be OK but the other(s) would not. There must of course be a
unique index on the date and sequence number columns.

Ken Sheridan
Stafford, England