Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2005
    Posts
    44

    Unanswered: counting items on sub forms

    Hey guys!

    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?

    ANY IDEAS??????

    many thanks
    H

  2. #2
    Join Date
    May 2005
    Posts
    150
    are you familiar with ADODB?

    try something like :

    Code:
    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 & "));"
    
    With rstSubCnt
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockBatchOptimistic
    .Open sqlSubCnt, CurrentProject.Connection
    
    If .bof = false then
    .movefirst
    end if
    
    if .bof = true or .eof = true then
    cntSubCnt = 0
    End If
    
    cntSubCnt = Nz(.Fields("CountofYourField").value,0)
    
    .Close
    End With
    
    Set rstSubCnt = Nothing
    
    Me.YourTextBox = cntSubCnt

  3. #3
    Join Date
    Apr 2005
    Posts
    44
    no i'm not.... i havent got a clue!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •