none
Custom Navigation Buttons on a form

    Question

  • I have created a tabbed form in access.  When the form opens, I would like the record count to display in a field along with custom navigation buttons.  I have created the buttons which work fine, and I have created an unbound field.  The record count should display in the unbound field, but I keep getting an error message when I run it.  It seems to error out on the .move first field.  lngcount = 0 and me.currentrecord = 1.  Any ideas how to make this work?

     In the Current event of the form I have placed the following code:

    Option Explicit

    Private Sub Form_Current()

    ' Provide a record counter for using with custom navigation buttons (when not using Access built in navigation)

        Dim rst As DAO.Recordset
        Dim lngCount As Long

        Set rst = Me.RecordsetClone

        With rst
            .MoveLast
            .MoveFirst
            lngCount = .RecordCount
           End With
       
    'Show the result of the record count in the text box (txtRecordNo)
      Me.RecCount = "Record " & Me.CurrentRecord & " of " & lngCount

    End Sub


    Monday, June 20, 2011 9:02 PM

Answers

  • I got it working with the navigation buttons made with the Wizard and this code:

    Private Sub Form_Current()

    ' Provide a record counter for using with custom navigation buttons (when not using Access built in navigation)

      Dim frm As Form, LastRec As Long, rst As DAO.Recordset
     
      Set frm = Forms!frmProjectEntry!sfrmMeasureEntryForm.Form 'MainForm Name
      Set rst = Me.RecordsetClone
     
      If rst.BOF Then
      frm!txtRecordNo = "No Records!"
      Else
      If Trim(frm!RecCount & "") = "" Then
      rst.MoveLast
      DoEvents
      End If
     
      LastRec = rst.RecordCount
      If Me.NewRecord Then LastRec = LastRec + 1
     
      frm!RecCount = "Record " & CStr(Me.CurrentRecord) & _
      " of " & CStr(LastRec)
     
      End If
     
      Set rst = Nothing
      Set frm = Nothing

    End Sub

    • Marked as answer by Geo Camp Wednesday, June 29, 2011 7:42 PM
    Wednesday, June 29, 2011 7:42 PM

