total records of nested subforms in a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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]...
 
Back
Top