Navigation Buttons

[silversun]

Hi there,
I have a bounded form/sub-form setup with navigation buttons that I've created using the Macro methods available to navigate through the records.
How do I disable my NEXT button when I am at last record or PREVIOUS button when I am at first record?
Macro or VBA? Which one is easier?
Thank you for your help

 

[Pat Hartman]

I don't have a sample to post but I think @MajP might have one that uses a class module. That would be a better option than writing code for each form.

 

[HiTechCoach]

This might help:

RecordNavButtons (lebans.com)

 

[MajP]

Solved - Form-specific codes in reusable class modules

Using the technique mentioned in this thread: Creating Pseudo Custom Controls with Class Modules, I have successfully created my own set of form-navigation buttons, along with [Save] and [Undo] buttons. However, there is a problem with the [Save] action. Each form has a different save criteria...

www.access-programmers.co.uk

There are lots of examples out there. Just goggle for more.

 

[silversun]

Your examples and google searches come up with all different VBA modules.
I thought there should be an Action available to use in embedded Macro under On Click event.
My understanding is Macro modules are there to make things easier if I don't wanna use VBA. Am I right?
Is everything done with VBA doable with Macro or vise versa?

 

[isladogs]

VBA is far more powerful than macros.
Macros can only do a very small proportion of what can be done using VBA.
However, assigning shortcuts to actions is done using an Autokeys macro and Autoexec macros provide a convenient way of running code at startup

 

[The_Doc_Man]

Is everything done with VBA doable with Macro or vise versa?

No. VBA is FAR more powerful and versatile than using a Macro. Access allows you to convert Macros to VBA but does not have the corresponding reverse button to convert VBA to Macros. It is EASILY possible to do things in VBA that could NEVER be done correctly by a Macro.

To be fair, there IS a place for beginners to use Macros - e.g. if you have a sequence of queries that need to be run in order, you could easily do that with a Macro. But if you had to test something complex about the queries after each macro step to verify that each one ran correctly, you are stepping outside of the range of Macro abilities.

My understanding is Macro modules are there to make things easier if I don't wanna use VBA.

Yes and no. Where there is a Macro action corresponding to what you want to do, yes it is easier and should work perfectly well. But as I described above, in some cases there IS no corresponding Macro action and VBA becomes your only available method.

Macros tend to be limited on making complex decisions and on error handling. Macros tend to be of limited usability for essentially linear steps. Oh, you CAN do some logic solely using a Macro - but like I said, limited when compared to VBA.

 

[silversun]

OK then with all have said about Macro and VBA here comes my question.
In a bounded form that includes a sub-form and buttons that are created (using Macro actions) to go to next/previous records, running a VBA module does have any conflict? If there are some tricks or rules needed to be considered please explain it for me.
Thank you

 

[isladogs]

It is possible to use any combination of saved macros, embedded macros and VBA procedures in the same form.
Personally, I wouldn't do so as that makes maintaining the 'code' more complex as it will be in several different places

 

[Pat Hartman]

The problem with navigation buttons whether powered by macros or VBA or as class modules, is that Access already includes this functionality for free. One of the options you may never have noticed is the Navigation Caption on the Format tab of the form's property sheet. I've enclosed a picture of a form with two subforms so you can see it. Using this caption property is frequently enough to make the scroll bars sufficiently distinct so the users don't have trouble with using them.

 

[silversun]

The problem with navigation buttons whether powered by macros or VBA or as class modules, is that Access already includes this functionality for free. One of the options you may never have noticed is the Navigation Caption on the Format tab of the form's property sheet. I've enclosed a picture of a form with two subforms so you can see it. Using this caption property is frequently enough to make the scroll bars sufficiently distinct so the users don't have trouble with using them.
View attachment 100705View attachment 100706View attachment 100707

I am aware of this functionality in Access but some of the users aren't. For that reason I am trying to create a set of navigation buttons larger and in the middle of the form.
Thank you for your time anyways.

 

[isladogs]

Something like these where buttons are disabled on the first or last record??

 

[silversun]

Something like these where buttons are disabled on the first or last record??

View attachment 100777

View attachment 100778

View attachment 100779

Yes. That's exactly what I am trying to do. Could you please show me how to build this nav. bar?

 

[cheekybuddha]

Put something like this in your form's Current event:

Private Sub Form_Current()

  With Me
    If .Recordset.BOF Or .Recordset.EOF Then
      ' Set focus to another control to avoid error disabling a control that has focus
      .txtXofY.SetFocus
    End If
    .cmdPrev.Enabled = Not .Recordset.BOF
    .cmdNext.Enabled = Not .Recordset.EOF
  End With

End Sub

(Untested!)

 

[silversun]

If .Recordset.BOF Or .Recordset.EOF Then

I like your code as it is very simple and easy to understand (almost!) for me. I have some issues here.
Unfortunately it doesn't work. I need your help to fix it please.
Is "Recordset" in your code known/predefined? Do I need to have a connection to table and retrieve the data from that table before I run this code?
I've pasted your code into my VBA module but Access engine changes "Recordset" to "recordset" automatically. That's why I am not so sure about recordset.

 

[isladogs]

Yes. That's exactly what I am trying to do. Could you please show me how to build this nav. bar?

You need 4 buttons cmdFirst, cmdPrev, cmdNext, cmdLast and a lable lblPos (for the 1 of 10 record count info)
You can find the same items and all required code as part of the form in the attached app

You don't need to worry about what the rest of the app is designed to do

 

[moke123]

Since your using the form recordset, I've had better luck with using the recordset.absoluteposition.

If frm.Recordset.RecordCount = frm.Recordset.AbsolutePosition + 1 Then
            MsgBox "No Next Record"
        Else
            frm.Recordset.MoveNext
        End If

heres an example navbar class that has a few options.
All but the next and previous buttons are optional.
A counter label is optional.
You can either have a messagebox fire if you try and go past the first or last record or you can set it to cycle back to the first or last records.

 

[cheekybuddha]

I've pasted your code into my VBA module but Access engine changes "Recordset" to "recordset" automatically.

My guess is that you do not have Option Explicit declared at the top of every code module, and you have somewhere declared another variable named recordset (lowercase)