Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178

    Unanswered: Keeping Track of Record Numbers

    How can I keep track of record numbers using VBA?

    I'm having trouble keeping track of record numbers in my forms. I've tried the following VBA code, but I keep getting an error message telling me "You can't go to the specified record."

    (CurrentRec is a text box.)

    Private Sub CurrentRec_BeforeUpdate(Cancel As Integer)

    Dim EnteredData As String, NewRecNo As Integer, RecCntr As Integer

    On Error Resume Next

    If (Val(CurrentRec) = 0) Or (Val(CurrentRec) > (RCount + 1)) Then
    MsgBox "Try Again", vbOKOnly + vbCritical, "Invalid Record Number"
    DoCmd.CancelEvent
    Else
    If Val(CurrentRec) > OldCount Then
    NewRecNo = (Val(CurrentRec) - OldCount)
    For RecCntr = 1 To NewRecNo
    DoCmd.GoToRecord , , acNext
    Next RecCntr
    ElseIf Val(CurrentRec) < OldCount Then
    NewRecNo = OldCount - Val(CurrentRec)
    For RecCntr = 1 To NewRecNo
    DoCmd.GoToRecord , , acPrevious
    Next RecCntr
    End If
    End If

    End Sub

    The reason I use a for...next loop is because I had the same problem using the "DoCmd.GoToRecord , , acGoTo, Offset" format, and I wanted to try something else. By the way, I have PreviousRecord and NextRecord command buttons that work just fine.

    I do NOT get this problem when I use the built in form record navigator. The only problem there, is that I have no way of keeping track of where I just went. And that, of course, is what I need to do.

    Any suggestions? Thanks a lot.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    I'm not quite sure of what you are trying to accomplish, but what it looks like is a Jump or GoTo Record Box to go with your home made navigation buttons. IF this is the case, then consider this.

    a) Your GoTo Box (TextBox) should have the Enter Key Behavior property set to New Line in Field. Let's say this box is named GoToBox,

    b) In your Form's OnCurrent event, enter this code:
    Code:
    Me.GoToBox = Me.CurrentRecord
    c) In your GoToBox KeyDown event, enter this code:
    Code:
    If KeyCode = 13 Then
       KeyCode = 0
       Dim TotalRecords As Long
       Dim RecordToHit As Long
       RecordToHit = CLng(Me.GoToBox.Text)
       TotalRecords = DCount("[myRecordID]", "myTableName", "[myRecordID] > 0")
       If RecordToHit < 1 Or RecordToHit > TotalRecords Then
    	  MsgBox "The Record number entered (" & RecordToHit & ") is out of bounds." & vbNewLine & _
    			 "Please try again.", vbExclamation, "Record Number Out Of Bounds"
       Else
    	  DoCmd.GoToRecord acDataForm, "myFormName", acGoTo, RecordToHit
       End If
    End If
    Now....if a record number is entered into the GoTo box and the user hits the ENTER key, then the Form jumps to that record. The GoTo box is always updated through the Form's OnCurrent event. This means then, when you are navigating records with your buttons, the GoTo box keeps track of the current record....there is no special code required to do this.

    As for your Navigation buttons, here are some built in functions you can use:

    GoToFirst Button OnClick event:
    DoCmd.RunCommand acCmdRecordsGoToFirst

    GoToPrevious Button OnClick event:
    DoCmd.RunCommand acCmdRecordsGoToPrevious

    GoToNext Button OnClick event:
    DoCmd.RunCommand acCmdRecordsGoToNext

    GoToLast Button OnClick event:
    DoCmd.RunCommand acCmdRecordsGoToLast

    GoToNew Button OnClick event:
    DoCmd.RunCommand acCmdRecordsGoToNew

    The enabling and disabling of buttons as required will still need to be coded, but this is rather simple to do.


  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Thanks a lot. I will try it out as soon as possible; right now I was handed a task that will probably take me the rest of the day. I hope to report my results to you.

    Thanks again
    Sam

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Hi, CyberLynx,

    I tried your code and suggested changes. They worked like a charm. Thanks so much and kudos!!

    Sam

Posting Permissions

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