Disable next record button on last record

This is not needed in general.
You should have your malfunctioning VBA code fixed to cure this issue.

/gustav

You are rigth, but I need it

I navigate in records in one given patient..(but I have hundreds of patients in the
  If I change the patient, I have to go out of the formular, select a new one and come back..

In that given patient, when I reach the last record.. I loose evrything.. particularly the patient number

So, when I ask a new record, the system is lost..

The solution is
  either to disable the arrows (previous next) when I am on a last or first record
 or disable the NEW button when I am not on a record with a patient number

Sorry, I was not clear

In a given patient, when I reach the last record.. AND Click on the next button, the formular dispays a complete empty sheet, this means that I have lost also patient number.. So when / If I choose to click on the NEW button, I will obtain an error, because I have lost the patient number, a primary key of the related table..

check and compare your codes with these codes

Option Compare Database
Option Explicit

Dim rs As DAO.Recordset
'------------------------------
Private Sub Form_Current()
Set rs = Me.RecordsetClone
rs.MoveLast
    Me.txtRecCount = Me.CurrentRecord & " of " & rs.RecordCount
End Sub
'------------------------------
Private Sub cmdLast_Click()
On Error GoTo Err_cmdLast_Click


    DoCmd.GoToRecord , , acLast
    
    Me.txtRecCount = Me.CurrentRecord & " of " & rs.RecordCount
    If Me.CurrentRecord = 1 And rs.RecordCount > 1 Then
        cmdNext.Enabled = True
        cmdLast.Enabled = True
        cmdNext.SetFocus
        cmdPrev.Enabled = False
        cmdFirst.Enabled = False
    End If
    If Me.CurrentRecord < rs.RecordCount Then
        cmdNext.Enabled = True
        cmdLast.Enabled = True
    End If

Exit_cmdLast_Click:
    Exit Sub

Err_cmdLast_Click:
    MsgBox Err.Description
    Resume Exit_cmdLast_Click
    
End Sub
'------------------------------
Private Sub cmdNext_Click()
On Error GoTo Err_cmdNext_Click


    DoCmd.GoToRecord , , acNext
    
    Me.txtRecCount = Me.CurrentRecord & " of " & rs.RecordCount
    If Me.CurrentRecord < rs.RecordCount Or Me.CurrentRecord > 1 Then
        cmdPrev.Enabled = True
        cmdFirst.Enabled = True
    End If
    
    If Me.CurrentRecord = rs.RecordCount Then
        cmdPrev.SetFocus
        cmdNext.Enabled = False
        cmdLast.Enabled = False
    End If

Exit_cmdNext_Click:
    Exit Sub

Err_cmdNext_Click:
    MsgBox Err.Description
    Resume Exit_cmdNext_Click
    
End Sub

'------------------------------
Private Sub cmdPrev_Click()
On Error GoTo Err_cmdPrev_Click


    DoCmd.GoToRecord , , acPrevious
    
    Me.txtRecCount = Me.CurrentRecord & " of " & rs.RecordCount
    If Me.CurrentRecord = 1 And rs.RecordCount > 1 Then
        cmdNext.Enabled = True
        cmdLast.Enabled = True
        cmdNext.SetFocus
        cmdPrev.Enabled = False
        cmdFirst.Enabled = False
    End If
    If Me.CurrentRecord < rs.RecordCount Then
        cmdNext.Enabled = True
        cmdLast.Enabled = True
    End If

Exit_cmdPrev_Click:
    Exit Sub

Err_cmdPrev_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrev_Click
    
End Sub

'------------------------------

Private Sub cmdFirst_Click()
On Error GoTo Err_cmdFirst_Click


    DoCmd.GoToRecord , , acFirst
    Me.Dirty = False
    
    Me.txtRecCount = Me.CurrentRecord & " of " & rs.RecordCount
    cmdLast.Enabled = True
    cmdNext.Enabled = True
    cmdNext.SetFocus
    cmdPrev.Enabled = False
    cmdFirst.Enabled = False

Exit_cmdFirst_Click:
    Exit Sub

Err_cmdFirst_Click:
    MsgBox Err.Description
    Resume Exit_cmdFirst_Click
    
End Sub

'------------------------------

Private Sub Form_Load()
Set rs = Me.RecordsetClone
rs.MoveLast
    Me.txtRecCount = Me.CurrentRecord & " of " & rs.RecordCount
    rs.MoveFirst
End Sub

Select allOpen in new window

Your answer will certainly help..  It contains the elements I will need (but will also need a bit time to adapt to my purpose)

I use the Wizard VBA code   proposed by access like this one (for the previous command)

Private Sub Previous_Record_Click()
On Error GoTo Err_Previous_Record_Click

    DoCmd.GoToRecord , , acPrevious

Exit_Previous_Record_Click:
    Exit Sub

Err_Previous_Record_Click:
    MsgBox Err.Description
    Resume Exit_Previous_Record_Click
   
End Sub

This generates an information window when I am on the first record and click on the previous button. And this is fine.

But The VBA code for the NEXT button doesn't do the same.. I produces the display of an empty formular.. Unusable..

The code is the following..

Private Sub Next_Record_Click()
On Error GoTo Err_Next_Record_Click

    DoCmd.GoToRecord , , acNext

Exit_Next_Record_Click:
    Exit Sub

Err_Next_Record_Click:
    MsgBox Err.Description
    Resume Exit_Next_Record_Click
   
End Sub

I can't tell if you are talking about custom buttons or the built in navigation buttons.  If it is custom buttons, Rey has provided the code.  If it is the built in navigation buttons, you have no control.  HOWEVER, each form has a cycle property and changing that to current record might solve the problem.  The cycle property is on the Other tab of the property sheet for the form.

Are you using the built-in navigation buttons, or have you created your own?

Is this a form or a sub-form that you are talking about.  If a sub-form, you will need to link the main form and the sub-form using the Master/Child relationship based on the PatientID.  If you do that, then the subform will automatically get the PatientID from the main form.

Gustav,

You've never used custom navigation buttons; really?  I almost never use the built-in ones.  I like the greater control I gain by creating and using my own.

Dale

 I almost never use the built-in ones.
I'm with @Dale Fye on this one.  I suspect @mx would be, too
Rey has posted the whole code behind a set of navigation buttons.
That's a bit of code to digest.

The guts of it are in the Current Event of the form.
Like Dale Fye, I use custom Nav buttons, and in many places, and so I've abstracted it all out into a separate code module.
At its heart, you fire up a recordsetclone object
If you are on a new record, going forward makes no sense -- and for me creating another new record makes no sense -- so those get disabled.
If you are not on a new record, move the recordsetclone back one record.  If BOF occurs, then you are on the first record: disable Previous and First.  Now move forward one to get to the original, and move forward again.  If EOF occurs, then you are on the last record: Disable Next and Last.
It gets called in the Current Event as
Call SetNavButtons(Forms!TheFormName)

Function SetNavButtons(f As Form)
On Error Resume Next
   Dim rs As DAO.Recordset, c As Control
   
'sets the navigation buttons to appropriate values
    Dim rsClone As Recordset
    Set rsClone = f.RecordsetClone
    If f.NewRecord = True Then
        f!cmdNewJob.Enabled = False
        f!cmdNext.Enabled = False
        f!cmdBack.Enabled = True
        f!cmdFirst.Enabled = True
        f!cmdLast.Enabled = True
    ElseIf rsClone.Bookmarkable = True Then
        f!cmdNewJob.Enabled = True
        rsClone.Bookmark = f.Bookmark
        rsClone.MovePrevious
        f.cmdFirst.Enabled = Not (rsClone.BOF)
        f.cmdBack.Enabled = Not (rsClone.BOF)
        rsClone.MoveNext
        rsClone.MoveNext
        f.cmdNext.Enabled = Not (rsClone.EOF)
        f.cmdLast.Enabled = Not (rsClone.EOF)
        rsClone.MovePrevious
    End If
    rsClone.Close

End Function

Select allOpen in new window

I rarely use custom buttons.
1. I'm not inclined to reproduce something that Access handles correctly
2. I don't want to run another query to count records to produce x of y
3. I don't want to run code that access is already running to track record position.
4. They take more room on the form
5. They don't offer the option of going to a specific record by typing in the record number field unless you add code for that also.
6. I rarely create a main form that allows navigation so in my world, navigation buttons are primarily used on subforms and the navigation bar just fits better.
7. Also, since most of my subforms are datasheets, I don't have the ability to actually place navigation buttons on the form.
8. I like consistency.  If I can't use custom buttons in all cases, then I prefer to use the navigation bar in all cases, especially now that it can have a name which gives the user a hint at what recordset it is controlling.

While 2/3 are miniscule in their use of resources, they are still resources I don't need to use.

LOL :)
I'm not inclined to reproduce something that Access handles correctly
I don't EVER want Access creating a new record, because in my world, it cannot do that correctly.
So, I am already looking at code to deal with new records
I don't want to run another query to count records to produce x of y
Nor do I, and I don't bother.  X comes from a textbox with =[CurrentRecord] in it
I don't want to run code that access is already running to track record position.
If I turn off all of Access navigation, I am not sure then that it is running any of that code :)
They take more room on the form
True, but they can be positioned wherever I want them to be for greatest end-user utility, and they give very noticeable feedback regarding position in the recordset, so I'll deal with the space issue.
They don't offer the option of going to a specific record by typing in the record number field unless you add code for that also.
In my world, the user has no idea if the record they want is X number of records from where they are at now, so there's no point to being able to do that anyway.
I rarely create a main form that allows navigation so in my world, navigation buttons are primarily used on subforms and the navigation bar just fits better.
The main form is where nav happens in my world, and having nav on main forms and on subforms confused the bejesus out of end users.
Also, since most of my subforms are datasheets
You and I discussed that -- never use them -- in part because you can't put controls on them.
I like consistency.  Me too.  That's why every form that has nav has the same set of buttons, named the same way, and I can have a global function that takes a form object as an argument to handle them.

