Query with parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all.

I have a query that prompts the user to enter the payer they are requesting
information on and I was wondering if it is possible to have a drop down list
instead of the prompt? If the end user does not type the name exactly right
they do not pull up any information and from the # of responses I have
received-- I must come up an alternative to typing the name in. Any ideas?

Thanks.
 
You need to create a form with a combo box. Then in your query instead of
having a criteria of [some prompt] you will enter
Forms![formName]![ComboBoxName].
You can then set the afterupdate event of the combo box to open your query.
I would suggest instead of opening a query, create a report on the query and
have it open instead. If you use a query the operator can change data, where
a report will just let them see the information and you can better format a
report.
 
You'll need to create an unbound dialogue form with a combo box of the
payers on it and a button to open the query, or a form or report based on
the query. The parameter in the query would then reference the combo box on
the form, e.g.

SELECT *
FROM MyTable
WHERE payer = Forms!frmPayerDlg!cboPayer;

You can get a report or form based on the query to open the dialogue form
automatically by putting code in its Open event procedure:

Const FORMNOTOPEN = 2450
Dim frm As Form

On Error Resume Next
Set frm = Forms! frmPayerDlg
If Err = FORMNOTOPEN Then
DoCmd.OpenForm " frmPayerDlg"
Cancel = True
Else
If Err <> 0 Then
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End If
End If

When code like the above is used in an Open event procedure, if you are
opening a form or report based on the query with code from elsewhere in the
application you need to handle the error which occurs when the opening of the
form/report is cancelled e.g.

Const OPENCANCELLED = 2501

On Error Resume Next
DoCmd.OpenReport "MyReport"
If Err = OPENCANCELLED Then
' anticipated error so do nothing
Else
If Err <> 0 Then
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End If
End If

Ken Sheridan
Stafford, England
 
Back
Top