Control max number of records on subForm

[WatsonDyar]

Is it possible to limit a subform to 3 records?

 

[vbaInet]

Go to query bound to subform and look for the property called Top Values. Enter 3 in there.

 

[WatsonDyar]

I should have explained more. The subForm is based on child table:

Parent table: tStaffMembers
Child table: tRatingChain

I want to limit the child table to three record per staff member.

 

[vbaInet]

Forms should be based on queries instead of tables. So base it on a query and follow the suggestion of before.

 

[WatsonDyar]

I tried that will no success. I made a query based on the table, limited the top values to "3". The problem I'm having is that it limited all the records to 3. I need to limit the amount of records of the same foreign key to 3.

Any ideas?

 

[vbaInet]

Is the subform supposed to be read-only or read/write?

 

[WatsonDyar]

read/write.

Thanks for your time by the way.....

 

[burrina]

A couple of suggestions:

If DCount("PrimaryKey", "YourTable", "SomeField=True") >= 3 Then 'only show 3 records of OnCall Employees
'Your Criteria Here Me.Something.Enabled = False
End If

Public Sub SetFormAllowAdditions( _
ByVal frm As Form, _
ByVal lngRecordCountMax As Long)

' Limit count of records in (sub)form to that of lngRecordCountMax.
' 2004-10-06, Cactus Data ApS, CPH
'
' Call in (sub)form:
'
' Private Sub LimitRecords()
' Const lngRecordsMax As Long = 5
' Call SetFormAllowAdditions(Me.Form, lngRecordsMax)
' End Sub
'
' Private Sub Form_AfterDelConfirm(Status As Integer)
' Call LimitRecords
' End Sub
'
' Private Sub Form_AfterInsert()
' Call LimitRecords
' End Sub
'
' Private Sub Form_Open(Cancel As Integer)
' Call LimitRecords
' End Sub

Dim booAllowAdditions As Boolean

With frm
booAllowAdditions = (.RecordsetClone.RecordCount < lngRecordCountMax)
If booAllowAdditions <> .AllowAdditions Then
.AllowAdditions = booAllowAdditions
End If
End With

End Sub

HTH

 

[GinaWhipp]

Have a look at...

Limit Number of Record Entries...
http://access-diva.com/blog/?p=173

 

[burrina]

WatsonDyer Good Luck With Your Project!

 

[WatsonDyar]

This worked perfectly for me:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord = True And Me.RecordsetClone.RecordCount = 3 Then
MsgBox "Cannot add more than 3 rating officials. Press OK to continue.", vbOKOnly, "Maximum Number of Rating Officials Met"
DoCmd.GoToRecord , , acPrevious
End If
End Sub

Thank you EVERYONE!

 

[vbaInet]

I thought you were trying to restrict the number of records displayed.
Good to see you got some help!

 

[stopher]

In principle you should try to implement all your constraints at table level. That way you don't run the risk of forgetting to include the handing of the constraint in a form or query. For example if you were importing data to your database you would have to manually remember to check the integrity rules that you have not implemented at table level.

In your example, see here for how to implement the constraint at table level but do note the caveats.

Once you have implemented at table level you can use the forms On Error event to catch the error - in this case it would be error no 3317 (One or more values are prohibited by the validation rule <rule> set for <field name>. Enter a value that the expression for this field can accept.). Since the error isn't very specific you would still need to do some test like the one Gina suggested to determine the exact problem. But the key point is this code is run only when there is an error rather then at every change thus allowing the database engine to to the hard work. By the same token you can catch other constraint errors using 3317 e.g. values from the constraint list in your table.

3201 is another useful code for trapping foreign keys errors.

hth
Chris