?
Solved

OpenArgs, getting random errors.

Posted on 2012-09-03
23
Medium Priority
?
338 Views
Last Modified: 2012-09-21
I have been using the code below to open a form and then go to a patient's record. I am using the OpenArgs function so that after I open the form to the record that I initially chose, I can then navigate to other records without having to close the form first.

'Patient name double click opens PhysicianRecordForm
Private Sub PatientName_DblClick(Cancel As Integer)
DoCmd.RunCommand acCmdSaveRecord
Dim strFormName As String
Dim strCriteria As String
strFormName = "PhysicianRecordForm"
strCriteria = "[MRN]= " & Me![MRN]
DoCmd.OpenForm strFormName, OpenArgs:=strCriteria
End Sub

Open in new window


This has worked well up until the past few weeks when two of my users started getting error messages "record not found". I am getting the error on only about half of the patients, the other half still works well. Decompile and recompile does not help.  I think that it is a glitch with the OpenArgs function. Should I be using the TempVars method instead?  Any ideas?
0
Comment
Question by:Drjdh
  • 12
  • 10
23 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38361975
There should be no problem using the OpenArgs parameter.  Is [MRN] a Number data type? Or is it really a String data type?  If the latter then your strCriteria should be:
strCriteria = "[MRN]= '" & Me.[MRN] & "'"

Open in new window

If the above is fine, then you should try to narrow your problem down a bit.  Can you consistently produce the error for a certain patient?  If so, put a breakpoint somewhere in your form PhysicianRecordForm and see if OpenArgs has the correct MRN.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38361984
if you are using the open event of the form "PhysicianRecordForm" to get the openArgs value and getting the errors,

  use the "LOAD" event of the form.
0
 

Author Comment

by:Drjdh
ID: 38362069
Thanks IrogSinta, MRN is a number.

The interesting thing is that this line of code has worked well for the past 6 months or so. It is only in the past few weeks that 2 users have begun to have the problem. The problem is only with their copy of the database, and only with a few patients (records) in this database.

To me, this points to something corrupted. If I can't fix the problem, then I will have to reinvent the code as I can't have users crashing in the middle of the night!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Drjdh
ID: 38362074
Capricorn1, thanks for your help with this. You actually developed this code for me in a previous post *Thanks!*.  Regarding your question, here is the Load Event of the form that gets opened:

Private Sub Form_Load()
If Me.OpenArgs & "" <> "" Then
With Me.RecordsetClone
   .FindFirst Me.OpenArgs
    If Not .NoMatch Then
    Me.Bookmark = .Bookmark
    Else
    MsgBox "record not found"
    End If
End With
End If
End Sub

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38362083
are you using a FE-BE setup? is the back end an access db or not?
this could be a timing problem.
did you trace the operation they did when they got the error?
0
 

Author Comment

by:Drjdh
ID: 38362118
The database is not split.  It is run locally on a PC. One interesting point is that the two individuals who have had this problem are both running the Access file from a server location instead of the desktop.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38362128
i think it is about time that you split your db..
0
 

Author Comment

by:Drjdh
ID: 38362176
I know, I just don't have access to server space right now.
0
 

Author Comment

by:Drjdh
ID: 38365174
I spoke with the end-users that were involved in the cases and they seem to think that the error appeared after they clicked on a blank record.

Would the code be more robust if I revised it using the TempVars method instead?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38365338
<they seem to think that the error appeared after they clicked on a blank record.>

well, i don't consider that as an error
the message is just informing them that the record does not exists..

Private Sub Form_Load()
If Me.OpenArgs & "" <> "" Then
With Me.RecordsetClone
   .FindFirst Me.OpenArgs
    If Not .NoMatch Then
    Me.Bookmark = .Bookmark
    Else
   MsgBox "record not found"   '<<< THIS is where the message is coming from
    End If
End With
End If
End Sub
                                           




TempVars will just behave the same.
0
 

Author Comment

by:Drjdh
ID: 38365650
I guess that I was not clear; I understand that you will get an error message if the record does not exist.  They portend that by accidentally clicking a blank record, they somehow changed something in the database that is irreversible. The error that I am getting is on records that clearly exist; both in the tables and the queries that are associated with the form.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38366135
<The error that I am getting is on records that clearly exist; both in the tables and the queries that are associated with the form.>

ok, try this codes.
this will display in a msgbox the OpenArgs value that was passed to the form


Private Sub Form_Load()
If Me.OpenArgs & "" <> "" Then

msgbox me.openargs

With Me.RecordsetClone
   .FindFirst Me.OpenArgs
    If Not .NoMatch Then
    Me.Bookmark = .Bookmark
    Else
   MsgBox "record not found"  
    End If
End With
End If
End Sub
0
 

Author Comment

by:Drjdh
ID: 38403688
Thanks for the code Capricorn. I've been out of town.

On the records that are working the msg gives the MRN (key), then opens the form to that record. On the records that are NOT working, the msg gives the MRN, then "record not found" text.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38403795
that is what the codes must do.

if the record based on the MRN is not found, then the record is not there.
0
 

Author Comment

by:Drjdh
ID: 38404023
But the record is in the table. Interesting.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38404044
upload a copy of the db..  
and give explanation how to recreate the problem..
0
 

Author Comment

by:Drjdh
ID: 38417162
The database is too large to copy over (160mb without data). Is there a way to reset the OpenArgs? perhaps this is the problem.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38417783
<The database is too large to copy over (160mb without data).>

you don't need to upload the whole thing..
create a copy of the db, delete all the objects that is not part of this problem..
zipped the file and upload.
just make sure there are sample data available for testing.


<Is there a way to reset the OpenArgs? perhaps this is the problem. >
what exactly do you mean?
0
 

Author Comment

by:Drjdh
ID: 38420312
My thought is that the error was introduced as timing: OpenArgs sets a parameter in form 1 then form 2 uses the value in OpenArgs to find the record. Perhaps the value in OpenArgs has been locked (or something to that effect).
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38420394
<Perhaps the value in OpenArgs has been locked (or something to that effect).>

i couldn't imagine how it could happen..
that is why i need your sample db and the steps to reproduce the error.
0
 

Author Comment

by:Drjdh
ID: 38420497
Here is a copy of the database.  Double click on the patient name to open form 2.
Dysfunctional-DB.accdb
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 38423553
your form "PhysicianRecordForm" was saved with a value in the FIlter property

x
remove it and save the form..

see this revised db

i also enabled the record navigation in the form so  you can see how many records are available in the form.
functional-DB.accdb
0
 

Author Closing Comment

by:Drjdh
ID: 38423789
Thanks Capricorn!!  Great work.  

I don't know why I keep getting values in the filter property of my forms.  I have had a similar issue with the other form (Form1) causing records to be listed out of order. Is there a setting that I can change to prevent this in the future?
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…
Suggested Courses

829 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