How to limit insertion of records

[Faraz A. Qureshi]

I have two tables as follows:
TABLE1: (BUSINESS TYPE)
=====
ID
Business Type (With a lookup choice like: Individual, Partnership, Company)

TABLE2: (OWNERS)
=====
ID
Business Type (Looked up from Table1)
Owners

I want the Table2 based subform, to allow only a SINGLE record to be entered
if an INDIVIDUAL business type is selected in the Table1 based main form,
Maximum of SEVEN records if PARTNERSHIP business type is selected in the
Table1 based main form, and UNLIMITED records if COMPANY business type is
selected in the Table1 based main form.

Any guidance shall be highly obliged.

 

[Tom Wickerath]

Hi Faraz,

Try something like this (warning: this is untested air-code) in the
Form_Current event procedure of the subform.

Private Sub Form_Current

dim i As Integer
i = Me.Recordset.RecordCount

Select Case Me.Parent.{NameOfControl}
Case "INDIVIDUAL"
If i >= 1 Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If
Case "PARTNERSHIP"
If i >= 7 Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If
Case "UNLIMITED"
Me.AllowAdditions = True
End Select

End Sub

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Faraz A. Qureshi]

Thanx Tom!

XClent concept of AllowAdditions!!!

 

[Tom Wickerath]

Hi Faraz,

You might want to add a Case Else statement that sets AllowAdditions =
False, just to catch any other entries. Also, you should look into the
possibility that the control on your main form that has the Business Type is
not null, and handle that possibility.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________