AutoNumbering

  • Thread starter Thread starter Larry Baxley
  • Start date Start date
L

Larry Baxley

I have a text field PO# which is my key field. Every month the PO# changes
(i.e. OCT 2006 - PO-FY601-001, NOV 2006 PO-FY602-001,002,003 etc). I have
an append query the I run to reset the FY6## every month. Is there any way
to automatically increase the last three digits?

Thanks

Larry
 
Larry,

If I understand correctly, your fiscal year starts Oct.01? So now it is
FY511?

Are you entering PO's manually through a form? If yes, then it's just a
question of a VBA function to calculate the next "number"; use the
function in the form control's Default Value property, and it will fdo
the job. Now, the function:

Function Next_PO_No() As String
Dim vThisMonth As Integer
Dim vFM As String
Dim vFY As String
Dim vLast As String
Dim vPrefix As String
vThisMonth = Month(Date)
Select Case vThisMonth
Case 1 To 9
vFY = Right(Year(Date), 1)
vFM = Format(vThisMonth + 3, "00")
Case 10 To 12
vFY = Right(Year(Date) + 1, 1)
vFM = Format(vThisMonth - 9, "00")
End Select
vPrefix = "PO-FY" & vFY & vFM & "-"
vLast = DMax("PONumber", "tblPurchaseOrders", _
"Left([PONumber],9)='" & vPrefix & "'")
Next_PO_No = vPrefix & Format(Val(Right(vLast, 3)) + 1, "000")
End Function

HTH,
Nikos
 
Larry

Just an observation...

If your field contains something like "OCT 2006 - PO-FY601-001" from your
post, your table structure might benefit from a bit more normalization. It
appears you have multiple facts embedded in a single field.

If so, you would need to either "parse out" information (e.g., Fiscal Year)
to use it, or would have to redundantly store a field of "Fiscal Year" data
to use it (e.g., show all POs from FY 06).

One of the early steps in normalization can be paraphrased as "one field,
one fact". You could store your combined facts in separate fields, and use
a query to concatenate them together into the string you provided as an
example "PO#".

Regards

Jeff Boyce
<Access MVP>
 
Sorry, I'm new at this game. What exactly do you mean by the form
control's Default Value property? I've inserted the code into OnOpen &
BeforeUpdate and can't get it to work.

TIA
 
Larry,

Hope you're still out there, I've been off for two weeks.

The OnOpen event, which you tried, is a form event, not an event of the
particular control (invoice number textbox). Presumably, the
BeforeUpdate one was also the form's BeforeUpdate - but even if it were
the control's BeforeUpdate event, it's still not the right one. While in
form design, select the invoice number textbox, display its properties,
go to tab Events, put the cursor next to Default Value and type (or
paste) the function name, Next_PO_No(). The function itself (code in my
previous post) must be pasted in a general module (Modules, New in the
database window to create one, if you don't have any yet).

HTH,
Nikos
 
Back
Top