Assign Running Number

  • Thread starter Thread starter Paul M. via AccessMonster.com
  • Start date Start date
P

Paul M. via AccessMonster.com

I have a data entry form that I am entering data from feedback forms. I'd
like to be able to assign an id# to each data entry form that will be a
running number for each entry.

Example: For a entry that was entered in the database in October 2005 and it
was the 112th entry it would assign: 05-10-112; but then in November it
would start over and assign 05-11-01 for the first feedback form entered on
November 1st.

Does anyone know how I can set this up?

Thanks!!
 
Hi Paul,
In the 'On Current' event of your form place the following code,

Change [IDNumber] to whatever name you have used for your incrementing ID
number,
Change [Name Of your Table] to whatever name you have used for the table
where [IDNumber] is located.
This will increment up to 999 each month, is that enough digits per month??
Also will need a bit of tweaking to get any " - " displayed in the right
places, did you need them?

************************************************
Private Sub Form_Current()
If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "[IDNumber] Like """ & Format(Date, "yyyymm") & "*"""
varResult = DMax("[IDNumber]", "[Name Of your table]", strWhere)

If IsNull(varResult) Then
Me.[IDNumber] = Format(Date, "yyyymm") & "001"
Else
Me.[IDNumber] = Left(varResult, 6) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If
End If
End Sub
******************************************************
 
I have a data entry form that I am entering data from feedback forms. I'd
like to be able to assign an id# to each data entry form that will be a
running number for each entry.

Example: For a entry that was entered in the database in October 2005 and it
was the 112th entry it would assign: 05-10-112; but then in November it
would start over and assign 05-11-01 for the first feedback form entered on
November 1st.

Does anyone know how I can set this up?

Thanks!!
Click to expand...

Store this in a DIFFERENT field. A composite field containing a date
and a running number is hard to maintain and bad design. You can
combine the date with the number for display purposes if you wish.

To get the number, use the Form's Beforeinsert event, with code like

Me!txtSeq = NZ(DMax("[SeqNo]", "[YourTable]", "[Datefield] >= #" &
DateSerial(Year(Date()), Month(Date()), 1)) + 1

using your own table and fieldnames of course.

John W. Vinson[MVP]
 
dim strFeedBackId as String
dim varNextNum as Variant

strFeedBackId = format(year(date),"yy") & "-" & format(month(date),"mm") & "-"
varNextNum = Nz(DLookup("[FEEDBACK_ID]", "SomeTable", _
"Left([FEEDBACK_ID], 6) = '" & strFeedBackId & "'"),0) +1
strFeedBackId = strFeedBackId & Format(varNextNum,"000")

All that other stuff about changing table names is scary.
 
Back
Top