Autonumber without a autonumber field

[PennyB]

I have two tables one is

tblProjectsMain - primary key is an autonumber field with no duplicates

table 2

tblPermitMain - is has job number and an indexed key for PermitTaskNumber

I would love to have the permit task number be an autonumber, but the way
the company is handling their permits I can't have it that way.

So for every job number in the main table, there can be many permits.

What I would like to do is write code or something that will auto fill the
next available permit number in for the end user every time they want to
enter a new permit. In other words autonumber for the next permit as follows:

Job Number 1 and Permit task number 1
Job Number 1 and Permit task number 2
Job Number 2 and Permit task number 1
and so on

Hope this makes sense. Any help would be great.

Thanks,

PennyB

 

[Allen Browne]

Use DMax() to get the highest permit task number for the job.

I suggest you do this in the BeforeUpdate event of the form (i.e. the
subform where the jobs are added to the project.) This is the last possible
moment before the record is saved, which reduces the chance that 2 users
will be given the same number at the same time.

Something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

'do any checking you need to first.
If Me.NewRecord And Not Cancel Then
strWhere = "[JobNumber] = " & Me.[JobNumber]
varResult = DMax("[PermitTaskNumber]", "tblPermitMain", strWhere)
Me.[PermitTaskNumber] = Nz(varResult, 0) + 1
End If
End Sub

 

[PennyB]

It didn't work on before update, but it works perfect on current.

Thank you for your help!

 

[Allen Browne]

It would be better to use Form_BeforeInsert rather than Current.

But Form_BeforeUpdate could prevent the multiuser problem (duplicates
assigned.)