Problem trying to use a form to supply parameters to a query

  • Thread starter Thread starter Steve M
  • Start date Start date
S

Steve M

Hi
I am using Access 2000, and I cannot get my query to take the parameers from
a form. Is this not possible in Access 2000?

I put this into the criteria [Forms]![frmCustomerCombo]![Customer]

When i run it it the form does not open it just displays the above in the
parameter box.

Any help would be appriciated

Steve
 
Steve

If the query is run with that parameter, but the form is not open, the query
will prompt for a parameter. If the (named) form is open, the query will
look for the named control, and use the value in that control.

The two most common reasons why a query parameterized in this way does not
work are:
1. the form is not open
2. spelling errors (the name of form or control is misspelled).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Steve:

The form must be open and with a value in the control before opening the
query. You can’t just open a query from the database window and get it to
open the form automatically. With a report based on a query you can get it
to do this, however, by putting the following code in the report’s Open event
procedure:

Const FORMNOTOPEN = 2450
Dim frm As Form

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

You’d then need code in the click event of a button on the form to open the
report:

DoCmd.OpenReport, "YourReportName", acViewPreview

To open a form based on the query in the same way you need to delete the
query name from the form’s RecordSource property in its properties sheet and
amend the code for the form’s Open event slightly:

Const FORMNOTOPEN = 2450
Dim frm As Form

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

The frmCustomerCombo form would now need a button to open the form bound to
the query with:

DoCmd.OpenForm "YourFormName"

Ken Sheridan
Stafford, England
 
Hi
I am using Access 2000, and I cannot get my query to take the parameers from
a form. Is this not possible in Access 2000?

I put this into the criteria [Forms]![frmCustomerCombo]![Customer]

When i run it it the form does not open it just displays the above in the
parameter box.

Any help would be appriciated

Steve
Click to expand...

The query is not supposed to open the from.
The form is supposed to be open when the query is run.

Add a command button to the form:
DoCmd.OpenQuery "QueryName"


Then open the form. Enter the parameter in the Customer control.
Click the command button. The Query will open.
 
Back
Top