Add 1 to last record with criteria

[primagic]

Hi

I have a table tblEngagements which are linked to tblSchools via SchoolID.

Each school can have several engagements. However I want these engagements to allocated a number, - 1, 2, 3, 4, 5, 6....

So the first engagement created for the school is 1 and the second one created is 2 and so on

I am using the code to add 1 to the previous record but its not working properly obviously because its not specifying the schoolID. How would I modify it to check for the last engagement where the schoolID = x then get that number and add 1.

Private Sub
....
[EngagementNumber] = DLast("EngagementNumber"]", "tblEngagements") + 1
End Sub

Thanks

 

[44nato44]

Not sure if you can do this

[EngagementNumber] = Cint(DLast("EngagementNumber"]", "tblEngagements")) + 1

is EngagementNumber an numeric field ?

 

[primagic]

No its a text field. should I make it a number field? But how would I tell it to use the last engagement number for the current school id

Because the table looks like

EngagementID SchoolID EngagementNumber
(Autonumber)
----------------------------------------------
1 1 1
2 2 1
3 1 2
4 1 3
5 2 2

So there are three records with a schoolid of 1 therefore three engagements, numbered 1, 2, 3. what I need is code that says when i add new engagement to school 1, it auto makes the enagement number 4. so the table would look like:

EngagementID SchoolID EngagementNumber
(Autonumber)
----------------------------------------------
1 1 1
2 2 1
3 1 2
4 1 3
5 2 2
6 1 4

 

[dhookom]

Dlast() is mostly a worthless function. I expect you should be using DMax() like:

  [EngagementNumber] = DMax("EngagementNumber", "tblEngagements","SchoolID = " & SchoolID) + 1

This assumes SchoolID and EngagementNumber are numeric.


Duane
Hook'D on Access
MS Access MVP

 

[PHV]

And for safety purpose:

Me!EngagementNumber = Nz(DMax("EngagementNumber", "tblEngagements", "SchoolID=" & Me!SchoolID), 0) + 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Craig0201]

Why would you do this at all?

The Engagement presumably has a date. Sort the Engagements by school and by date. The 'Number' is then calculable.