Limiting number of records in a subform

[djjay]

Hi.

I have a main form in by system [Projects] with a subform which is used to allocate employees to each particular record. On the main bookings form there is a field which states the number of employees required for that project (this is always stated in advance).

I now want to know if it's possible to limit the number of records on the subform so that if for example the bookings form says "2" employees required then only 2 records can be added in the subform for that record.

The subform is setup in continuous forms format if this helps at all.

Any help would be much appreciated

 

[TheAceMan1]

How are ya djjay . . . . .

In the OnCurrent event of the subform, copy/paste the following ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Dim cnt As Long
   
   cnt = Me.RecordsetClone.RecordCount
   
   If cnt >= Forms![purple][b]MainformName[/b][/purple]![purple][b]CountControlName[/b][/purple] Then
      Me.AllowAdditions = False
   Else
      Me.AllowAdditions = True
   End If[/blue]

See Ya! . . . . . .

 

[djjay]

Hi AceMan. Thanks for your reply. I've tried the code you've given and it seems to work for the very first record when i reset all the info in the database but after that my subform wont allow any entries it jst comes up blank.

Any ideas? I've tried getting the form to refresh with the OnChange facility of the field controlling the count of records but this doesnt change anything.

Thanks

 

[TheAceMan1]

djjay . . . . .

[blue][b]Change:[/b]
   If cnt >= Forms!MainformName!CountControlName Then
[b]To:[/b]
   If [purple][b]cnt <> 0 And[/b][/purple] cnt >= Forms!MainformName!CountControlName Then[/blue]

See Ya! . . . . . .

 

[djjay]

It's still not working but i think it might be a problem with my code somewhere else. I'll keep checking, thanks for your input so far

 

[TheAceMan1]

[blue]It's still not working but i think it might be a problem with my code somewhere else.[/blue]

I have my doubts!

Is there anything else in the [blue]OnCurrent[/blue] event besides what I gave you?

See Ya! . . . . . .

 

[djjay]

Yeah i've just got rid of some code which was changing some of the properties on the form...seems to be working ok now, although the guy im doing the db for has decided he doesnt need that fuction now :S

thanks for your help anyways, will remember that code as I can see it coming in handy again sometime

Jay