Solved

msaccess newrecord failure

Posted on 2014-11-27
28
141 Views
Last Modified: 2014-11-30
Hi,

I need your help.
I builded a DB with an identification form.. to select an ID called Ref_ID
With that ID I open a second form (Data_Form) related to a table with the ID variable as a primary key

For each ID I record several data through Two subforms of that Data_Form

Everything works fine, except that I cannot create a new record  with that Data_Form. I use the conventional code

Private Sub New_Record_Click()
Dim CodePatient As Long
On Error GoTo Err_New_Record_Click


     CodePatient = Me.Réf_Patient    'This one is OK
     Me.Requery

    DoCmd.GoToRecord , , acNewRec
   
   
    Me.Réf_Patient = CodePatient   'this one produces an error  after execution Me.ré_Patient is NULL
    NewRecordFlag = True
    ChangeLockForm (True)
   

    Me.[_SubFormA].Requery
    Me.[_SubFormB].Requery

   
Exit_New_Record_Click:
    Exit Sub

Err_New_Record_Click:
    MsgBox Err.Description
    Resume Exit_New_Record_Click
   
End Sub


Must be something trivial or related to a property oy the form, I didn't set correctly..

Thanks for any help

Jacques
0
Comment
Question by:weissenb
  • 13
  • 11
  • 4
28 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 40469844
    If nz(Me.Réf_Patient,0)=0 then
          msgbox "Il n'y a pas un CodePatient en Me.Réf_Patient,  Arreter!"
          exit sub
     End if


      CodePatient = Me.Réf_Patient    'This one is OK
      Me.Requery

     DoCmd.GoToRecord , , acNewRec
     
     
     Me.Réf_Patient = CodePatient   'this one produces an error  after execution Me.ré_Patient is NULL
     NewRecordFlag = True
     ChangeLockForm (True)
     

     Me.[_SubFormA].Requery
     Me.[_SubFormB].Requery
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40469848
So,
CodePatient = Me.Réf_Patient    'This one is OK
But you must test it to see if a valid value is being given to CodePatient.
if not, then exit the sub until a proper value is entered into Me.Réf_Patient

Otherwise, there will be the error you see.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40469853
When we say null in fact it could be empty not a "null", so therefore it is safer to code to handel both null and empty like:

If nz(Me.Réf_Patient,"")="" then
          msgbox "Il n'y a pas un CodePatient en Me.Réf_Patient,  Arreter!"
          exit sub
End if

But I think, if you are dealing with none-exiting record, we need to do something else. Because in order for us to talk about null or empty, first we have to have a record.

Now, if we don't have record, then

debug.print varType(Me.Réf_Patient)

should return 9 (9 stands for object, it think). Please include the above line to see what it returns when it errors.