It's a question of style, end-users, and type of data.

Hi, I didn't want to trigger a fight..

I am a physician who learned a bit of coding at times we used cards to input lines of codes, line by line, without even a screen to display something.. Time was not so much money at these times and we could wait for days for answers to our codes ...

So, when I have a tool like MSAccess, I modestly use every build-in code, because I win a lot of time. I learned nevertheless that these build-in codes have limitations like the one that triggered my question.
Why does the build-in "previous record" button open an alert window when you try to use the previous button on the first record and does not the symetrical alert when you are on the last record..  

I don't use the navigation bar, I create buttons on the form using the build-in tools (the wizard tools)

In a first step, I will try to use

 Me.AllowAdditions = Not IsNull(Me!PatientID.Value)

of Gustav, although I don't exactly understand what it does (could you explain it a bit?)

Probably something like :
If Me!PatientID.value="" then Me.AllowAdditions = Not
This will probably also need a line of code to reverse the process when the form dispalys a patient's record

But.. The natural thing should be to make the buttons disappear from the form when you don't have to use it.. This is the real natural thing..to do for a DB that will be used by a lot of untrained people.

So  if I find a bit of  time, I will try to decrypt Nick67's code, to see if it could help me to do exactly that because I like to build forms as natural as possible (for the user) and where most of the manipulations errors are prevented

