Create a second AutoNumber containing Date and Number 2

[Guest_imported]

Does anyone know how I can create a 2nd AutoNumber in a Table? I need a record ID as well as a Permit Id made up of the current date and a number, i.e., 20010302-001 that will increment for each new record. I really could use the help ASAP.

 

[gwinn7]

You can't (create a second Autonumber in a table)!

However, you can work around it. My suggestions are...

1. Create a function that will create a new ID every time a user adds a record to your database through a Form. This is a function that you will have to obtain or write in VBA.

2 Create an additional table responsible for holding the second autonumber field and link it with your original table.

3. OTher?

Gary
gwinn7

 

[PaulF]

Something like this will do the trick. It is based on a textbox with the name txtPermit being bound to a Text Field named "Permit ID". The Form BeforeUpdate Event checks the last record in the recordset, converts the Text Value of the Permit ID field to a date, then checks its value against today's date and makes the correct entry based on the result.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim dtVar As Date, intNum As Integer
With Me.RecordsetClone
.MoveLast
dtVar = CDate(Mid(![Permit ID], 5, 2) & "/" & Mid(![Permit ID], 7, 2) & "/" & Left(![Permit ID], 4))
If dtVar = Date Then
intNum = Val(Right(![Permit ID], 3))
intNum = intNum + 1
txtPermitID = Format(Date, "yyyymmdd&quot & "-" & Format(intNum, "000&quot
Else
txtPermitID = Format(Date, "yyyymmdd&quot & "-001"
End If
End With
End Sub


PaulF

 

[LarryDeLaruelle]

D.S.:

Don't know your level of experience with VBA, but here is a solution I created for one of my databases that "autoincrements" a record id. It's based on a year (not month) but you should be able to modify as necessary to reset.

I created a single field/single record table to hold the value of the last used Record Number and reference that table in the procedure. The format of the ID is YYYYNNNN; example: 20010123.

The procedure is triggered when the Referral Date is entered and, as you can see, tests the value of the year part of the date (here is where you will need to modify the code) to reset for a new year.

Next, it reads the value from the table, increments it by one, stores the new value in the table and assigns the number to the Record ID.

Dim dbTemp As Database
Dim rsTemp As Recordset
Dim strSQL As String
Dim refdate As Date
Dim RefNum As String

strSQL = "SELECT BaseNum FROM tblBaseNum"

Set dbTemp = CurrentDb
Set rsTemp = dbTemp.OpenRecordset(strSQL)

If Val(DatePart("YYYY", refdate)) > Val(Left(rsTemp.Fields(0), 4)) Then
If MsgBox("WARNING You are about to reset the Referral Counter" & Chr(13) & Chr(13) _
& "Are you sure you want to do this?", vbOKCancel, "WARNING -- WARNING -- " _
& "WARNING -- WARNING&quot = vbCancel Then
Exit Sub
Else
With rsTemp
.Edit
.Fields(0) = DatePart("YYYY", refdate) & "0000"
.Update
End With
End If
End If

'This takes the value stored in tblBaseNum (the last Referral Number used), increments it
'by one and assigns that value to the current Referral Record.

With rsTemp
.Edit
.Fields(0) = Val(.Fields(0)) + 1
.Update
End With

RefNum = rsTemp.Fields(0)

rsTemp.Close

Hope this helps. If you have any questions, feel free to ask.

Larry De Laruelle
larry1de@yahoo.com

 

[MichaelRed]

faq700-184

Go here.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over