VBA - Program question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm writing a 2003 version dbase in which I need specific PO numbers for
individual purchases. I would like a format such as mmddyy000, i.e.
101005195, where every day the system would reset back to the current date
plus 000, would autonumber, and would have the potential for 999 Purchase
Orders for the day. How could I program through VBA to automatically resolve
and input this field?

Thanks in advance for any help!
 
Hi Stephen,
you can mare a text field in a table to store this number, and then set it
value in a form, where you enter new purchase. you can use form's
beforeupdate event

Dim strDatePart As String, strNewNumber As String
strDatePart = Format(Date, "mmddyy")

strNewNumber = DMax("PONumber", "Table1", "left([PONumber],6)='" &
strDatePart & "'") & ""

If Len(strNewNumber) = 0 Then
strNewNumber = "000"
Else
strNewNumber = Format(Val(Right(strNewNumber, 3)) + 1, "000")
End If

strNewNumber = strDatePart & strNewNumber

Me.PONumber = strNewNumber
 
Thanks Alex. That worked well. You make this sound really easy!

I appreciate the quick response and help.

Stephen

Alex Dybenko said:
Hi Stephen,
you can mare a text field in a table to store this number, and then set it
value in a form, where you enter new purchase. you can use form's
beforeupdate event

Dim strDatePart As String, strNewNumber As String
strDatePart = Format(Date, "mmddyy")

strNewNumber = DMax("PONumber", "Table1", "left([PONumber],6)='" &
strDatePart & "'") & ""

If Len(strNewNumber) = 0 Then
strNewNumber = "000"
Else
strNewNumber = Format(Val(Right(strNewNumber, 3)) + 1, "000")
End If

strNewNumber = strDatePart & strNewNumber

Me.PONumber = strNewNumber


--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com



Stephen said:
I'm writing a 2003 version dbase in which I need specific PO numbers for
individual purchases. I would like a format such as mmddyy000, i.e.
101005195, where every day the system would reset back to the current date
plus 000, would autonumber, and would have the potential for 999 Purchase
Orders for the day. How could I program through VBA to automatically
resolve
and input this field?

Thanks in advance for any help!
Click to expand...
Click to expand...
 
Back
Top