How to limit the subform records

[Guest]

I want to put code to limit the number of records of subform. I mean I want
user to enter maximum 10 records in the subform. Once the 10 record is
entered, it will not allow any further entry/disable to new entry of that
particular record.

As advised earlier in the newsgroup, I tried the below code but it is not
working properly. For example In one of the record's subform I entered 10
records, the code worked and it disable more entry. But at the same time, it
effected to the other records also specially when I moved to a new record I
found the subform is not allowing to enter records.

The code is as follows :

Private Sub Form_Current()
If Me.RecordsetClone.RecordCount >= 10 Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If
End Sub

Please correct my code, so that I can complete this.

Regards.

Irshad

 

[John W. Vinson]

I want to put code to limit the number of records of subform. I mean I want
user to enter maximum 10 records in the subform. Once the 10 record is
entered, it will not allow any further entry/disable to new entry of that
particular record.

As advised earlier in the newsgroup, I tried the below code but it is not
working properly. For example In one of the record's subform I entered 10
records, the code worked and it disable more entry. But at the same time, it
effected to the other records also specially when I moved to a new record I
found the subform is not allowing to enter records.

The code is as follows :

Private Sub Form_Current()
If Me.RecordsetClone.RecordCount >= 10 Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If
End Sub

Please correct my code, so that I can complete this.

Regards.

Irshad

This code should work if it's in the Subform's Current event - do you perhaps
have it in the main form's Current event?

An alternative approach is to use the Subform's BeforeInsert event;

Private Sub Form_BeforeInsert(Cancel as Integer)
If DCount("*", "[childtable]", "[ID] = " & Me!ID) >= 10 Then
MsgBox "Only ten records allowed", vbOKOnly
Cancel = True
End If
End Sub

This counts records directly in the table (adapt to your table and fieldnames
of course) rather than the RecordsetClone to prevent hassles with filtered
forms.

John W. Vinson [MVP]

 

[Guest]

Thank you very much for your advise. I tried it, It worked perfectly.

Actually, I was using the code on subform current event only. But it
effecting another records subforms. Any how its done.

Regards.

Irshad

I want to put code to limit the number of records of subform. I mean I want
user to enter maximum 10 records in the subform. Once the 10 record is
entered, it will not allow any further entry/disable to new entry of that
particular record.

As advised earlier in the newsgroup, I tried the below code but it is not
working properly. For example In one of the record's subform I entered 10
records, the code worked and it disable more entry. But at the same time, it
effected to the other records also specially when I moved to a new record I
found the subform is not allowing to enter records.

The code is as follows :

Private Sub Form_Current()
If Me.RecordsetClone.RecordCount >= 10 Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If
End Sub

Please correct my code, so that I can complete this.

Regards.

Irshad

This code should work if it's in the Subform's Current event - do you perhaps
have it in the main form's Current event?

An alternative approach is to use the Subform's BeforeInsert event;

Private Sub Form_BeforeInsert(Cancel as Integer)
If DCount("*", "[childtable]", "[ID] = " & Me!ID) >= 10 Then
MsgBox "Only ten records allowed", vbOKOnly
Cancel = True
End If
End Sub

This counts records directly in the table (adapt to your table and fieldnames
of course) rather than the RecordsetClone to prevent hassles with filtered
forms.

John W. Vinson [MVP]