So, if this was the case (record didn't exist), then use:

If varType(Me.Réf_Patient) = 9 then    '<-- please verify it is 9 when it errors
          msgbox "Il n'y a pas un CodePatient en Me.Réf_Patient,  Arreter!"
          exit sub
End if

Basically, when there is no records, instead of returning a value (which deson't make sense to expect a value, null, or empty), it returns VarType of the text box which is object.

Mike
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40469856
@eghtebas
Dim CodePatient As Long
It makes no sense then to test
If nz(Me.Réf_Patient,"")="" then when you are looking for a Long

   If nz(Me.Réf_Patient,0)=0 then
           msgbox "Il n'y a pas un CodePatient en Me.Réf_Patient,  Arreter!"
           exit sub
      End if

    If isNumeric(Me.Réf_Patient,0)=False then
           msgbox "La valeur en Me.Réf_Patient n'est pas une numbre!"
           exit sub
      End if


This maybe, if needed
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40469860
You are assuming PK is long. I have seen many times it is not. What I gave works for both.

BTW, I saw in fact it is long.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40469866
I've blown up what you gave a lot, too
Leave Me.Réf_Patient with general format
Enter "foobar" in it.
Run what you gave.

It'll run past because "foobar" <> ""
If nz(Me.Réf_Patient,"")="" then
           msgbox "Il n'y a pas un CodePatient en Me.Réf_Patient,  Arreter!"
           exit sub
 End if

But CodePatient is dimmed Long.
Whatever is in  there will ultimately need to be IsNumeric = true

Just saying testing for "" when numbers are the expected input leads to problems.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40469869
If nz(Me.Réf_Patient,"")="" then  always works.

But to focus helping weissenb, we need to get some feedback as to what

debug.print varType(Me.Réf_Patient)

prints at error.

Mike
0
 

Author Comment

by:weissenb
ID: 40470076
Woauh! this was fast

First point :
I put a stop on the VBA code on front of the DoCmd..
I selected ID patient 5
This opened the DATAForm with the first record of patient 5

Than  clicked on "Newrecord"
The stop on VBA allowed me to show that both Me.Ref_Patient and CodePatient where 5

The first debug.print line of code returned  3
The second (aboutCodePatient) returned 5
The third again about varType returned 1

Looks like DoCmd changed the VarType of Me.[Réf_Patient]


 
  CodePatient = Me.[Réf_Patient]    
    Debug.Print VarType(Me.Réf_Patient)    ==> 3
    NewRecordFlag = False    
    DoCmd.GoToRecord , , acNewRec  
    Debug.Print VarType(Me.Réf_Patient)    ==> 1
    Debug.Print CodePatient ==> 5 (correct)
    Me.[Réf_Patient] = CodePatient
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40470211
So, if you are getting 1 when it errors, then use the code Nick gave earlier. FYI:
VarType
0
 

Author Comment

by:weissenb
ID: 40470302
How can this code
 
   Me.Réf_Patient = 4   (I tried it so alos)
   written Just after the DoCmd instruction  produces a No Valid data

looks crazy to me

I can of course put the code of Nick, but it only checks for error, how can it solve it?
I introduced that code, just to test, but the compilation was rejected..
Here is the code

Private Sub New_Record_Click()
Dim CodePatient As Long
On Error GoTo Err_New_Record_Click

     CodePatient = Me.Réf_Patient
     Me.Requery

    DoCmd.GoToRecord , , acNewRec
   
  If Nz(Me.Réf_Patient, "") = "" Then
           MsgBox "Il n'y a pas un CodePatient en Me.Réf_Patient,  Arreter!"
           Exit Sub
 End If

    If IsNumeric(Me.Réf_Patient, 0) = False Then
           MsgBox "La valeur en Me.Réf_Patient n'est pas une numbre!"
           Exit Sub
      End If
   
   
    Me.Réf_Patient = CodePatient
   
    NewRecordFlag = True
    ChangeLockForm (True)
   
    Me.[_SubMicrobiologie]!Réf_Germe = 1
    Me.[_SubMicrobiologie].SetFocus
 
Exit_New_Record_Click:
    Exit Sub

Err_New_Record_Click:
    MsgBox Err.Description
    Resume Exit_New_Record_Click
   
End Sub
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40470558
You have put my code too late
Dim CodePatient As Long
 On Error GoTo Err_New_Record_Click

      CodePatient = Me.Réf_Patient
      Me.Requery

     DoCmd.GoToRecord , , acNewRec

'Too Late!  If CodePatient is Null now there's no helping matters!
     
   If Nz(Me.Réf_Patient, "") = "" Then
            MsgBox "Il n'y a pas un CodePatient en Me.Réf_Patient,  Arreter!"
            Exit Sub
  End If

     If IsNumeric(Me.Réf_Patient, 0) = False Then
            MsgBox "La valeur en Me.Réf_Patient n'est pas une numbre!"
            Exit Sub
       End If


It must check FIRST AND FOREMOST that CodePatient has a valid value before proceeding
Like so:
Private Sub New_Record_Click()
 Dim CodePatient As Long
 On Error GoTo Err_New_Record_Click

'check first that Me.Réf_Patient contains a valid Long value.
'exit if it does not

   If nz(Me.Réf_Patient,0)=0 then 'null value in Me.Réf_Patient
            msgbox "Il n'y a pas un CodePatient en Me.Réf_Patient,  Arreter!"
            exit sub
    End if

     If isNumeric(Me.Réf_Patient,0)=False then 'non-numeric value in Me.Réf_Patient
            msgbox "La valeur en Me.Réf_Patient n'est pas un numbre!"
            exit sub
     End if

     If CLng(Me.Réf_Patient) <> Me.Réf_Patient then 'a non-Long value in Me.Réf_Patient
            msgbox "La valeur en Me.Réf_Patient n'est pas un type valide de nombre!"
            exit sub
     End if

'alright, if we have reached this point, then there is a valid value in Me.Réf_Patient 
'we'll set CodePatient now
      CodePatient = Me.Réf_Patient    'This one is OK
      Me.Requery  'Jacques, why6 are you requerying just before moving to a new record, that makes no sense!
      
     DoCmd.GoToRecord , , acNewRec 'new record
     
     'set Me.Réf_Patient to the valid value we saved in CodePatient 
     Me.Réf_Patient = CodePatient   'this one produces an error  after execution Me.ré_Patient is NULL
     NewRecordFlag = True
     ChangeLockForm (True)
     

     Me.[_SubFormA].Requery
     Me.[_SubFormB].Requery

     
 Exit_New_Record_Click:
     Exit Sub

 Err_New_Record_Click:
     MsgBox Err.Description
     Resume Exit_New_Record_Click
     
 End Sub

Open in new window

0
 

Author Comment

by:weissenb
ID: 40470670
CodePatient is always valid.. as is the first Me.Réf_Patient
I Checked with Patient ID =2
Codepatient and Me.Réf_Patient are 2 before the DoCmd

After the DoCmd, CodePatient is still 2

But the code Me.Réf_Patient = CodePatient does't work.. The error lays there somewhere

I put your code just before that code Me.Réf_Patient = CodePatient

The message was Il n'y a pas un CodePatient en Me.Réf_Patient

So why do I have that null value in Me.Réf_Patient

You right with teh Me.requery before the NewRecord.. is nonsense.. Forgotten why it is here.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40470680
Is Me.Réf_Patient bound to a field?  to an autonumber field?
You cannot change the value of a control bound to an autonumber field!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40470732
Let us NOT error-check
Comment out
' On Error GoTo Err_New_Record_Click

Let us see the true error!
Errors are notorious for causing the value of variables to be set back to null (ESPECIALLY global variables)
We are testing like mad for NULL -- but what if that is a side-effect of the error check, and not the cause!
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:weissenb
ID: 40470747
What drives me crazzy is that that code, exactly the same, even with the Me.requery on the wrong place is used on another DB (even several one) working since years..

But you are probably on the right point..(pure intuition), something is wrong with the Me.Réf_Patient
Could be the Me..   May be he loosed his mind.. But How?

Réf_Patient is the variable (but not a primary key) of the RCP Table which is feeded by a form called Gestion_RCP through a requiry where a filter is set.. I open the requiry with a filter on Réf_Patient, so that the records of the form contains only the RCP data of a given patient..

The Gestion_RCP form contains two subForms.. But this is not the question here.
0
 

Author Comment

by:weissenb
ID: 40470765
Of course..

Comment out
' On Error GoTo Err_New_Record_Click  The NULL thing means probably nothing
The error is traped and produces a Err.descrption which doesn't help :impossible d'attribuer une valeur à cet objet (No way to give a value to that object)
(the error number is -2147352567 )

This cannot explain why, with CodePatient being a numeric and recognised perfectly

Me.Réf_Patient=CodePatient produces an error
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40470837
Ok,

Let's see if we can change Me.Réf_Patient by hand

Private Sub New_Record_Click()
     DoCmd.GoToRecord , , acNewRec
 End Sub


Then go and try to set Me.Réf_Patient manually
Can you?
Or does Access say that the 'control can't be edited; It's bound to ... ''error
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40470937
Qu'est-ce que c'est?
ChangeLockForm (True)

if Me.Réf_Patient is .Locked=True when you go to set it's value, that will also go BANG!
0
 

Author Comment

by:weissenb
ID: 40471086
Private Sub New_Record_Click()
     DoCmd.GoToRecord , , acNewRec
 End Sub

This works, I tested with Form Gestion_RCP opened on patient ID 9.. RCP 1,  but I obtained a form with all fields empty (correct), also Réf_Patient and Réf_RCP
After input of the first field, the Rèf_Patient field displayed a  NEW PATIENT ID =10 with no RCP number and this is what I do not want. I need a new record (a new RCP) for that same patient..ID = 9

ChangeLockForm (True)

This is a subroutine which will allow edition, addition and suppression of records on the form and the 2 subforms
(When FALSE than all this is forbiden)

If Me.Réf_Patient is locked =True : where can I find that propertie. I check the field "Réf_Patient and changed the property of locked, but nothing changed
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40471106
Can you post the (hopefully) mdb?  This is hard to visualize, but I can read enough French to make some sense of it, I hope
After input of the first field, the Rèf_Patient field displayed a  NEW PATIENT ID =10 with no RCP number and this is what I do not want. I need a new record (a new RCP) for that same patient..ID = 9

Now, this to me indicates that DoCmd.GoToRecord , , acNewRec is not creating the new record in the table that you hope for, but in a different table.
Look at the RecordSource for the form.
Open it. Does it have the fields that you expect it to?
Add a new record
Is that the record that you expected to be created?
0
 

Author Comment

by:weissenb
ID: 40471498
I send it

To work on it..

  Select the Patient ID
 Select a patient (command box under the REF number of thepatient)
   Attention : if you want to add a patient, don't forget to give him a doctor..

The command will open the RCP form, with its two subforms..One for Bacteria discovered and one for treatment prposed

   On the the top you will see the ID of the patient, the RCP number you are diplaying and you can circulate between the records (RCPs)

 The commands are

 Classical door to return to the patient ID

Arrows to circulate between the records

on the top left.. A newrecord button (NOUVEAU)

At the middle on the right an lock/unlock command which looks like an edit box

 A command with a magnifying glass; to display a summary of all the records of that patient (this is not yet working)

 A save command (I didn't yet check that one)


Please be kind with me, I am not a professional DB creator and this one is a clone of an old DB I created 10 years ago, and some parts of the code may look strange..  

Thanks for your help

Jacques
RCP-Ortho-septique--For-EE1.mdb
0
 

Author Comment

by:weissenb
ID: 40471555
Oh!

Please go in the Menu Principal form in Creation mode

Change the name of the upper command to "Gestion des références"
and that of the other command to "Gestion des patients"

I changed the name before sending it to be more understandable to you, but forgot to change also the related code

It will work just doing this

Sorry for the inconvenience
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40471772
Please be kind with me
Certainment.
We have all been there.

Votre problem est avec _rqfiltreentreeformulaire
You took Ref_patient from ID_Patient, not RCP.
A new record command would then need to create new records in both tables -- and it cannot.
In Datasheet view the TWO [new] notations in the new line were the giveaway.

With the query fixed, there is no need to set or change Ref_Patient.
I have fixed the immediate problems, and uploaded the result.
There are other problems, but perhaps you will be able to tackle them, or help me understand your design.
RCP-Ortho-septique--For-EE1.mdb
0
 

Author Comment

by:weissenb
ID: 40472036
YES! That was it.. Thanks

The only point was that you put

' Me.Réf_Patient = CodePatient 'Pas nécessaire  

and this made that I lost the patient number of that new RCP record, therefore unable to allocate the RCP to any patient..

I removed the cote and everything went back to work..

Before I close the question, can you look at the micorbiologie subform.. Something is weird there

You see three fields : one for the bacteria name, a second one for the date when the sample was taken and a thirrd with the type of sample.. This last one is a list field.. I want people to select one type from a smalll list of items (Liste Milieu Prelevement), But what the subform shows is all the type of sample of the _Microbiologie Table, related to the subform
What I want is not exactly that. I want that the item shown is the item related taken from the _micobiologie table, i.e. related to the sample date and to the Réf_RCP of the main form. This is done here..
But when I click on the list I want to see only the propositions of the Liste Milieu Prelevement. It is not very correct to show all the items of the table _Microbiologie.  What did I do wrong?

Thanks for your help. You solved it brillantly and it helped me a lot

Best regards

Jacques
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40472182
Now,

The ControlSource of Modifiable20 is Milieu_Prélèvement
So that is where the data will be stored.
The RowSource is _Microbiologie
So that is where all of the choices in Modifiable20 will come from.

That is not what you want, correct?
So, we will change the RowSource to 'Liste Milieu Prélèvement'
and see if that is what you would like.
0
 

Author Comment

by:weissenb
ID: 40472314
You are right, but when I do that, the Modifiable20 stays empty

The solution I found and which works is :

Modify the -Micorbiologie Table, add a numerical code Réf_Milieu
Modify the Liste des Milieu, introduce a Réf_Milieu as a primary key

Document the _Micorbiologie table accordingly

and correct the necessary relations between _Micorbiologie table and Liste des Milieu..

And finally make the correction in the subform, with Réf_Mileu as the SourceControl and Liste des Milieu as the RowControl (called "Contenu" here..) this works,


Thanks a lot for your time and the help it provided to me

Jacques
0
 

Author Closing Comment

by:weissenb
ID: 40472316
This was a difficult bug to solve. It was done brillantly and very fast..
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40472586
Pas de tout!
You're welxome.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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