Input mask to include year and an auto number?

[kdowney]

I have inherited a table that includes a text field called "UTC #"

Currently, for data for this field is manually entered in this format: Four digit year + an ascending three-digit consecutive number.

Example: 2002-159; 2002-160; 2002-161

First, I don't know if it is possible to automate this with an input mask, but if it is, is there coding I could use in the input mask so that when the year changes the "2002" will change to "2003", and the three-digit code will re-set to "000"??

Thanks for all help!

 

[FancyPrairie]

As far as the date goes, set you DefaultValue Poperty of the field to: Year(Date) & "-", which would give you 2002-
as the default value (or 2003- when the year changes).

Changing the number part to 000 would not be a problem, but you don't want 000 showing everytime

 

[WayneRyan]

kdowney,

FancyPrairie is right for the year component.

Since this field will not be accessible to the user
you can do the following on the form's BeforeInsert
event:

' *****************************************************
Dim db as DataBase
Dim rst as RecordSet
Dim strSQL as String
Dim strNextNum As String

Set db = CurrentDb
strSQL = "Select * from tblTable " & _
"Where [UTC #] like '" & Me.[UCT #] & "' " & _
"Order by [UTC #] desc;"
set rst = db.OpenRecordSet(strSQL)

If rst.EOF and rst.BOF Then
' First record of new year
Me.[UCT #] = Me.[UCT #] & "000"
Else
' Biggest value +1
strNxtNum = Str(Val(Right(rst![UTC #], 3)) + 1)
Me.[UCT #] = Me.[UCT #] & strNxtNum
End If
Set rst = Nothing
Set db = Nothing
' *****************************************************

I haven't tested this, but it should be close to the
general idea. I'm specifically not sure about the
references to [UTC #] as I try to avoid spaces and/or
special symbols in field names.


hth,
Wayne

 

[Lightning]

Hi Guys

Excuse me for butting in, but I notice one minor problem with Wayne's code. It assumes that a blank recordset exists if it is a new year. (NOT a criticism, just an observation.)

If rst.EOF and rst.BOF Then
' First record of new year
Me.[UCT #] = Me.[UCT #] & "000"

Since this is not necessarily going to be the case, you might need to do a slightly more vigorous test for the Year.

As a suggestion, try replacing Wayne's If...Then statement with this one

Dim strYear As String
Dim strLastRecord As String

strYear = Year(Date)
strLastRecord = Str(Val(Left(rst![UTC #], 4)))
If strYear = strLastRecord Then
'Not a new year
' Biggest value +1
strNxtNum = Str(Val(Right(rst![UTC #], 3)) + 1)
Me.[UCT #] = Me.[UCT #] & strNxtNum
Else
'is a new year
Me.[UCT #] = Year(Date) & "-000"
End If

This compares the latest record UCT # with the current system date and, if they're the same, increments the number by one. If it is not the same it starts a new sequence of numbers.

Like wayne, I haven't tested this but it should work as advertised.

HTH
Lightning

 

[WayneRyan]

Hi, me again ...

I just typed the code into NotePad and it was just
meant to be a general concept. However, I forgot the
"*" needed for the like operator:

"Where [UTC #] like '" & Me.[UCT #] & "*' "

If the [UTC #] is for a new year then the recordset
will be empty and the next number is 000.

Lightning's code will work fine, but the SQL for the
record set shouldn't have the Where clause.

OK, I'm ready to move on to something else.

Wayne