Results 1 to 7 of 7
  1. #1
    Join Date
    May 2007
    Posts
    4

    Unanswered: Help with the Refresh. Newbie to Access 2002.

    I was asked to fix the little access database program. I believe it was migrated from Access 97 to Access 2202. The problem I am having is if I delete a record, I can't view any records after the record I delete. It displays the record in a recordset properly but when I want to view it, it opens a different record. Looks like it is not refreshing properly. Please help. Below is the code for the delete and the form load and form current. Thanks,


    Private Sub cmdDelete_Click()
    On Error GoTo EHandler

    Dim query As String, cmd As ADODB.Command, intResponse As Integer

    intResponse = MsgBox("Are you sure you wish to delete this book?", vbQuestion + vbYesNo, "Confirm Delete")
    If intResponse = vbYes Then

    query = "Delete from Books where BookID = " & Me.BookID
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdText
    cmd.CommandText = query
    cmd.Execute
    query = "Delete from Borrows where BookID = " & Me.BookID
    cmd.CommandText = query
    cmd.Execute
    DoCmd.RunCommand acCmdRecordsGoToFirst
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.Bookmark = Me.Bookmark
    Me.Requery
    'Mf.Bookmark = rs.Bookmark
    End If
    sExit:
    Exit Sub
    EHandler:
    MsgBox Err.Description & " - " & Err.Number
    Resume sExit
    End Sub

    Private Sub Form_Current()
    On Error GoTo EHandler

    If Me.Copies > 1 Then
    Me.cmdCopyBorrow.Visible = True
    Me.cmdReturn.Visible = True
    Me.cmdBorrow.Visible = False
    'Me.optIn.Enabled = False
    Me.optIn.Locked = False
    Me.optOut.Locked = False
    Me.frmIn.Enabled = False
    'Me.optIn.Locked = True
    'Me.optIn.TripleState = True
    'Me.optIn.Value = Null
    'Me.optOut.Enabled = False
    Me.txtInOUt.Visible = True
    Else
    Me.cmdCopyBorrow.Visible = False
    Me.cmdReturn.Visible = False
    Me.cmdBorrow.Visible = True
    Me.optIn.Locked = True
    Me.optOut.Locked = True
    Me.frmIn.Enabled = True
    Me.txtInOUt.Visible = False
    End If

    Dim query As String, rs As ADODB.Recordset, conn As Connection, x As Integer

    x = 0

    query = "Select * from Books, Borrows where Books.BookID = Borrows.BookID AND (Borrows.DateIn = '' OR IsNull(Borrows.DateIn) = True) AND Books.BookID = " & Me.BookID

    Set rs = New ADODB.Recordset
    Set conn = CurrentProject.Connection

    rs.Open query, conn, adOpenDynamic, adLockOptimistic

    If Me.Copies > 1 Then

    Do While Not rs.EOF
    x = x + 1
    rs.MoveNext
    Loop
    xIn = Me.Copies - x
    xOut = x
    txtInOUt = "Books In: " & xIn & " Out: " & xOut

    If xIn = 0 Then
    Me.cmdCopyBorrow.Enabled = False
    Else
    Me.cmdCopyBorrow.Enabled = True
    End If
    If xOut = 0 Then
    Me.cmdReturn.Enabled = False
    Else
    Me.cmdReturn.Enabled = True
    End If

    Else

    If rs.EOF = False Or rs.BOF = False Then
    BookOut = True
    Else
    BookOut = False
    End If

    If BookOut = True Then
    cmdBorrow.Caption = "Return"
    frmIn.Value = 2
    Else
    cmdBorrow.Caption = "Borrow"
    frmIn.Value = 1
    End If

    End If

    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing

    sExit:
    Exit Sub

    EHandler:
    MsgBox Err.Description & " - " & Err.Number
    Resume sExit

    End Sub
    Private Sub cmdClose_Click()
    On Error GoTo Err_cmdClose_Click


    DoCmd.Close

    Exit_cmdClose_Click:
    Exit Sub

    Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click

    End Sub

    Private Sub Form_Load()
    On Error GoTo EHandler

    If IsNull(Me.OpenArgs) = False Then
    Dim BookID As Integer
    BookID = Me.OpenArgs
    DoCmd.GoToRecord , , acGoTo, BookID
    End If
    Me.txtTitle.SetFocus

    sExit:
    Exit Sub

    EHandler:
    MsgBox Err.Description & " - " & Err.Number
    Resume sExit

    End Sub

  2. #2
    Join Date
    Aug 2006
    Posts
    559
    It's usually easier to post the db here so we can actually look at what you're trying to do rather than just the code. Can you do that in a zip format?

  3. #3
    Join Date
    May 2007
    Posts
    4
    Hi,

    I just put that in a zip file.

    Can anyone take a look? Thanks,
    Attached Files Attached Files

  4. #4
    Join Date
    Aug 2006
    Posts
    559
    Ok, I took out your one statement of "Me.Bookmark" in two locations and it seems to be working fine.

    The problem that I know is that you're table is based on an "autonumber" for the book IDs and this number does not get replaced when you delete a record, it will just leave that record blank. You can put information back in to that record once it is deleted but you cannot completely delete the record cause it will just add to the last number in your 'autonumber'.

    The one way you can try to get around this is with a 'DMax()', I think that's what it is. It's the way to automatically increase the number by 1 versus using an 'autonumber'.


    Here's the upload of the one I did. Let me know.
    Attached Files Attached Files

  5. #5
    Join Date
    May 2007
    Posts
    4
    Hi Grafixx01,

    I just try it and now it seems to happen to every record I try to view. It opens one record ahead.

    Thanks,

    P.S. Where would you put the DMAX() inside the code?

  6. #6
    Join Date
    Aug 2006
    Posts
    559
    Ok, I'll look at it again in the morning at work.

  7. #7
    Join Date
    May 2007
    Posts
    4
    Thanks,

    This is where the BookID changes from 167 to 169. I will try to do more research.

    query = "Select * from Books, Borrows where Books.BookID = Borrows.BookID AND (Borrows.DateIn = '' OR IsNull(Borrows.DateIn) = True) AND Books.BookID = " & Me.BookID

Posting Permissions

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