limit number of records in the many side

[hikaru]

i have the following tables:
http://img519.imageshack.us/img519/571/tablesrelsm6.jpg

in the entry form, I want the user to only be able to enter (as maximum) 28
VOs, 6 Obstructions, 5 Letters per Contract (tbl_Contracts) NOT per
Cont_Monthly_Change

so there are 3 levels of tables.

How can I do this?.. any help is appreciated.

 

[Paul Shapiro]

Access doesn't have any built-in validation options to enforce child row
count limits, unless you make artificial limits with suitable primary key
choices. If tblVO's primary key was changed to the combination
{Cont_Monthly_No, voSequenceNumber}, where voSequenceNumber is a tinyInt,
you could enforce your limit by specifying a field validation rule for
voSequenceNumber Between 1 And 28.

Otherwise your only option is to write code in the forms to enforce the
limits you want. In the AfterInsert event of your subforms you could
disallow new rows when your limit is reached. You have to be thorough in
handling all events that could potentially effect your limits.

 

[hikaru]

thanks for your help, it works with the AfterInsert event in the subForm,
here is the code:

Private Sub Form_AfterInsert()
Dim COfObs As Integer
Dim stDocName As String
Dim rst As DAO.Recordset

'Me.Refresh

COfObs = 0
Me.Parent!Cbo_ObsCounto.Requery

Me.Parent!Cbo_ObsCounto.DefaultValue = Me.Parent!Cbo_ObsCounto.ItemData(0)
COfObs = Me.Parent!Cbo_ObsCounto.Value

If COfVOs = 6 Then
MsgBox "You have reached the limit of 6 Obstructions per Contract"
End If

If COfObs > 6 Then
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdUndo
MsgBox "You have reached the limit of 6 Obstructions per Contract, you
cannot add any more. Last added record was deleted."
DoCmd.SetWarnings True
End If

Form.Refresh
Set rst = Me.RecordsetClone
rst.MoveLast
Me.Bookmark = rst.Bookmark
Set rst = Nothing
'Me.Obs_Desc.SetFocus
End Sub


Cbo_ObsCounto is a combobox that is based on a query that count number of
records in the subForm.. here is the SQL of the query:

SELECT Count([Obs_No]) AS ObsCount
FROM Tbl_Cont_Monthly_Change INNER JOIN Tbl_Obstructions ON
Tbl_Cont_Monthly_Change.Cont_Monthly_No = Tbl_Obstructions.Cont_Monthly_No
WHERE
(((Tbl_Cont_Monthly_Change.Cont_Monthly_No)=[forms]![Frm_Cont_Monthly_Details_Edit]![Cont_Monthly_No]));

I hope this is gonna help someone, and thanks again Mr. Shapiro