Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    77

    Question Unanswered: question about delete record

    I have one recordset, under some condition I delete the current record. It works fine, but I got one problem. If the last record is meet the condition, and I delete it. It will cause problem. But If I use eof, bof to test it, it’s not eof, not bof.
    So how do I know the last record is deleted. Thanks a lot

    rs.MoveFirst
    Do Until rs.EOF
    If rs.Fields(0) = “xxxx”
    rs.delete
    rs.MoveNext
    Loop

    if the last record is deleted, it will have error, the record is deleted, so can't do the following

    Anybody know how to solve problem

    Thank you so much

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    why don't you just use

    DELETE *
    FROM YourTable
    WHERE yourField = "xxxx"

    if your recordset is based on a table, you could run this through code if you like,
    you could then step back a step until you can always move next or you could test for if the record is the last record and exit your do after this so it will not try to move into a record that doesn't exsit something like

    Code:
    Sub test()
        Dim qdf As DAO.QueryDef
        Dim rs As DAO.Recordset
        
        Set qdf = CurrentDb.CreateQueryDef("QryTemp", "SELECT * FROM tblTest")
        Set rs = CurrentDb.OpenRecordset(qdf.Name)
        
        With rs
            .MoveFirst
            Do Until .EOF
                If .Fields(1) = "xxxx" Then
                    .Delete
                    If .EOF Then Exit Do
                End If
            .MoveNext
            Loop
         End With
    End Sub
    HTH
    Dave

  3. #3
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    77
    i can't delete things from table.

    I only can delete from recordset for displaying.

    and after delete from recordset, it can't detect it's eof. so i can't use .eof to decide whether it's end of record and exit.


    thanks anyway for the suggestion

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Ok then Back to using SQL

    SELECT *
    FROM YourTable
    WHERE YourField <> "xxxx"

    And create your recordset this way this way you wont have to delete anything out of your recordset, it just wont be included,

    you could build up your SQL Statement in your code if you have a variable amount of things to get rid of

    i.e.

    Dim mySQL as String

    mySQL = "SELECT * FROM yourTable WHERE "
    For I = 1 to 10
    If I = 1 then
    mySQL = mySQL & "myField <> " & varfield(I)
    Else
    mySQL = mySQL & " AND myField <> " varField()
    End If
    And mySQL = mySQL & ";"


    or something Similar

    HTH
    Dave

  5. #5
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    77
    oh..... good idea. thanks

Posting Permissions

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