All replies

  • lngcount = 0 and me.currentrecord = 1.

    I am assuming from your statement above that the error occurs when there are no records in the table and you open the form? If so then try the following. (I don't think that you need MoveFirst; just MoveLast but test.)

        With rst
          If .EOF Then
            lngCount = 0
          Else
            .MoveLast
            .MoveFirst
            lngCount = .RecordCount
          End If
        End With


    Regards, OssieMac
    Tuesday, June 21, 2011 6:41 AM
  • My mistake.  I forgot to mention this is the record count for a subform.  Depending on the project, some already have records.  Those projects are fine, it's just when we attempt to start a new project in the form/subform.

    I tried inserting your code that you gave me and all records say 1 of 0 even if there are six records.  (I took out .movefirst as it errored out.)  I thought this would be a simple reocord counter but i've had to work on it longer than the merge to word!  Any other suggestions?

    Tuesday, June 21, 2011 12:59 PM
  • Possibly should test for If NOT .EOF then move otherwise zero. Below example does this.

    I have not tested using a sub form but the following example works for a standard form with buttons for Move To First, Move to Last, Move to Next and Move To Previous.

    I have tested using a table for the RecordSource both with the table containing records and the table empty.

    Note that MoveFirst and MoveLast does not require the bookmarks for both the Form and the RecordsetClone to be aligned before the move because in both cases the first and last record are the same. Bookmark is only needs to aligned after the move.

    However, for MoveNext and MovePrevious, the bookmark in RecordsetClone needs to aligned with the Form bookmark before the move and then re-align the bookmark of the form to that of the clone after the move.

    Private Sub btnGoFirst_Click()
        Dim rst As DAO.Recordset
        Dim lngCount As Long
       
        Set rst = Me.RecordsetClone
       
        With rst
          If Not .BOF And Not .EOF Then
            .MoveFirst
            Me.Bookmark = .Bookmark
          End If
        End With

    End Sub

    Private Sub btnGoLast_Click()
        Dim rst As DAO.Recordset
        Dim lngCount As Long
       
        Set rst = Me.RecordsetClone
       
        With rst
          If Not .EOF Then
            .MoveLast
            Me.Bookmark = .Bookmark
          End If
        End With
    End Sub

    Private Sub btnGoNext_Click()
        Dim rst As DAO.Recordset
        Dim lngCount As Long
       
        Set rst = Me.RecordsetClone
       
        With rst
          If Not .EOF Then
            'rst bookmark to match that of form
            .Bookmark = Me.Bookmark
            .MoveNext
            If Not .EOF Then
              Me.Bookmark = .Bookmark
            End If
          End If
        End With

    End Sub


    Private Sub btnGoPrevious_Click()
        Dim rst As DAO.Recordset
        Dim lngCount As Long
       
        Set rst = Me.RecordsetClone
       
        With rst
          If Not .BOF Then
            'rst bookmark to match that of form
            .Bookmark = Me.Bookmark
            .MovePrevious
            If Not .BOF Then
              Me.Bookmark = .Bookmark
            End If
          End If
        End With
    End Sub

    Private Sub Form_Current()

    ' Provide a record counter for using with custom navigation buttons
    '(when not using Access built in navigation)

        Dim rst As DAO.Recordset
        Dim lngCount As Long
       
        Set rst = Me.RecordsetClone
       
        With rst
          If Not .EOF Then
            .MoveLast
            lngCount = .RecordCount
          Else
            lngCount = 0
          End If
        End With
       
    'Show the result of the record count in the text box (RecCount)
    Me.RecCount = "Record " & Me.CurrentRecord & " of " & lngCount

    End Sub


    Regards, OssieMac
    Tuesday, June 21, 2011 11:14 PM
  • I made all of the changes you suggested and I still cannot get this to work on the subform.  When I open sfrmMeasure on its own, the record count and buttons work fine.  When I open the main form to access sfrmMeasure the record count still shows 1 of 0 and none of the buttons work.  I even tried re-creating the buttons and the whole subform with no luck.  Would moving the record count to another event work, or does it have to be in the current event?
    Wednesday, June 22, 2011 1:12 PM
  • I have to go out today so have not got time to work on this now but when I return I will set up a sub form and see what is occurring but in the mean time a question and something else to test.

    What is the record source for the sub form. Is it a table that is linked to the record source for the main form? If I set up a test I need to know this because I am wondering if it cannot move through the records because of the way it is tied to the main form.

    Now to test the Form_Current() code. Set up a command button on the form and then change the sub name of Form_Current() to the command button's click event and run the code from the button and see what occurs. That will  test if the code works.

    Also try putting the inbuilt navigation buttons back on the form and test if using these return the correct results? This is a test to see if what you are attempting to do can in fact be done. When testing this, also check what is occurring with the records on the main form.

    An additional thought added on Edit: Is there a reason for not using the inbuilt navigation buttons? Just thought that maybe there is a problem using them that might be able to be alleviated.


    Regards, OssieMac

    Wednesday, June 22, 2011 8:01 PM
  • Sorry, I didn't get to this until this morning. 

    The record source for all forms in the db are queries.  The main form is using a query which is from the projects table.  The subform is using a query which is from the measure table.  They are linked by projNum.  Whenever I change the design to have the forms record source be the tables, the buttons and code do not work.  The reason I have queries as the record source are because there are calculations that need to be performed behind the scenes.

    I added the code to a button on the subform and it works every time I click on it.  The navigation buttons work fine as well.

    Thursday, June 23, 2011 3:25 PM
  • The record source for all forms in the db are queries.

    I added the code to a button on the subform and it works every time I click on it. 

    I didn't get back to it yesterday either and I have to go out again today. However, it appears that the code works fine but just does not work with the form's current event.

    Place a MsgBox in the current event like the following to ensure it is getting called. (I am sure it is but need to test.)

    Private Sub Form_Current()
    MsgBox "Form_Current called"

    Just speculating now until I can get back to doing some more testing but because you are using a query as the record source, try a requery in Load event. This will ensure the form has records. My reason for this is that 1 of zero records indicates an empty record source; the 1 being an unsaved empty record that is displayed. Can't put ReQuery in Current event because it will create repetitive calls to current event each time the requery is run.

    Private Sub Form_Load()
      Me.Requery
    End Sub

     


    Regards, OssieMac
    Thursday, June 23, 2011 8:16 PM
  • The message box pops up when I open the forms, and the reQuery does not fix the problem.  Still record 1 of 0.
    Thursday, June 23, 2011 8:51 PM
  • I'm sorry but I can't even speculate why it might not work so I have to give up. 

    I suggest that you post a new question and include the following information to include what you have told me:

    The code being used in the Current event.

    Stipulate that the code works from a button on the form but won't work from the Current event.

    Include that the forms standard navigation keys work. 

    Stipulate that the forms record source is a query.

    Include if the forms Record Source query is built into the form as when you click the 3 dots at the end of the Record source to build it of if the query is constructed separately.

    That you confirmed the current event runs by using a msgbox.

    Include the Load event code to ReQuery.


    Regards, OssieMac
    Friday, June 24, 2011 8:27 PM
  • Why not just query the table

    SELECT count(*) AS cnt FROM myTable

    It returns 1 record with the count.

    Friday, June 24, 2011 9:40 PM
  • I got it working with the navigation buttons made with the Wizard and this code:

    Private Sub Form_Current()

    ' Provide a record counter for using with custom navigation buttons (when not using Access built in navigation)

      Dim frm As Form, LastRec As Long, rst As DAO.Recordset
     
      Set frm = Forms!frmProjectEntry!sfrmMeasureEntryForm.Form 'MainForm Name
      Set rst = Me.RecordsetClone
     
      If rst.BOF Then
      frm!txtRecordNo = "No Records!"
      Else
      If Trim(frm!RecCount & "") = "" Then
      rst.MoveLast
      DoEvents
      End If
     
      LastRec = rst.RecordCount
      If Me.NewRecord Then LastRec = LastRec + 1
     
      frm!RecCount = "Record " & CStr(Me.CurrentRecord) & _
      " of " & CStr(LastRec)
     
      End If
     
      Set rst = Nothing
      Set frm = Nothing

    End Sub

    • Marked as answer by Geo Camp Wednesday, June 29, 2011 7:42 PM
    Wednesday, June 29, 2011 7:42 PM