Thanks for all these informations..

That was no fight, that was a discussion of the pros and cons of user defined navigation buttons vice the built-in buttons.

Actually, you don't want to hide the buttons (disappear) when they are not available, you want to set the Enabled property to False.  This way, users are not confused about why the button is missing.  If it is disabled (Enabled = False), then they know that for some reason they cannot use that button.

Generally, if you try to move beyond the "First" record, you are going to get an error.  But generally, if you try to move beyond the "Last" record, Access assumes you want a new record.  I generally disable the "Next" and "Last" buttons when I am on the last record of the recordset.

I have installed that code in the top part of the Newrecord subroutine

    If IsNull(Me.Réf_Patient.Value) Then
    Me.AllowAdditions = Not IsNull(Me!Réf_Patient.Value)
    Exit Sub
    End If

I added also
Me.AllowAdditions = True
in th etop part of the open_form subroutine

And this does the job I needed.. It avoids the error of trying to create a new record without a patient ID

First step done..

Now I will try to use  Rey's and Nick67's code..
This will take some time

How do I do the job with the points..Do I take the time to test what I can do with Rey's and Nike's code r shoud I close the question?

The Accepted Solution should point anyone viewing the question at what YOU used to get the job done.
You may flag other posts as Assisted Solutions if they helped you figure out or decide how to get the job done -- or decide on how NOT to get the job done.

It is nice for anyone coming afterward from Google looking for the same answer as you if they can do as you did.
If time allows, take the time to ensure you have a solution before closing.

A thank you note to all those who contributed, whether you chose to go their route or not, is always polite and appreciated.  Taking the time to explain how and why you distributed points, if it helps add clarity for anyone finding your Q later also adds greatly to the value of everyone's contribution.  Explaining and posting your final solution, if possible, does the same.

I begun using Nick67's code..
It looks like it could work.
But I have a compilation error
on   ElseIf rsClone.Bookmarkable = True
the message is "member of method or data not available (Introuvable) with Bookmarkable highlighted (Blue)

I work on W7 Pro, with MDAccess 2010, DAO 3.6 Object library is selected on the references
ModNavButtonState is set on a separate module and
Private Sub Form_Current()
Call SetNavButtons(Forms!Gestion_RCP)
End Sub
is included in the Gestion_RCP form VBA code

