Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2009
    Posts
    7

    Unanswered: EOF false but still get "No Current Record"

    Basically this code goes through a table recordset to see if the name has already been entered or not, if it has, return True, if not, return False.

    The problem I getting is that even when EOF is False, when rst.MoveNext is executed, it says No Current Record. Why is it giving me this error?

    I thought it might be something wrong with my code that the EOF slips through or something but just before the rst.MoveNext runs I used a MsgBox to display the EOF and it indeed displays False.
    Help....

    Code:
    Private Function checkDuplicate(Name As String)
    'Make sure there are no duplicates
    Dim db As Database
    Dim rst As Recordset
    Dim sSQL As String
    
    Set db = CurrentDb
    sSQL = "SELECT Table1.Name FROM Table1;"
    Set rst = db.OpenRecordset(sSQL)
    
    'If the recordset is empty exit
    If rst.EOF Then
    checkDuplicate = False
    Exit Function
    End If
    
    rst.MoveFirst
    
    Do While Not rst.EOF
        
      If rst.Fields(0) = Name Then
    
      checkDuplicate = True
      Exit Function
    
      End If
    
    If rst.EOF Then
    checkDuplicate = False
    Exit Function
    End If
    
    rst.MoveNext
    Loop
    rst.Close
    checkDuplicate = False
    End Function

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You have overcomplicated the problem, and you are using row by row processing instead of set based which is much less efficient. This can be accomplished really easily by just getting the database engine to search for the name in the table. Note that DLookup is not efficient if called many times (e.g. from a query or in a loop) but is more efficient than a recordset if making a single call to the database.

    Code:
    Public Function checkDuplicate(Name As String)
    
        Dim sSQL As String
        
        checkDuplicate = Not (vbNullString = Nz(DLookup("Name", "Table1", "Name = '" & Name & "'"), vbNullString))
    
    End Function
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2009
    Posts
    7
    Can you look up the record number using Dlookup? Because I also have to check if the record that is found, is the current record, because if it is, then it isn't a duplicate.

    I ommited part of my code thinking it wasn't relevant. but I guess that was important to know. Basically I have a form with bound textboxes for the user to enter info into and custom buttons that save the record, go to a new record, or deletes the current record. When the user enters data into a new record, the data is autosaved, but if the user clicks anything other than Add, it will run Me.Undo. When the user clicks Add however, it will call this checkduplicate method (also passing in an argument of what the current record is) to make sure that the record is not found in the table, or if it is found that it is found in the current record position.

    So now I'm back to square two, why is EOF false but I'm getting "No Current Record"

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    Public Function checkDuplicate(Name As String, Surrogate As Int)
    
        Dim sSQL As String
        
        checkDuplicate = Not (vbNullString = Nz(DLookup("Name", "Table1", "Name = '" & Name & "' AND mySurrogate <> " & Surrogate), vbNullString))
    
    End Function
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - I'm assuming you've got a unique index on Name, yah? (makes no difference to the solution, but is necessary in good database design).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2009
    Posts
    7
    Name is the unique index.

    Wow, thats awesome, thanks pottle flump! Putting the recordnumber as a argument for the DLookup totally slipped my mind.
    Last edited by aznmaster; 07-21-09 at 12:49.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by aznmaster
    Name is the unique index
    Good - I worried briefly you were using this to enforce constraints.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, since you've posted different code to what you were using- have you confirmed the error still occurs? Because your original code works fine for me (it is simply inefficient).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jul 2009
    Posts
    7
    Okay, I tried my original code and it works. Totally clueless of why it didn't work before....
    Last edited by aznmaster; 07-21-09 at 13:02.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah! Just noticed you edited post #6. Good stuff

    When I run your code, the EOF & MoveFirst works fine.
    Correct - any more than about three DLookups (and that includes using it in a query where more than 3 rows are returned) usually mean you need to look at other methods.

    EDIT - I also noticed I left the sSQL declaration in there, and also I made my function public not private (for debugging purposes)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jul 2009
    Posts
    7
    I don't get where mySurrogate is comming from??

    There is no field in my table that says which record number it is.

    What I did in my previous code was figure out what the recordnumber was by using rst.RecordCount and compared to what the input parameter was in your case, Surrogate.

    But there is no field called mySurrogate in my table. Though I guess I could add an AutoNumber field
    hmm...

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - I ASS-U-MEd by recordnumber you meant autonumber.
    Adding one would be clunky.

    I would suggest you check the Name value before submitting to the database - this way you know it cannot be the row the person is currently viewing.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    I experienced a similar problem with my Do While loop while writing on one of my current projects...

    I just put a little if statement inside the loop

    If rst.AbsolutePosition = -1 Then Exit Do
    This will assure that as soon as loop reaches the final record, it will get out of the loop and won't have the opportunity to try the rst.MoveNext. I'm not sure the specific technical reason why it does this, but I know checking the absolute position before trying to move to the next record averts the problem.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  14. #14
    Join Date
    Jul 2009
    Posts
    7
    So you mean, change everything into unBound boxes and add the records manually using an append query, and delete records using a delete query instead?

    And that way I can check the table for duplicates more easily?


Posting Permissions

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