Access Counter when adding data /records

[Skroof]

Hi all, please help. I have a counter which is working when making bookings however i want it to count per "ClientID" , it must pop a message and still continue taking other bookings, for other people. e.g. If I make a booking for John Doe, the booking will run till the third booking and only pop a message saying "maximum bookings made". Same must happen with other users. In other words it counts per user id not by overall bookings.If Peter made 1 and John made 2 bookings - the message must not pop up although 1 + 2 = 3. The message must only pop up for Peter once he reaches 3 and for John on his 3rd booking as well. I hope you will help me here. Again thanks for your help.See attached screenshots
Here is my current code - where do i stick the client id,appointment table, etc
Private Sub Form_BeforeUpdate(Cancel As Integer)
If RecordsetClone.RecordCount = 2 Then
MsgBox "Maximum bookings made."
End If
End Sub
Private Sub Form_Current()
If RecordsetClone.RecordCount = 2 Then
Me.AllowAdditions = True
Me.AllowEdits = False
End If
End Sub

 

[Pat Hartman]

The RecordsetClone isn't filtered to just one client is it? It doesn't sound like it so you need to use something like a DCount()

If DCount("*", "yourtable", "ClientID = " & Me.ClientID) < 3 Then
    Me.AllowAdditions = True
Else
    Me.AllowAdditions = False
End If

I should add that this code may not work the way you want because it doesn't prevent people from adding 2 or more records at one time. My preferred solution is to leave the AllowAdditions property at True all the time but to stop the insert in the BeforeInsert event.

If DCount("*", "yourtable", "ClientID = " & Me.ClientID) >2 Then
    Cancel = True
    Me.Undo
    Msgbox "Only three ... are allowed.  You have reached your limit.",vbOKOnly
    Exit Sub
End If