Custom Navigation Buttons 5

[sara82]

I have disabled the navigation buttons that access provides to you. I want to create my own navigation buttons for:

Next, Previous, and New. I also ant to emulate the Record 1 of 4 in a text box.

Any help will be greatly appreciated.

Thanks

 

[sara82]

I know how to create the buttons using the wizard but I don't know about creating the textbox using VB code.

Also you know how in access the navigation buttons you can type in the record number and it'll take you to that record. Like for example it'll have Record 2 or 5 then where the 2 is you can type 4 and it'll take you to 2. Can that also be done through VB code.. and if so how.

Thanks.

 

[missinglinq]

I do the "Record Number" of "Records" in the caption bar at the top of the screen. I suspect you can figure out how to modify the snippet of code to suit your purpose in a text box!

Private Sub Form_Current()
Me.Caption = "Record " & CurrentRecord & " Of " &
RecordsetClone.RecordCount & " Records"
End Sub

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[ZmrAbdulla]

A more detailed page is here(

http://www.databasedev.co.uk/navigation-record-count.html)

________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials

 

[sara82]

Thank you missinglinq and zmrabdulla

ZamrAbdulla I went to the site and tried it out. It worked fine but I encountered one problem. When I am at a new record it tells me record 9 of 8 instead of displaying record 9 of 9 the way Access does it.

Missinglinq I tried yours out too and I came across that same problem.

Anyway to overcome this?

 

[ProgramError]

create four buttons. Call them NextRecCMD, PrevRecCMD, FirstRecCMD and LastRecCMD.

put this code in the vba window of the form. If you need help with this let me know.

Private Sub NextRecCMD_Click()
On Error GoTo Err_NextRecCMD_Click
    DoCmd.GoToRecord , , acNext
Exit_NextRecCMD_Click:
    Exit Sub
Err_NextRecCMD_Click:
    MsgBox "You have reached the LAST record", vbOKOnly
    'MsgBox Err.Description
    Resume Exit_NextRecCMD_Click
End Sub

Private Sub PrevRecCMD_Click()
On Error GoTo Err_PrevRecCMD_Click
    DoCmd.GoToRecord , , acPrevious
Exit_PrevRecCMD_Click:
    Exit Sub
Err_PrevRecCMD_Click:
    MsgBox "You have reached the FIRST record", vbOKOnly
    'MsgBox Err.Description
    Resume Exit_PrevRecCMD_Click
End Sub

Private Sub FirstRecCMD_Click()
On Error GoTo Err_FirstRecCMD_Click
    DoCmd.GoToRecord , , acFirst
Exit_FirstRecCMD_Click:
    Exit Sub
Err_FirstRecCMD_Click:
    MsgBox Err.Description
    Resume Exit_FirstRecCMD_Click
End Sub

Private Sub LastRecCMD_Click()
On Error GoTo Err_LastRecCMD_Click
    DoCmd.GoToRecord , , acLast
Exit_LastRecCMD_Click:
    Exit Sub
Err_LastRecCMD_Click:
    MsgBox Err.Description
    Resume Exit_LastRecCMD_Click
End Sub

Ian Mayor (UK)
Program Error
Programmers do it one finger at a time!

 

[sara82]

Thank you ProgramError:

That worked great. I am also trying to emulate the way Access has Record 1 of 9 etc. I tried this:

I created an unbound text box txtRecordNo

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
        '.MoveFirst
        '.MoveLast
        'lngCount = .RecordCount
    'End With
    
'Show the result of the record count in the text box (txtRecordNo)

   ' Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngCount

End Sub

When I am at a new record it tells me record 9 of 8 instead of displaying record 9 of 9 the way Access does it.I obtained this code form the link zmrabdulla provided.

 

[PHV]

lngCount = .RecordCount + Abs(Me.NewRecord)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[missinglinq]

sara82,

Not sure why this is important but this modification will do it:

Private Sub Form_Current()
If Me.NewRecord Then
Me.Caption = "Record " & CurrentRecord & " Of " & (RecordsetClone.RecordCount + 1) & " Records"
Else

Me.Caption = "Record " & CurrentRecord & " Of " & RecordsetClone.RecordCount & " Records"
End If

End Sub

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[PHV]

The Missinglinq, here a oneliner version of your code:
Me.Caption = "Record " & CurrentRecord & " Of " & (RecordsetClone.RecordCount + Abs(Me.NewRecord)) & " Records

 

[MajP]

A couple things to think about. Most likely you will want to use this functionality on many forms. At a minimum you should put the code and controls on a subform. Using Me.parent you can refernce the form that your on.
By far the best solution is to build a class module (clsNavigation) with all of the functionality built in.

The class module would have something like the following properties, and "with events" set:

mForwardControl as control
mBackwardControl as control
mFirstControl as control
mLastControl as control
mRecordOfRecord as control
mForm as form

Then in the future you just instantiate a clsNavigation and set the controls of your clsNavigation. You will have all the functionality of the navigation controls without writing any more code.

 

[sara82]

Thanks all for the help.

I used the oneliner code

Me.Caption = "Record " & CurrentRecord & " Of " & (RecordsetClone.RecordCount + Abs(Me.NewRecord)) & " Records"

I have the custom navigation buttons on a subform. So there is a one to many relationship between the main form and subform. When I navigate through my main form on the subform I get Record 1 of 1 Records when in reality there is Record 1 of 4 Records. But when I click on next it goes to say Record 2 Of 4 Records, Record 3 of 4 Records etc.

Any suggestions on how it the subform will reflect the true amount of records on load? If a user sees that they'll think there is only 1 record when there is more than that. They might not know to click on the next button in order to see the rest of the records.

 

[missinglinq]

PHV,

If memory were as dear as it used to be, you'd be a prince!

Actually, you are a prince! Cool hack!

I've already used it in my apps!

The Missinglinq

There's ALWAYS more than one way to skin a cat!