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
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"PennyB" <(E-Mail Removed)> wrote in message
news:1B5FC132-F890-4740-96C0-(E-Mail Removed)...
>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
Similar Threads
