Create a "batch" number specific to record

Question

I have a Cutlist form and I would like to have a numbering system that defaults to the next number when a new cutlist is opened for that project.

I have the cutlist set to open only by clicking the "New Cutlist" button located on my project form. I have a subform on my projects that has a summary of the cutlists for that project; is it possible to code the cutlist form box "Batch" to increase the batch number by "1" based on the last batch number given for that job. Ie: if job 10134' last batch # is 2 the next is 3, yet if you open a new batch under project 10135 it will give it the batch number of 1?

Thank-you, I hope this makes sense.

Jen -Access 2000

Answer 1

I used this code in the AfterUpdate of my projectID box and it worked. but I had to change it a bit what I ended up going with is the following:

Private Sub ProjectID_AfterUpdate()

If Me.NewRecord Then

Me.SheetNum = Nz(DMax("sheetnum", "cutlist", "ProjectID=" & Me.ProjectID), 0) + 1

End If

End Sub

Thank-you for your help

Answer 2

Use the new record form's BeforeUpdate event to assign the batch number field:

If Me.NewRcord Then

   Me.BatcNum = Nz(DMax("BatchNum", "cut list table", "Project = " & Me.Project), 0) + 1

End If

Answer 3

That makes sense but when I used that code it does not return a batch number (SheetNum) after I select the Project Num.

This is how I would have to enter the code

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then

   Me.SheetNum = Nz(DMax("SheetNum", "cutlist", "ProjectNum = " & Me.ProjectNum), 0) + 1

End If

End Sub

Answer 4

That will work if you are the only person that can create new records.  But if two people try to do that at nearly the same time they can get the same sheet number.  The problem situation is greatly increased if one person enters a project number, that code gets the next sheetnumber, but the person goes off to get lunch before saving the new record.  Meanwhile someone else tries to create another new record with the same project number, the code will get the same number as the person who is out to lunch.  This problem has a vanishingly small chance of happening if you use the form's BeforeUpdate event becase once the record save is started the new sheetnum is calulated and the record is saved with only a few microseconds of time between the two actions.

Answer 5

You can use code in the form's BeforeInsert event to find the largest batch number for the job:

Private Sub Form_BeforeInsert(Cancel as Integer)

Me!BatchNo = NZ(DMax("[BatchNo]", "[tablename]", "JobNo = '" & Me![JobNo] & "'")) + 1

End Sub

Of course use your own field and control names. The subform is irrelevant in this context.

---

John W. Vinson/MVP