Incrementing Work Order Numbers 3

[rockiwood]

I don't know if I am missing something simple or if I am trying to make this too simple.
I am creating a work order number from the last 2 digits of the year, which seems to be working.
The 2nd part of the work order number should increment by "1" after counting the records. The Work Order Number actally works at this point, as in 04-1, however, it always gives me a 04-1 and won't increase. Any advice will be greatly appreciated.

Private Sub Form_Current()

' Set Variables for first part of the New Work Order Number
Dim yr As Date
Dim WONumberYear As Variant

' Set up date defining the first part of the Work Order Number
yr = Date
WONumberYear = Right(yr, 2) + "-"

'Set up variables for the second part of the Work Order Number
Dim WONumber As Integer
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAvionicsMainWO")
rs.MoveLast
' Set up Integer for second part of Work Order Number

If rs.BOF Then
WONumber = 1
ElseIf rs.EOF Then
WONumber = rs.RecordCount
Else
WONumber = WONumber + 1
End If


' Creates New Work Order Number and puts it in the Work Order Box
Dim WO2Number As String
WO2Number = WONumber
Me.txtWO = WONumberYear + WO2Number


' Closes active Recordset
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

Thanks in advance for the help.

Jerry

 

[KenReay]

Hi

f rs.BOF Then
WONumber = 1
ElseIf rs.EOF Then
WONumber = rs.RecordCount
Else
WONumber = WONumber + 1
End If

since you are Using Record count, if a record other thna the last (ie highest numbered) is deleted you would get a duplicate key

If you just want a sequentila number why not use an autonumber?

Have a WOYear Column, with default of Format(Date(),yy)

Make your primekey a compound of the two columns

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

To answer your question

Set rs = db.OpenRecordset(SELECT * FROM tblAvionicsMainWO ORDER BY WONumber")
rs.MoveLast
' Set up Integer for second part of Work Order Number

If rs.BOF Then
WONumber = 1
Else
WONumber = Rs!WONumber + 1
End If


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Albion]

Couple of things to think about first. If you are going to increment the Work Order from the actual table itself, you might want to consider what happens if two people try to enter a new work order at the same time.

Here's what I do. First I calculate The new number and reserve it.

Dim varWorkOrder As Variant
Dim strIncrement,strNewWorkOrder As Integer
Dim aryTemp As Variant

'  Grab the last WO entered
varWorkOrder = DMax("[WO_Number]", "tblWorkOrders")

'   Split the work order number into 2 parts, increment the second part by one, then join them back together.
aryTemp = Split(varWorkOrder, "-")
strIncrement = Cstr(Cint(aryTemp(1))+1)
strNewWorkOrder = Right(year(now()),2) & "-" & strIncrement

'   Reserve the new Work Order in the database immediatly to resolve any multiple user duplication issues.
DoCmd.RunSQL "INSERT INTO tblWorkOrders (WO_Number) VALUES ('" & strNewWorkOrder & "')"

'   The rest of your code for entering a new Work Order can go here.

On a second note, personally I try to avoid using ADO and DAO calls to queries and tables in Access as much as possible. I find that nearly everything can be done with an SQL statement. This also makes this forward compatiable when Microsoft decides to make more changes and break your code or you decide to start using MSSQL or MySQL.

-Al

 

[rockiwood]

Thanks for all the help. I will take all of the advice and try it out. I'm sure I can get this done now.

Thanks for your help!

Jerry

 

[MichaelRed]

for some differnt thoughts on the sampe topic review faq700-184. From my experience, what has been offered is necessary, but not sufficient.



MichaelRed