i can't find it because i have not recived any notification about thatAllen Browne said:Answered in one of the other threads where you asked this question.
hi
" run-time error '3075' :Allen Browne said: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.
Reply to group, rather than allenbrowne at mvps dot org.
message
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' :Allen Browne said: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
message
:
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:
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
thanks for your helpAllen Browne said:To debug the error message, add the line:
Debug.Print strSql
immediately before the line:
Set rs = ...
When it fails, open the Immediate Window (Ctrl+G).
Copy the SQL statement there, and paste it into SQL View in a new query. Or,
mock up a query that does the same thing, and see what's wrong with the SQL
statement.
If you still can't see what's wrong, post the actual SQL statement that
shows in the Immediate Window.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
message
hiAllen Browne said: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
message
:
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" run-time error '3075' :i entered the code as you told but i encountered this error:
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
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.