Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: If statement problem

    Hi,

    I've created a multiple If statement to do a quick search in two tables to check for records (see below), but upon executing the second search, even if the recordcount is 0 it runs it as if it contains a value.

    Here's my code:

    Code:
        Dim db As Database
        Dim rst As DAO.Recordset
        Dim sSQL As String
        Dim sSQL2 As String
        Dim stDocName As String
        Dim stLinkCriteria As String
    
        Set db = CurrentDb()
        sSQL = " SELECT Name FROM dbo_tblEmpContacts" & _
               " WHERE Emp_ID = " & Me!Emp_ID & ""
               
        Debug.Print sSQL
    
        Set rst = db.OpenRecordset(sSQL)
        
        stDocName = "frmEngageBD"
    
        If rst.RecordCount = 0 Then
        MsgBox "No employer contacts found.  Please go back and add a contact.", vbOKOnly
        Else
        GoTo SQL2:
        End If
        
        rst.Close
        Set rst = Nothing
        
    SQL2:
        sSQL2 = " SELECT FirstName, Surname" & _
               " FROM dbo_tblCollegeContacts" & _
               " WHERE Emp_ID = " & Me!Emp_ID & ""
        
        Debug.Print sSQL2
        
        Set rst = db.OpenRecordset(sSQL)
        
        If rst.RecordCount = 0 Then
        MsgBox "No college contacts found. Please go back and add a contact.", vbOKOnly
        Else
        If MsgBox("You must fill in the following engagement information," & _
                  "or it will result in a blank record. Do you want to continue", vbYesNo, "WARNING") = vbYes Then
           DoCmd.OpenForm stDocName, , , stLinkCriteria
        End If
        End If
        
        rst.Close
        Set rst = Nothing
    Can anyone see why it might think the last If statement has a recordcount greater than 0?

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what does the help system have to say about the recordcount property of a recordset?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    recordcount

    Could you use: if rst.eof and rst.bof then...(can't remember if you can use this with DAO).
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Sep 2005
    Posts
    240
    Thanks.
    Yeah. it's rst.EOF that works.

  5. #5
    Join Date
    Sep 2005
    Posts
    240
    Thanks.
    Yeah. it's rst.EOF that works.

    But it was also the fact that in the second recordset I was referring to the SQL in the first recordset.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I would personally use DCount() in this scenario...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    using dcount

    I guess the question would be: which would be slower, dcount or rst.eof/rst.bof?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    hard to say. I'm not sure how Access does it intrinsically.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Feb 2006
    Posts
    60
    ok, i have NFI about access really, but i am looking through other peoples codes and i am noticing this rest.eof and other wierd things all the time? what are these because they aren't in help lol.

  10. #10
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I believe that the recordcount property will return 0 for no records, 1 for at least one record. to get a true count, you have to move you recordset to the end first to populate the whole thing - at least that's how it was with DAO.

  11. #11
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Quote Originally Posted by neRok
    ok, i have NFI about access really, but i am looking through other peoples codes and i am noticing this rest.eof and other wierd things all the time? what are these because they aren't in help lol.
    ummm, what's NFI? oh, wait, could I say that I have NFI about on line chat speak?

    Anyway, EOF means End of File. The context here is for recordsets and EOF means the end of the recordset (1 spot past the last record). Check the help file, MSDN.microsoft.com and this forum for info about recordsets.

Posting Permissions

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