Limit data entries

[deb]

I have 3 tables and a main form with a continuous sub form.

Table one is called tFuel (FuelID, FuelName, FuelType)
example
1, JetA, Liquid
2, Kerosine, Liquid
3, Propane, Gas
4, JetA, Duel
5, Kerosine, Duel
6, Propane, Duel
7, JetA, Multi
8, Kerosine, Multi
9, Propane, Multi

The second table (tPerfEmissionGua) holds the choice of (Gas,Liquid,Duel or
Multi) in the (FuelTypeChoice) field. Can only be one of the choices for the
combox (cboFuelType) that is in the masterform called (fPerfEmissionGua)

The Third table (tPerfEmissionGua) holds the choices of (JetA,
Kerosine,Propane...)
This is used as a combobox (cboFuel) in a continuous form that is the
subform called (fPerfEmissionGuaDetails)

After all of that.. My question is

How can I limit the entries in the continuous sub form as follows
if the main form combobox cboFuelType value is gas or Liquid - allow only
one entry in the subform
If the combobox cboFuelType value is Duel - allow only two entries in the
subform
If the combobox cboFuelType value is Multi- allow unlimited entries in the
subform

How can this be done?

 

[Ken Sheridan]

Add the following function to the parent form's module:

Public Function LimitSubformEntries() As Integer

Dim intCount As Integer
Dim frm As Form

Set frm = Me.fPerfEmissionGuaDetails.Form
intCount = frm.RecordsetClone.RecordCount

Select Case Me.cboFuelType
Case "Gas", "Liquid"
frm.AllowAdditions = (intCount < 1)
Case "Dual" 'see note below re spelling of this
frm.AllowAdditions = (intCount < 2)
Case Else
frm.AllowAdditions = True
End Select

End Function

Note that in the above fPerfEmissionGuaDetails is the name of the subform
*control*, i.e. the control on the parent form which houses the subform.
This might not necessarily be the same as the name of the underlying form
object, so you might need to change the reference in the code.

Also note that the function must be declared Public as it will be called
from the subform's module.

BTW, I've assumed that the 'Duel' in your post should be 'Dual'.

In the parent form's Current event procedure, and in the cboFuelType
cintrol's AfterUpdate event procedure put:

LimitSubformEntries

In the AfterInsert event procedure of the subform put:

Me.Parent.LimitSubformEntries

Ken Sheridan
Stafford, England

 

[deb]

Thank you for your help. You answer was very detailed and easy to follow.
Sharing your knowledge has helped me greatly!!!