total records of nested subforms in a form

A

Arash kamankesh

hi
i have a form named "orders" contains subform "sets" witch contains subform
"items"
i want to have maximom 14 "items" record in each form "orders" while each
"sets" record may have any number of "items"
tor example we can have 14 record "sets" with 1 record "items" in each
"sets" or

2 record "sets" with 7 record "items" in each "sets" record and ....
but i want to have maximom 14 record of "items"totaly in form "orders"
please help me
thanks
 
A

Allen Browne

Cancel the BeforeInsert event of the innermost subform if there are already
14 items in the order.

Presumably you have 3 tables here:
- tblOrder, with OrderID primary key
- tblSet, with SetID primary key, and OrderID foreign key.
- tblItem, with ItemID primary key, and SetID foreign key.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rs As DAO.Recordset
Dim strSql as String
Const lngcMaxItem As Long = 14

With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter a record in the previous form first."
Else
strSql = "SELECT Count(tblItem.ItemID) AS ItemCount " & _
"FROM tblSet INNER JOIN tblItem ON tblSet.SetID =
tblItem.SetID " & _
"WHERE tblSet.OrderID = " & ![OrderID] & ";"
Set rs = dbengine(0)(0).OpenRecordset(strSql)
If rs.RecordCount > 0 Then
If rs!ItemCount >= lngcMaxItem Then
Cancel = True
MsgBox "No more!"
End If
End If
rs.Close
End If
End With
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...
 
Top