1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Stopping a form from going past the last record when using record navigation

Discussion in 'Business Applications' started by RKoch95, Nov 5, 2014.

Thread Status:
Not open for further replies.
Advertisement
  1. RKoch95

    RKoch95 Thread Starter

    Joined:
    Mar 21, 2011
    Messages:
    18
    Hi:

    I've created a form in Access-2013 and placed record navigation buttons (using the button wizard) in the footer to allow the user to scroll through the records. They are the usual buttons and function as you would expect:

    << | < | > | >>[/FONT]

    Problem is that when a user is on the last record in the table and clicks the ''right" button (in red above), the form displays a blank record. This is normal behavior for Access (as far as I know) and as long as a user enters NO DATA in ANY field, Access does not write the record to the table.

    Given that users may be less than astute :), I would like to eliminate the possibility of them creating a "blank" record in the table.

    Is there anyway to stop this default behavior in Access?

    Thanks
    RonK
     
  2. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    A combination of Edit checks(before save, before update) to prevent empty records from saving.This code attached to custom buttons , Navigation Buttons set to none on the form is the way I'm doing it in one of mine. Probably other ways as I inherited this and added the no new records unless 'Add new' Record Button was clicked.

    You may find another way to implement the theory without the custom navigation.
    Code:
    With Recordset
      If .AbsolutePosition = .RecordCount - 1 Then
    'you are on the last record
            DoCmd.GoToRecord , , acFirst
      Else
    'you are on some other record
            DoCmd.GoToRecord , , acNext
      End If
    End With
       ' DoCmd.GoToRecord , , acNext -this is commented out
    Exit_cmdNext_Click:
        Exit Sub
    Err_cmdNext_Click:
        MsgBox "cm Next Error Number: " & Err.Number & vbCrLf & Err.Description
        Resume Exit_cmdNext_Click
    End Sub
    
     
  3. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    The above loops them back to the first record of their search results. Note this is using 'Recordset'.
     
  4. RKoch95

    RKoch95 Thread Starter

    Joined:
    Mar 21, 2011
    Messages:
    18
    DRACEPLACE: Thanks, I think I can work with your solution. Although I'm not using a recordset (just moving through a table), I don't think there is any reason I can't open a recordset in the "right" navigation button custom code and apply your code.

    I'll give it a try and let you know my results.

    Thanks,
    Ron
     
  5. RKoch95

    RKoch95 Thread Starter

    Joined:
    Mar 21, 2011
    Messages:
    18
    draceplace:

    Okay ... with some tweaks, your suggestion worked very well. Here's the code I put into the "next rec" button call:

    Private Sub cmdNextRec_Click()

    Dim db As Database
    Dim rst As Recordset
    Dim Rec As Integer

    On Error GoTo cmdNextRec_Click_Err

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Risk Register", dbOpenDynaset)

    Rec = Me.FullKey.Value 'FullKey is my self calculated record index
    rst.Movelast
    rst.Movefirst

    With rst
    If Rec = .RecordCount Then
    DoCmd.GoToRecord , , acFirst
    Else
    DoCmd.GoToRecord , "", acNext
    rst.MoveNext
    End If
    End With

    cmdNextRec_Click_Exit:
    Set rst = Nothing 'close recordset
    Set db = Nothing 'close db
    Exit Sub

    cmdNextRec_Click_Err:
    MsgBox Error$
    Resume cmdNextRec_Click_Exit

    End Sub


    Since I was already calculating an "Index" (FullKey in my table), I compare that to the .RecordCount and when they are equal I loop back to the first.

    Thanks for you help. I'm marking this one SOLVED

    Ron
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    Ron, you can also use this version that makes the buttons invisible if the move is not available. it also provides a record position of the number of records.

    Dim rs As Object, Records As Integer
    Set rs = Me.Recordset.Clone
    If Not Me.NewRecord Then
    Me.Of = Me.CurrentRecord ' Forms![Kitchen Link].CurrentRecord
    rs.MoveLast
    Me.Records = rs.recordcount ' count of records
    rs.Bookmark = Me.Bookmark
    rs.MoveNext
    Me.Next.Visible = Not (rs.EOF)
    rs.Bookmark = Me.Bookmark
    rs.MovePrevious
    Me.Command36.Visible = Not (rs.BOF)
    Me.Command37.Visible = Not (rs.BOF)
    rs.MoveNext
    Me.Command38.Visible = True

    Else

    End If
    rs.Close
    Set rs = Nothing
     
  7. RKoch95

    RKoch95 Thread Starter

    Joined:
    Mar 21, 2011
    Messages:
    18
    OBP:

    Excellent. I like the use of the bookmark and making the button invisible when it no longer makes sense for the user to have it.

    The problem I found with the .AbsolutePosition was it always read '0' because I was closing and reopening the recordset everytime the button was pushed ... no matter which record was actually being displayed in the form so the comparison always failed. That made sense after I thought about it and thus my solution to use the index number as the comparison value.

    I'll code up you suggestion and let you know how it works.

    Thanks for your input!
    Ron
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    Ron, that code goes in the Form's On Current Event Procedure, you will obviously need to use your Button names to make it work
     
  9. RKoch95

    RKoch95 Thread Starter

    Joined:
    Mar 21, 2011
    Messages:
    18
    ODP:

    Thanks. Got it.

    Ron.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1136781

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice