Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184

    Angry Unanswered: Recordset only picking up one record!

    I have created a function that takes input from an unbound form, and checks the table to see if the record exists. SSN and DateSigned are both text fields. For verification purposes, all data is entered twice by different people, so I'm checking:

    1) if the record is already entered twice, then reject the new entry.

    2) if the record is entered only once, then check to see if [Clerk] is the same as the current user. If different, no prob. If the same, then reject the new entry.

    Problem: .RecordCount always gives me one record. I copied the SQL statement into a query and got two records (the correct number). In VBA help, I see there's a difference between DAO and ADO, where one counts the number of records accessed (what does that mean??), NOT the number of records in the recordset. HELP!


    Here is the code:
    ---------------------------------------------------------------

    Public Function CheckPreviousEntries()
    'Check to see if the record has already been entered twice,
    'or has already been entered once by this user

    CheckPreviousEntries = False

    Dim rst2 As Object
    Dim intCount As Integer
    Dim strSQL As String

    strSQL = "SELECT * FROM tblW4Transactions WHERE [SSN] = '" & Me.txtSSN
    strSQL = strSQL & "' AND [DateSigned] = '" & Me.txtDateSigned & "'"

    Set rst2 = CurrentDb.OpenRecordset(strSQL)

    intCount = rst2.RecordCount

    If intCount >= 2 Then 'Reject. This record has already been entered twice.
    MsgBox "Your entry has been REJECTED. The entry for " & Me.txtSSN & " dated " & _
    Me.txtDateSigned & " has already been entered twice.", vbCritical, "ENTRY REJECTED"
    CheckPreviousEntries = True

    End If

    If intCount = 1 Then 'Check to see if this clerk entered it already
    rst2.MoveLast 'Move to the only record in the set
    If rst2("Clerk") = Me.txtClerk Then 'Reject. This clerk already entered this record.
    MsgBox "Your entry has been REJECTED. You have already entered this record." & _
    " Dual Key Entry Procedures require that data is re-entered by a second person.", _
    vbCritical, "ENTRY REJECTED"
    CheckPreviousEntries = True
    End If

    End If

    rst2.Close

    End Function
    Last edited by LisaChow; 10-21-04 at 16:56.

  2. #2
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    I rewrote part of the code to work like this, so I can see that for some reason it's only picking up one record:


    intCount = 0
    rst2.MoveFirst

    With rst2
    intCount = intCount + 1
    .MoveNext
    End With

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Quote Originally Posted by LisaChow
    strSQL = "SELECT * FROM tblW4Transactions WHERE [SSN] = '" & Me.txtSSN
    strSQL = strSQL & "' AND [DateSigned] = '" & Me.txtDateSigned & "'"

    Set rst2 = CurrentDb.OpenRecordset(strSQL)
    rst2.MoveLast : rst2.MoveFirst
    intCount = rst2.RecordCount
    You have to Move the pointer to the last record then to the first, then the property RecordCount has the actual records in the record set

  4. #4
    Join Date
    Oct 2004
    Posts
    5

    Alternate method...

    The following will also get you the record count - perhaps more efficiently than movelast, movefirst?

    strSQL = "SELECT count(*) FROM tblW4Transactions WHERE [SSN] = '" & Me.txtSSN

    Emily

  5. #5
    Join Date
    Feb 2004
    Posts
    42
    Emily,
    Hey, I'm trying to do something similar, but the code you gave doesn't seem to work for me. I'm trying to count how many records in a table do not have the checkbox checked. The check box signifies e-mails sent, so I'm trying to find out how many people I have not sent an e-mail to.

    I was trying something like this:

    Dim varX as Integer
    varX = "SELECT Count(tblAuditorAvail.Emailed) AS CountOfEmailed FROM tblAuditorAvail"

    Any ideas?
    Thanks,
    Noobie

Posting Permissions

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