Problem with Parameter Query from a Form

[Lauri S]

I created a switchboard form and another form both of which have command
buttons to run a query and opening in form view. The query comes from 2
different tables. When I run the query from the query design view window,
everything is fine and I get the results in datasheet view. When I try to run
the query by clicking the command buttons in either form, I get a second
dialog box asking for a parameter value that I didn't set. The second dialog
box looks like this: tblAppts.ApptDate. If I ignore the 2nd dialog box and
hit enter I still get the reults I want; I just don't know why I get that
second box.
Code for command on switchboard:
Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

Dim stDocName As String

stDocName = "qryPtSearch"
DoCmd.OpenForm "frmPatient", , "qryPtSearch"

Exit_Command27_Click:
Exit Sub

Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click

End Sub

Code for command on other form(they run the same query):
Private Sub Command15_Click()
On Error GoTo Err_Command15_Click

Dim stDocName As String

stDocName = "qryPtSearch"
DoCmd.OpenForm "frmPatient", , "qryPtSearch"

Exit_Command15_Click:
Exit Sub

Err_Command15_Click:
MsgBox Err.Description
Resume Exit_Command15_Click

End Sub


The form does contain a main form and a subform. It is the subform that
contains all the patient appts.


My 2 tables are tblAppts and tblPatient with the Medical
Record Number (MRN) being the parameter to input to return the results.


Any assistance would be appreciated!!

Lauri S.

 

[Dale Fye]

Lauri

Can you post the SQL of the query?
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Lauri S]

SELECT tblPatient.PtName, tblPatient.MRN, tblPatient.DOB, tblAppts.ApptDate,
tblAppts.ApptStatus, tblAppts.Provider, tblPatient.Scanned,
tblPatient.BatchNumber
FROM tblPatient INNER JOIN tblAppts ON tblPatient.MRN = tblAppts.MRN
WHERE (((tblPatient.MRN) Like [Enter MRN]))
ORDER BY tblAppts.ApptDate DESC;

 

[Lauri S]

SELECT tblPatient.PtName, tblPatient.MRN, tblPatient.DOB, tblAppts.ApptDate,
tblAppts.ApptStatus, tblAppts.Provider, tblPatient.Scanned,
tblPatient.BatchNumber
FROM tblPatient INNER JOIN tblAppts ON tblPatient.MRN = tblAppts.MRN
WHERE (((tblPatient.MRN) Like [Enter MRN]))
ORDER BY tblAppts.ApptDate DESC;

 

[Dale Fye]

Lauri,

1. From the looks of your query, when you run it from the query grid, it
asks you to provide a value "Enter MRN". I assume that is the first dialog
box that you don't really mention in your message. Personally, if you
already know the patient ID, I would put a textbox (or a combo box, or a
list box) on my form that allows me to select the patient, then, in my
query, I would refer to that control. With this type of interface, the
WHERE clause would look something like:

WHERE tblPatients.MRN Like Forms!yourFormName.txt_MRN

2. Which brings me to my second point. Using LIKE in the query doesn't
make a lot of sense, unless you plan on putting in a partial MRN, in which
case, you really need to use an asterisk in front, in back, or both of the
value you are passing the query. The way you currently have it worded, it
must equal the value you enter when prompted for the MRN. The disadvantage
is the Like is significantly slower that = when it is evaluated. So, I
would rework the WHERE clause to:

WHERE tblPatients.MRN = Forms!yourFormName.txt_MRN

Note: This assumes that the MRN field is numeric.

3. The reason you are getting the second dialog is that the JET database
engine does not recognizt tblAppts.ApptDate as a valid field, so it assumes
it is a parameter you are trying to evaluate. Check to make sure that the
ApptDate field actually exists in tblAppts. Then take another look at the
query and make sure that you don't have a space in the table or field name
in the query.

4. Assuming that the query you posted is saved as qryPtSearch, I'm
confused. frmPatient should already have a RecordSource, which is either a
query or a table. I'm not sure what you are trying to accomplish with the
"qryPtSearch" parameter you have passed the form. If I were actually doing
this, I would get rid of that, and change the OpenForm statement to look
something like:

docmd.OpenForm "frmPatient",,,"[MRN] = " & me.txt_MRN

Hope this helps.

SELECT tblPatient.PtName, tblPatient.MRN, tblPatient.DOB,
tblAppts.ApptDate,
tblAppts.ApptStatus, tblAppts.Provider, tblPatient.Scanned,
tblPatient.BatchNumber
FROM tblPatient INNER JOIN tblAppts ON tblPatient.MRN = tblAppts.MRN
WHERE (((tblPatient.MRN) Like [Enter MRN]))
ORDER BY tblAppts.ApptDate DESC;

