Navigation Buttons (1 Viewer)

silversun

Registered User.
Local time
Today, 07:05
Joined
Dec 28, 2012
Messages
204
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

Super Moderator
Staff member
Local time
Today, 10:05
Joined
Feb 19, 2002
Messages
42,983
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.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:05
Joined
May 21, 2018
Messages
8,463
There are lots of examples out there. Just goggle for more.
 

silversun

Registered User.
Local time
Today, 07:05
Joined
Dec 28, 2012
Messages
204
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

MVP / VIP
Local time
Today, 14:05
Joined
Jan 14, 2017
Messages
18,186
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

Immoderate Moderator
Staff member
Local time
Today, 09:05
Joined
Feb 28, 2001
Messages
27,003
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

Registered User.
Local time
Today, 07:05
Joined
Dec 28, 2012
Messages
204
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

MVP / VIP
Local time
Today, 14:05
Joined
Jan 14, 2017
Messages
18,186
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

Super Moderator
Staff member
Local time
Today, 10:05
Joined
Feb 19, 2002
Messages
42,983
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.
AccessNavigationBar.JPG
AccessNavigationBar2.JPG
AccessNavigationBar3.JPG
 

silversun

Registered User.
Local time
Today, 07:05
Joined
Dec 28, 2012
Messages
204
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 100705 View attachment 100706 View 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

MVP / VIP
Local time
Today, 14:05
Joined
Jan 14, 2017
Messages
18,186
Something like these where buttons are disabled on the first or last record??

1653497010639.png


1653497073147.png


1653497112746.png
 

cheekybuddha

AWF VIP
Local time
Today, 14:05
Joined
Jul 21, 2014
Messages
2,238
Put something like this in your form's Current event:
Code:
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

Registered User.
Local time
Today, 07:05
Joined
Dec 28, 2012
Messages
204
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

MVP / VIP
Local time
Today, 14:05
Joined
Jan 14, 2017
Messages
18,186
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
 

Attachments

  • JSONParser_v2.5.zip
    1.4 MB · Views: 167

moke123

AWF VIP
Local time
Today, 10:05
Joined
Jan 11, 2013
Messages
3,852
Since your using the form recordset, I've had better luck with using the recordset.absoluteposition.
Code:
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.
 

Attachments

  • NavigationBar.accdb
    692 KB · Views: 153

cheekybuddha

AWF VIP
Local time
Today, 14:05
Joined
Jul 21, 2014
Messages
2,238
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)
 

Users who are viewing this thread

Top Bottom