Re: subform records



"Allen Browne" wrote:

Reply posted to your thread yesterday:
Group: microsoft.public.access.forms
Subject: total records of nested subforms in a form


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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Arash kamankesh" <Arashkamankesh@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:68F97352-12C5-4DC1-9D03-6FCAF8ECDB48@xxxxxxxxxxxxxxxx

"Allen Browne" wrote:

Answered in one of the other threads where you asked this question.


hi
i can't find it because i have not recived any notification about that
i just can find this answer
can you help me again please
thanks
hi

i entered the code as you told but i encountered this error:
" run-time error '3075' :
syntax error (missing operator) in query expression
'count(tblItem.itemID)'."
i notify that
"itemID" &"setID" are autonumber as primery key
and "orderID" has number format
("sorry for bad english speaking")
thanks
.

Relevant Pages

  • Re: subform records
    ... total records of nested subforms in a form ... Cancel the BeforeInsert event of the innermost subform if there are already ... tblSet, with SetID primary key, and OrderID foreign key. ...
    (microsoft.public.access.formscoding)
  • Re: trapping users leaving new record b4 entring reqd data
    ... Allen Browne - Microsoft MVP. ... that out when i got your vba. ... Private Sub Form_BeforeUpdate ... Cancel = True ...
    (microsoft.public.access.modulesdaovba)
  • Re: Grouped MAX Records?
    ... The subquery returns the primary key value of the record where the client ... Allen Browne - Microsoft MVP. ... >> Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)
  • Re: Delete primary key
    ... Dim tdf As DAO.TableDef ... Set tdf = db.TableDefs ... Allen Browne - Microsoft MVP. ... new field the primary key. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Booking duplicates
    ... Doug Steele, Microsoft Access MVP ... Dim strWhere As String ... Cancel = True ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.modulesdaovba)
Loading