How to limit insertion of records


F

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.
 
Ad

Advertisements

T

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/
__________________________________________
 
Ad

Advertisements

T

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/
__________________________________________
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top