Results 1 to 3 of 3
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    DOA Recordset RecordCount returns wrong value

    The first snippet of code is taken from the form's module where a user has selected a filter set from a combo box
    Code:
        Case 9014 ' Add parking stalls to the display
        Call AppStalls                          'Refresh table
        Call ShowStalls(True)                   'Make the tb's visible
        Me.Filter = ""
        Me.RecordSource = "QRegistryPrkgDisp"   'Need the registry joined DISTINCT with stalls
        Exit Sub
    From the same module, the user has requested use of one of the app's functionality dealing with email addresses taken from the current RecordSource
    Code:
    Private Sub cmdEMAToCB_Click()
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    ' We need to append additional criteria to the existing RecordSource to exclude records
    ' that do not include EMAs.
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    Me.cmdTexting.Visible = False         'Can't do both at the same time
    Me.cmdEMAToCB.Width = EMARunWidth     'Adjust the display a bit
    bolEMASequence = True                 'Indicate a sequence has started
    
    If Me.FilterOn Then      'Applies to both QRegistry AND QGroupings (need the addition)
        Me.Filter = Me.Filter & " AND not isnull(EMA)"
    Else
        Me.Filter = "not isnull(EMA)"  'E.g., RecordSource might be W/PrkgStalls
        Me.FilterOn = True
    End If
    Me.Requery
    
    frm_EMAtoCB ("Clone")      'Get full or filtered set (EMAtoCB will now have a clean DOA Recordset to process)
    
    End Sub

    Also from the same module, the function that differentiates between the two possible paths taken so as to determine which path option the user has taken. In the current situation, the user simply clicked on the command button, as shown above.
    Code:
    Public Function frm_EMAtoCB(Optional QSet As String)
    '*=*=*=(V10.2 Update:)*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    '  We can get here two ways, via the original command button or the newly created entry
    '  in the R-Click popup via =EMAtoCB("QRegSelects").  With this new interface, the user
    '  can request the email addresses be placed on the clipboard whenever they have been
    '  "Selected".  Baring a "Selected" Recordset, we simply clone our RecordSource, filtered
    '  or not.  (Mail server limitations will be in accord)
    '
    '  In either case, our Recordset includes FmtEMAs:
    '  """" & [LastName] & ", " & [FirstName] & """<" & [EMA] & ">,"
    '
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    Dim I As Integer
    Dim strSQL As String
    
    If QSet = "Clone" Then
         Set rsRegEMAs = Me.RecordsetClone
         strRstType = QSet
    Else
        'A little tricky here because we need to apply whatever filter is in affect
        'First
        
        If Not IsNull(Me.Filter) Then
            strSQL = "SELECT * FROM QRegSelectFltrd WHERE " & Me.Filter
            MsgBox strSQL
            Set rsRegEMAs = DBEngine(0)(0).OpenRecordset(strSQL)
        Else
            Set rsRegEMAs = DBEngine(0)(0).OpenRecordset(QSet)
        End If
    End If
    
    lngEMACount = rsRegEMAs.RecordCount
    intEMAPartCnt = 1
    
    If lngEMACount = 0 Then
        MsgBox "No records found that contain e-Mail Addresses."
        Exit Function
    End If
    
    'DEBUG: ADD QSET TO CAPTION TO VERIFY PATH TAKEN ABOVE
    Me.cmdEMAToCB.Caption = lngEMACount & " in " & QSet         '" EMAs found"
    
    '*=*=*=*=(Okay, what method to use?)=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    Me.lblMethod.Top = Me.lblPartitions.Top      'Makes Design View less confusing for us
    Me.cmdClipboard.Top = Me.lblPartitions.Top   'old farts :-)
    Me.cmdCompose.Top = Me.lblPartitions.Top     '
     
    Me.lblMethod.Visible = True
    Me.cmdClipboard.Visible = True
    Me.cmdCompose.Visible = True
    
    End Function
    The results, as shown in the next screenshot, reflects the count returned by lngEMACount = rsRegEMAs.RecordCount
    (As noted in the code, the " in Clone" stemmed from the temporary use of the command button Caption)
    Click image for larger version. 

Name:	000.jpg 
Views:	9 
Size:	70.6 KB 
ID:	45487



    The next screenshot, taken from the same display as the first, reflects the correct number of records in the "clone", 37.
    Click image for larger version. 

Name:	001.jpg 
Views:	9 
Size:	31.7 KB 
ID:	45488

    Finally, a screenshot of the data properties of the form.
    Click image for larger version. 

Name:	002.jpg 
Views:	9 
Size:	49.8 KB 
ID:	45489

    The most perplexing aspect of this caper is that during step-by-step DEBUG through the code that the lngEMACount = rsRegEMAs.RecordCount has in fact returned the correct value a couple of times.

    The DOA Recordset is (Public rsRegEMAs As DAO.Recordset) in the form's module so as to extend its scope to a couple of other forms ONLY called by the current form. I.e., the current form will be in the Forms Collection as Open any time those others are opened.

    Finally, last but not least, is where did the count "22" came from? It is the number of records currently displayed on the form's screen. If I drag the borders up to where, say, only 15 records are displayed, then that's the number that will be returned by "RecordCount" and shown in the command button caption.

    In case the question comes up:
    Code:
    Option Compare Database
    Option Explicit
    Dim strGrpName As String
    Dim strTemp As String
    Dim SortColumn As String
    Dim SortField As String
    Dim SearchColumn As String         ' Set to the field name when searching a column
    Dim SearchLeftPos As Integer       ' Left position of search window based on column detected
    
    Public rsRegEMAs As DAO.Recordset
    Dim rsRegTexting As DAO.Recordset
    Dim intCmdsVisible As Integer
    Dim lngEMACount As Long
    Dim intEMAPartCnt As Integer
    Dim lngPartitionSize As Long
    Dim lngLastPartition As Long
    Dim bolEMASequence As Boolean
    Dim strRstType As String           'Indicator of EMA & Texting type of Recordset
    Dim strBasseFldr As String         'RDS folder name
    
    Dim EMAMaxSize As Integer          'Maximum number of EMA's per partition (Obtained from Settings)

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I normally MoveLast (at the very least) to get the true record count.????
    Doesn't Access just give you as many records as it needs to start with.? Most other operating systems do I believe?
    If I was to process a recordset, I'd also MoveFirst, then start processing the recordset
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    SUCCESS! Why it never occurred to me to at least try inserting rsRegEMAs.MoveLast before asking Access for a count leaves me somewhat embarrassed.
    Thanks for waking me up
    Bill

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 08-07-2018, 05:04 AM
  2. Replies: 5
    Last Post: 12-17-2014, 09:51 PM
  3. Replies: 4
    Last Post: 09-04-2014, 07:00 AM
  4. Replies: 2
    Last Post: 04-10-2012, 12:30 AM
  5. Access 2003 returns empty recordset
    By Leelers in forum Queries
    Replies: 0
    Last Post: 03-20-2009, 11:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums