Count Records in Subform to determine SubForm mode

[Guest]

Hi

I have five tables:
Vehicles (PK VRM)
Drivers (PK DriverID)
VehiclesDrivers (PK VRM and DriverID)
Warnings(PK WarningID)
DriversWarnings (PK DriveID and WarningID)

There are 19 entries in the Warnings table.
I have a main form, Vehicles, with a subform, VehiclesDrivers. From this
subform, the user can open a form, Drivers, to add more indepth infomation
about each driver.
On this Drivers form is a continuous subform, DriversWarnings.

The user enters warnings in the subform for the driver using a combo (whse
rowsource is the Warnings table.) I need to prevent the possibility of the
user entering duplicate warnings for the driver and have achieved this using
Me.Undo with a custom error message if the user does this. However, I still
need to put the form into AllowAdditions=No if the driver has all 19 warnings
assigned (ie if there are 19 records in the subform) so that the user will
not see the blank "new record" if all 19 warnings have been assigned.

QUESTION: How do you count the number of records in a continunous subform,
and if it is greater than 18, set the subform to Allow Additions = No?
Presumably I would need to check the number of entries when the form is
loaded, when new entries are made in the subform and also when records are
deleted.
So to cover all eventualities, I would need to put the expression in the
form's OnLoad event, in the AfterUpdate Event of the Combo and in the
AfterDeleteConfirm Event of the Form (right?)

I have tried creating a DCount expression in VB but am having difficulty
with the syntax. Is there a straightforward way of just counting the number
of records in the subform or do you have to specify all field names? I have
tried a "generic"
If DCount(*) >18 (this works to display total number of records on the form
itself) but it doesn't work as a VB expression.

I have also tried
DCount("[WarningID]","[DriversWarnings]","DriverID=VehiclesDrivers.DriverID"
but this doesn't work either. Have I got the syntax wrong? Am I putting the
correct fields in the expression? I can't work out where the DriverID should
come from in the last part of the expression - from VehiclesDrivers (on the
calling form which opens the Drivers form?) The Drivers form itself is opened
from a button on VehiclesDrivers subform, with the following OnClick event:

stLinkCriteria = "[DriverID]=" & Me![VehiclesDrivers.DriverID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Please help...

TIA
rich

 

[Guest]

Try this:
On the On Current Event and On the On Delete Event of the sub form write the
code

If Me.RecordsetClone > 18 Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If

 

[Guest]

I missed the RecordCount

If Me.RecordsetClone.RecordCount > 18 Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If

 

[Guest]

Thanks Ofer. Works nicely

I missed the RecordCount

If Me.RecordsetClone.RecordCount > 18 Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If
--
I hope that helped
Good luck

Hi

I have five tables:
Vehicles (PK VRM)
Drivers (PK DriverID)
VehiclesDrivers (PK VRM and DriverID)
Warnings(PK WarningID)
DriversWarnings (PK DriveID and WarningID)

There are 19 entries in the Warnings table.
I have a main form, Vehicles, with a subform, VehiclesDrivers. From this
subform, the user can open a form, Drivers, to add more indepth infomation
about each driver.
On this Drivers form is a continuous subform, DriversWarnings.

The user enters warnings in the subform for the driver using a combo (whse
rowsource is the Warnings table.) I need to prevent the possibility of the
user entering duplicate warnings for the driver and have achieved this using
Me.Undo with a custom error message if the user does this. However, I still
need to put the form into AllowAdditions=No if the driver has all 19 warnings
assigned (ie if there are 19 records in the subform) so that the user will
not see the blank "new record" if all 19 warnings have been assigned.

QUESTION: How do you count the number of records in a continunous subform,
and if it is greater than 18, set the subform to Allow Additions = No?
Presumably I would need to check the number of entries when the form is
loaded, when new entries are made in the subform and also when records are
deleted.
So to cover all eventualities, I would need to put the expression in the
form's OnLoad event, in the AfterUpdate Event of the Combo and in the
AfterDeleteConfirm Event of the Form (right?)

I have tried creating a DCount expression in VB but am having difficulty
with the syntax. Is there a straightforward way of just counting the number
of records in the subform or do you have to specify all field names? I have
tried a "generic"
If DCount(*) >18 (this works to display total number of records on the form
itself) but it doesn't work as a VB expression.

I have also tried
DCount("[WarningID]","[DriversWarnings]","DriverID=VehiclesDrivers.DriverID"
but this doesn't work either. Have I got the syntax wrong? Am I putting the
correct fields in the expression? I can't work out where the DriverID should
come from in the last part of the expression - from VehiclesDrivers (on the
calling form which opens the Drivers form?) The Drivers form itself is opened
from a button on VehiclesDrivers subform, with the following OnClick event:

stLinkCriteria = "[DriverID]=" & Me![VehiclesDrivers.DriverID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Please help...

TIA
rich