What can be wrong?
It is probably trivial, but I don't find where it could fail

And this does the job I needed.. It avoids the error of trying to create a new record without a patient ID

I'm going to guess that the actual cause of the error is code that you have in the wrong event that is dirtying the record when it shouldn't.  this is a particularly common problem in popup forms where some ID from the "parent" form is required as the foreign key in order to save the record in the popup form.  BEST PRACTICE is to NEVER dirty a form before the user does.  So, I populate the FK in the form's BeforeInsert event.  That event fires when the user types the first character in any control and it only fires for new records so it doesn't fire for record changes.  Your code is probably in the current event or perhaps the load event or activate event.

on   ElseIf rsClone.Bookmarkable = True
 the message is "member of method or data not available (Introuvable) with Bookmarkable highlighted (Blue)

I am at a loss there.
Anyone has thoughts?
Gestion_RCP is NOT a subform, right?  Because I didn't code for that!

Nick67 you are rigth, it is a main form (but includes 2 subforms)


Answer to Pathartman..
This has nothing to see with the code.. For the navigations between records (for a given patient), I usually use only the wizard tools.. affected to buttons installed on the form..
 The code generated is OK  for the command_previous_record : if I want to go  beyond the first record I have an alert window.. But if I am on the last record and click on the command_next_button nothing like this happens. Worth an empty (almost) blanck, shaded form is displayed which halas looks like a "New record".. But this is not the case.. . Empty means that I have lost the key that relate that form to a specific table (the key is the Patient ID). This is why that step must be forbiden to the user

Damn,

My post went missing.
Your old sample had ADO in the code Tools | References above DAO.
That causes the error

Sample attached
RCP.mdb

Cand you tell me what you did : the 5 first items of the tools/references are selected as weel in my own sample and the one you sent me. You have certainly done something because it works much better now.

I tried to implement your code on locally drawn figures.. I called CmdNext and cmdBack  (after renaming your own)
But the process idoesnt not work because, I triggered an another cmpilation error (Err 438) with the debogger stopped at

f.cmdBack.Enabled = Not (rsClone.BOF)

So, I really need to understand what you did..

Thanks for your help

Jacques

I did as I suggested in this  post  I copied my buttons from the sample and pasted them on your form.  I copied the code from my form and pasted it into the code of your form.  I copied the code from my module and created a module in your sample and pasted it in.

Then I tested, and I got the error
the message is "member of method or data not available (Introuvable) with Bookmarkable highlighted (Blue)

I looked in the code.  In Tools | References, I could see that the order is not what I expected.
The ADO library was higher in the list than the DAO library.
I switched the order and tested.  All seemed well.

ADO recordsets do not support Bokmarkable -- so DAO must be given priority.  The order of the five items MATTERS greatly.

OK, thanks. This is perfectly clear and now everything works..
I will begin to make the adaptation.

One last question. For trivial esthetic pupose, I would like to use my own custimzed command buttons..
I tried with your button to include an BMP image. But they look weird...

I Will work on it and joint the result in my final answer
Thank you once more.. Your input is very very helpfull

Jacques

You are welcome.
It is the concordance between the names of the controls and the names in the module that matter.  To delete cmdFirst and replace it with a new control named cmdPremier will be fine so long as in the module all instances of cmdFirst are replaced with cmdPremier.  Similarly cmdProchain, cmdDernier, cmd Nouveau, and I forget what cmdPrevious will become.

The mechanism remains the same.  Move the recordsetclone forward one record.  If EOF occurs you are on the last record.  Disable things.  Now move the recordsetclone back to its original spot, and then one record further back.  If BOF occurs you are on the first record.  Disable things.  Test for .NewRecord.  If true, disable things.  That's it

To avoid issues with DAO/ADO (they have similarly named objects) it is best to ALWAYS disambiguate the Dim statements.  You don't want to rely on one library being higher in the reference list than the other.  When Access is attempting to locate a variable it doesn't recognize, it searches for it starting with the first library in the list of references so although Nick's solution may have fixed this problem, there is an equal chance that it broke something else.

Go through your code and disambiguate ALL DAO/ADO references.
Prefix DAO objects with DAO -
Dim db AS DAO.Database
Dim rs AS  DAO.Recordset
 etc
Prefix ADO objects with ADODB -
Dim db AS ADODB.Database
Dim rs as ADODB.Recordset
etc.

@PatHatman
The error was on the recordsetclone, the .Bookmarkable property tossed an error.  Is there a way to tell a form that it should open itself with a specifc ADO/DAO recordset?

