I'm trying to count the number of items that occur on a subform so I have have a text box on my main form which shows the number of entries.
Basically what i have got is a set of tabs with the main details on the first tab and then a list of products that relate to these details on the second tab done as a subform.
I created a count query which looks at the info i have selected on the first tabs and then counts the entries in the subform. When I run this query with the form open it works perfectly, but what i want is the text box on my form to show the result. I've tried doing it so the control source of the text box is the part of the query I want but when I run it it displays #name?
Dim rstSubCnt As Adodb.Recordset
Dim sqlSubCnt As String
Dim cntSubCnt As Single
Set rstSubCnt = New Adodb.Recordset
sqlSubCnt = "SELECT Count(YourTable.YourField) As CountofYourField"
sqlSubCnt = sqlSubCnt & " FROM YourTable"
sqlSubCnt = sqlSubCnt & " WHERE (((YourTable.CriteriaField)=" & YourCriteria & "));"
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockBatchOptimistic
.Open sqlSubCnt, CurrentProject.Connection
If .bof = false then
if .bof = true or .eof = true then
cntSubCnt = 0
cntSubCnt = Nz(.Fields("CountofYourField").value,0)
Set rstSubCnt = Nothing
Me.YourTextBox = cntSubCnt