Progressive Incident ID number in Access 2007

5 pts.
Tags:
Access 2007 forms
Access 2007 functions
Microsoft Access 2007
Microsoft Access function
I have a db that records each security infraction at a library. The desired ID number for it is year/month/day/number of event that particular day (example, 2010/12/01-1 for the first event, 2010/12/01-2 for the second, etc.) My question: I have this -- 0000/00/00-0;_;;_ -- as the format for people to enter, but is there a way for Access to generate the final number (the 1, 2, 3, 4...) on its own based on what is already in the db? I guess what I'm asking is this: can Access a) assign the final number automatically after someone enters the year/month/day manually? or b) assign a number in this format every time someone opens the DB to enter a new incident? Thanks very much in advance. Mark

Software/Hardware used:
access 2007

Answer Wiki

Thanks. We'll let you know when a new response is added.

Both can be done, but you would probably prefer option ‘b’.

In summary you could do it modifying your entry form to add a BeforeInsert event and put some code to get the last code for the current date to generate and assign the new code. But you might want to store the last incident number in a table so you don’t have to look for the ‘max’ number every time you insert a new record.

I ran a small test, and this is what I did. I’m not an Access expert so there is probably a better or more efficient way to do it, but I think this should at least get you started.

Assumming that your incidents are being stored in a table called yourTable1 and you are going to store the last error number for the current day in a table called yourTable2, this code should work:

BeforeInsert event:

(A reference to the Microsoft DAO Object library is needed to run this code)

<pre>Private Sub Form_BeforeInsert(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim no As Integer
Set rs = CurrentDb.OpenRecordset(“SELECT [date],errno FROM yourTable2″, dbOpenDynaset, dbSeeChanges)
If rs.BOF And rs.EOF Then
rs.Close
MsgBox “yourTable2 table not found”
Exit Sub
End If
If rs![date] <> Date Then
no = 1
Else
no = rs![errno] + 1
End If
= Format(Date, “yyyy/mm/dd”) & “-” & no
rs.Close
End Sub</pre>

And after inserting the record you need to update the number in yourTable2, so this is the code for the AfterInsert event:

<pre>Private Sub Form_AfterInsert()
CurrentDb.Execute “UPDATE yourTable2 set [date] = #” & Format(Date, “dd-mmm-yyyy”) & “#, errno = ” & Val(Mid(code, InStr(code, “-“) + 1))
End Sub</pre>

Feel free to ask for clarification if needed.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following