Solved

Disable next record button on last record

Posted on 2014-12-17
38
752 Views
Last Modified: 2014-12-22
Hi,

Is it a way to disable the NextRecord button when the form dispalys the last record
 (same thing with the previousrecord when you are on the firstrecord)

The reason why I need this is because, when the user is on the record next to the last, the form has lost is mind and when thu user asks here for a new record, VBA answers with an error..
To prevent this, I would like to disable the
  previous button when the users displays the first record    and
  the next button when the user displays the last record

I have seen the answer to this somewhere, but am unable to find it again

Any help would be greatly appreciated

Jacques
0
Comment
Question by:weissenb
  • 13
  • 12
  • 6
  • +3
38 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40504595
This is not needed in general.
You should have your malfunctioning VBA code fixed to cure this issue.

/gustav
0
 

Author Comment

by:weissenb
ID: 40504616
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
0
 

Author Comment

by:weissenb
ID: 40504623
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..
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40504624
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

Open in new window

0
 

Author Comment

by:weissenb
ID: 40504672
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
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40504677
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40504681
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.
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 total points
ID: 40504692
First, why do you have these custom navigation buttons? I've never seen a true need for such.

>  or disable the NEW button when I am not on a record with a patient number

That is easy to do. Use the OnCurrent event:

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

/gustav
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40504806
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40504943
 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

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40505055
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40505104
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.
0
 

Author Comment

by:weissenb
ID: 40505832
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..
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40505913
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.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 300 total points
ID: 40505918
Hi, I didn't want to trigger a fight..
Ah, no fight.
By just as the Pyramide du Louvre can trigger honest differences of opinion, so can a few things in MS Access:
Navigation
'Proper' data entry forms
SQL vs VBA for record manipulation
Naming conventions.
Error handling methods
If there was a right-or-wrong such matters would be settled long ago.
Instead they are matters of style and circumstance
Thanks for all these informations..
And indeed, that was the point, to present you with competing ideas about how your task can be accomplished and let YOU decide what works best for YOU.

Please find attached a working sample.
Incidentally, the buttons can be copied-and-pasted to any form
The form's code can be copied-and-pasted into the form's code module
And the code I posted needs to go into a stand-alone code module.
Alter this
Call SetNavButtons(Forms!TheFormName) as required and you are ready to go
NavButtons.mdb
1
 

Author Comment

by:weissenb
ID: 40506071
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?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40506088
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.
0
 

Author Comment

by:weissenb
ID: 40508152
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
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40508275
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.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 26

Expert Comment

by:Nick67
ID: 40508294
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!
0
 

Author Comment

by:weissenb
ID: 40508351
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40508388
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
0
 

Author Comment

by:weissenb
ID: 40510287
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40510314
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.
references
ADO recordsets do not support Bokmarkable -- so DAO must be given priority.  The order of the five items MATTERS greatly.
0
 

Author Comment

by:weissenb
ID: 40510918
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40510942
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
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40511004
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40511011
@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?
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40511040
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40511094
Not disagreeing.
But if forms are DAO then why did changing the order of references resolve the .Bookmarkable error with rhe recordsetclone object?
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40511106
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.
0
 

Author Comment

by:weissenb
ID: 40511475
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?
0
 

Author Comment

by:weissenb
ID: 40512731
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
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
ID: 40512776
cannot open the mdb from my iPad, but you might check whether your forms Allow Additions property is set to "No".  If so, then in the "New" button you will need to change that to "Yes" prior to moving to the new record.
0
 

Author Comment

by:weissenb
ID: 40513648
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40513749
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
0
 

Author Comment

by:weissenb
ID: 40514160
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40514181
Glad to be of service.

Nick67
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Input box criteria 3 20
Syntax error with between dates 6 19
formattig excel from access 3 18
Data Conversion 11 0
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now