Lauri

Can you post the SQL of the query?
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Lauri S]

Dale,
I appreciate your response, thank you.

1. I checked both my query and my tblAppts for errors in spelling, etc. and
found nothing. I also changed the query to "=[Enter MRN]" instead of "Like"

2. On my form, my intention was not for data entry, but for display. I have
never tried using a text box to run a query, so I'm afraid I do not know how
to set that up.

3. My intention for the db is to have a couple of simple forms where a user
other than myself can input an MRN and see all the appointments that that
patient has had in form view. So I created frmPatient which contains the
patient info and a subform to contain the appt info since one pt would have
many appts.

4. I also wanted to have a switchboard to open upon opening the database
where the user could run the same query. In other words, run the query from
the switchboard first (because no form would appear at this point) and then
once the form opens with the first search performed, be able to run a second
search with a different MRN from the form instead of going back to the
switchboard.

Can you help me?

Lauri

Lauri,

1. From the looks of your query, when you run it from the query grid, it
asks you to provide a value "Enter MRN". I assume that is the first dialog
box that you don't really mention in your message. Personally, if you
already know the patient ID, I would put a textbox (or a combo box, or a
list box) on my form that allows me to select the patient, then, in my
query, I would refer to that control. With this type of interface, the
WHERE clause would look something like:

WHERE tblPatients.MRN Like Forms!yourFormName.txt_MRN

2. Which brings me to my second point. Using LIKE in the query doesn't
make a lot of sense, unless you plan on putting in a partial MRN, in which
case, you really need to use an asterisk in front, in back, or both of the
value you are passing the query. The way you currently have it worded, it
must equal the value you enter when prompted for the MRN. The disadvantage
is the Like is significantly slower that = when it is evaluated. So, I
would rework the WHERE clause to:

WHERE tblPatients.MRN = Forms!yourFormName.txt_MRN

Note: This assumes that the MRN field is numeric.

3. The reason you are getting the second dialog is that the JET database
engine does not recognizt tblAppts.ApptDate as a valid field, so it assumes
it is a parameter you are trying to evaluate. Check to make sure that the
ApptDate field actually exists in tblAppts. Then take another look at the
query and make sure that you don't have a space in the table or field name
in the query.

4. Assuming that the query you posted is saved as qryPtSearch, I'm
confused. frmPatient should already have a RecordSource, which is either a
query or a table. I'm not sure what you are trying to accomplish with the
"qryPtSearch" parameter you have passed the form. If I were actually doing
this, I would get rid of that, and change the OpenForm statement to look
something like:

docmd.OpenForm "frmPatient",,,"[MRN] = " & me.txt_MRN

Hope this helps.

SELECT tblPatient.PtName, tblPatient.MRN, tblPatient.DOB,
tblAppts.ApptDate,
tblAppts.ApptStatus, tblAppts.Provider, tblPatient.Scanned,
tblPatient.BatchNumber
FROM tblPatient INNER JOIN tblAppts ON tblPatient.MRN = tblAppts.MRN
WHERE (((tblPatient.MRN) Like [Enter MRN]))
ORDER BY tblAppts.ApptDate DESC;

Lauri

Can you post the SQL of the query?
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I created a switchboard form and another form both of which have
command
buttons to run a query and opening in form view. The query comes from 2
different tables. When I run the query from the query design view
window,
everything is fine and I get the results in datasheet view. When I try
to run
the query by clicking the command buttons in either form, I get a
second
dialog box asking for a parameter value that I didn't set. The second
dialog
box looks like this: tblAppts.ApptDate. If I ignore the 2nd dialog box
and
hit enter I still get the reults I want; I just don't know why I get
that
second box.
Code for command on switchboard:
Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

Dim stDocName As String

stDocName = "qryPtSearch"
DoCmd.OpenForm "frmPatient", , "qryPtSearch"

Exit_Command27_Click:
Exit Sub

Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click

End Sub

Code for command on other form(they run the same query):
Private Sub Command15_Click()
On Error GoTo Err_Command15_Click

Dim stDocName As String

stDocName = "qryPtSearch"
DoCmd.OpenForm "frmPatient", , "qryPtSearch"

Exit_Command15_Click:
Exit Sub

Err_Command15_Click:
MsgBox Err.Description
Resume Exit_Command15_Click

End Sub


The form does contain a main form and a subform. It is the subform that
contains all the patient appts.


My 2 tables are tblAppts and tblPatient with the Medical
Record Number (MRN) being the parameter to input to return the results.


Any assistance would be appreciated!!

Lauri S.