Chrischik said:
Hi,
I think what I want to do is possible, I'm just not sure how. (It's
probably super easy).
I'm building a sort of Issues table to be updated from InfoPath I
have a db and in one table I have the following columns
ID
FindingID
OpenDate
OpenedBy
Status
Category
Description
CloseDate
I want to generate the FindingID by taking the OpenDate + 001
(incrementing) so for example 06062008-001 would be the first one
entered followed by 06062008-002.
Can anyone help?
Generally it is better to *store* this in two fields and combine in the
desired format for *display* on forms and reports. That is better from a
pure database design aspect (one piece of data per field) as well as making
the automatic entry of the two fields a LOT easier.
For the date field you just set the default value of the field in the Table
to either =Date() or =Now().
With Date() you get all midnight times which by default are not displayed.
With Now() you get the current time as well as the current date. I
recommend the latter since you are formatting for display anyway and knowing
the time of record creation is often useful. Even if you don't need to know
that now you might wish you had it later on.
For the second field you use a long integer and all you need is a way to
increment it with each new record. That would be done with code in the form
used to make insertions. The best event for this is the BeforeUpdate event
especially if you might have multiple users making entries simultaneously.
The reason is that the BeforeUpdate event has the shortest window of time
between the calculation of the next number and saving the record (which
allows for the accurate calculation of the number after that). The code
would look like...
Dim FilterString as String
FilterString = "RecordDate >= Date() And RecordDate < DateAdd('d', 1,
Date())"
If Me.NewRecord Then
Me.IDNum = Nz(DMax("IDNum","TableName", FilterString),0) + 1
End If
To display your formatted number on the form use an expression in a
TextBox...
=Format(RecordDate,"mmddyyyy\-") & Format(IDNum,"000")
I made the assumption that you want the numeric part to start over each day
since you only allow three digits for it.