Form recordsets are DAO.  You don't have a choice.

Do not rely on reference library order.  ALWAYS disambiguate references.  DAO/ADO is the major problem since they are both commonly used in Access apps and they both expose similar objects with identical names so there is no reason to take the chance.  I always use DAO and I STILL disambiguate the references just in case I ever need to use ADO.  You may occasionally run into conflicts with other libraries and so some developers disambiguate EVERYTHING.  I do DAO/Excel/Word/Outlook.  It's not like it is a burden.  All you have to change are the Dim statements.

Not disagreeing.
But if forms are DAO then why did changing the order of references resolve the .Bookmarkable error with rhe recordsetclone object?

Apparently Access doesn't "know" that inherently.   It looks up the reference for the recordset regardless rather than saying - is this recordset bound to a form? it must be DAO.

One more question to Nick67

I succeeded in using my customized buttons on your code...

One trouble I don't understand (same with the original RCP  sample you have sent me):

If you click on "NewJob" and then click immediatly to previous record or firstrecord  (some user can be unpredictable) , you will produce an 2105 error (unable to reach the specified record).. If you do the same, but add a SaveRecord click, then everything is OK

What is wrong with this, and can it be avoided?

Hi,

I am on the last step. Every code proposed by Nick67 is installed and working fine.
But of course, I have a bug....
The New (Nouveau) command produces a 2105 error : can't go to specified record

I tried ..
   Empying the 3 tables (RCP related for the main form through a query; -Microbiologie and _Traitement for the subforms)..  No result
   Copying the tables from a previous and working version : failed also
   Check the query related to the main form (_rqfiltreentreeformulaire): this one displays the results correctly

Could someone check that DB (Only One fictive patient is included here)

Thanks for any advice, I am lost
RCP-beta.mdb

Oh! I lost my final comments and the attached corrected file

Gustav helped me initially to forbide asking for e new record when the Identifying key of the form is lost

But the major help came from Nick67 which brought the solution to control the last/First Back/Next record commands

and finally Dale proposed to add AllowAdditions=yes to stop the 2105 error that occurred after the last corrections
RCP-Corrected.mdb

Ok,
First, form Gestion des patients has a problem.
Text box Réf_Patient has a control source of ID_Patient.Réf_Patient
but ID_Patient.Réf_Patient no longer exists in the form's recordset.
Likely Réf_Patient is now all that is required.
You have changed the recordset's items, correct?
I do not understand why you have Liste des prescripteurs.* in that query
You generally want to avoid creating queries that show two (autonumbers) in the new record line.
These are hard to add new records to, because they really expect and/or permit  you to add new records to two tables at the same time.

In general you'd use a subform displaying the information from Liste des prescripteurs.
You CANNOT display a subform on a continuous form.  The closest you can come to that is to display a datasheet subform in the form footer of a continuous form.  I have done that, now.

I don't know if my English Access hates [Formulaires].  I changed it to [Forms], so the query on the RCP form would work.
To add a new record, I coded it add and navigate to the record rather than use the DoCmd, which is not liked because of the complexity of the underlying query, I think.

Have a look!
RCP-beta-v1.mdb

You are right. I have detected the bug on "gestion des patients", yesterday. This is due to a recent correction I made that I should not have done. I corrected Réf-Patient to Réf_Patient.. But this was WRONG. If you correct it or if you remove Réf-Patient from the table the JOIN query will fail (not really necessary) then you have to change the filtering Query of the Source of "Gestion des patients". And I didn't.. Not really easy to do it using the properties of the form.  This was corrected on the present joined DB Version Corrected 1.54

This way to input patients with their doctor (the one who sent them) is used in my lab since more then 10 years..
I didn't want to work on it.. It is just the remaining of my early DB.. The error was my last correction. If you check it on the present 1.54 version , it works perfectly.  Without a subform. Of course it looks a bit unusual with the curious way to select the doctor (and the 3 text boxes as a reminder of the choice taken with the selection box on the right).

You are rigth also on another point.. I use double primary keys very rarely. But sometimes they can be mandatory. My first DB that still works daily is a DB were technicians follows plasma drug levels of patient.
The DB was builded to be able to work on drug interactions between drug that they dose in the patient (one subform) and all the treatments that these patient can take simultaneoulsy (a second subform).
To be able to work on drug interactions, I had to croos data between these 2 subforms and this requiered a table with a double key.

Anyway thank you very much for your very clear analysis and your help
RCP-Corrected-V1.54.mdb

Glad to be of service.

Nick67