Using a Form to supply parameters to a query

  • Thread starter Thread starter Clef Dweller
  • Start date Start date
C

Clef Dweller

I'm trying to use a form to supply parameters to a query. I've created the
form, which appears to be working just fine. In the 'Criteria' of the query,
I've used the following syntax to open the form:
[Forms]![Name of form]![Field Name]
When I run the query, I get a parameter pop-up box that says:
Forms!Name of form!Field Name

Help! What am I doing wrong?
 
If the form entry object is a textbox bound to a field then you use the name
of the textbox -- not the field name it is bound to.
 
Is the form open when you run the query? It must be in order for this to
work. Otherwise, you'll get the pop-up you describe.

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.
 
Hi - Could you help with a similar situation that I'm having?
The criteria in my query is [Form]![frmMainBilling]![Text46].
The Control Source of Text46 is ="#" & Format([cboCycleToBill],"mm/dd/yyyy")
& "#" .
cboCycleToBill allows the user to select a month and year ie. September 2009.
On the form, Text46 displays as expected #09/01/2008#
The form is open when the query runs.
The query is prompting me for [Form]![frmMainBilling]![Text46]

What am I doing wrong?
--
TIA
Chuck M.


KARL DEWEY said:
If the form entry object is a textbox bound to a field then you use the name
of the textbox -- not the field name it is bound to.
--
KARL DEWEY
Build a little - Test a little


Clef Dweller said:
I'm trying to use a form to supply parameters to a query. I've created the
form, which appears to be working just fine. In the 'Criteria' of the query,
I've used the following syntax to open the form:
[Forms]![Name of form]![Field Name]
When I run the query, I get a parameter pop-up box that says:
Forms!Name of form!Field Name

Help! What am I doing wrong?
Click to expand...
Click to expand...
 
Try:

[Forms]![frmMainBilling]![Text46]

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



Chuck M said:
Hi - Could you help with a similar situation that I'm having?
The criteria in my query is [Form]![frmMainBilling]![Text46].
The Control Source of Text46 is ="#" & Format([cboCycleToBill],"mm/dd/yyyy")
& "#" .
cboCycleToBill allows the user to select a month and year ie. September 2009.
On the form, Text46 displays as expected #09/01/2008#
The form is open when the query runs.
The query is prompting me for [Form]![frmMainBilling]![Text46]

What am I doing wrong?
--
TIA
Chuck M.


KARL DEWEY said:
If the form entry object is a textbox bound to a field then you use the name
of the textbox -- not the field name it is bound to.
--
KARL DEWEY
Build a little - Test a little


Clef Dweller said:
I'm trying to use a form to supply parameters to a query. I've created the
form, which appears to be working just fine. In the 'Criteria' of the query,
I've used the following syntax to open the form:
[Forms]![Name of form]![Field Name]
When I run the query, I get a parameter pop-up box that says:
Forms!Name of form!Field Name

Help! What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Hi. Clef Dweller again. I tried renaming to the combo box and that didn't
work. The form itself works, but the query will not open the form. The form
is meant to supply the parameter for the first field in the query.

Dale Fye said:
Is the form open when you run the query? It must be in order for this to
work. Otherwise, you'll get the pop-up you describe.

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



Clef Dweller said:
I'm trying to use a form to supply parameters to a query. I've created the
form, which appears to be working just fine. In the 'Criteria' of the query,
I've used the following syntax to open the form:
[Forms]![Name of form]![Field Name]
When I run the query, I get a parameter pop-up box that says:
Forms!Name of form!Field Name

Help! What am I doing wrong?
Click to expand...
Click to expand...
 
Dale - Thanks for the quick response. Changing Form to Forms did the trick.
But now I get a Data Type Mismatch in Criteria Expression. The Format
property of Text46 was blank when I got the error. I changed it to Short
Date. I get the same mismatch error. Any thoughts?
--
TIA
Chuck M.


Dale Fye said:
Try:

[Forms]![frmMainBilling]![Text46]

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



Chuck M said:
Hi - Could you help with a similar situation that I'm having?
The criteria in my query is [Form]![frmMainBilling]![Text46].
The Control Source of Text46 is ="#" & Format([cboCycleToBill],"mm/dd/yyyy")
& "#" .
cboCycleToBill allows the user to select a month and year ie. September 2009.
On the form, Text46 displays as expected #09/01/2008#
The form is open when the query runs.
The query is prompting me for [Form]![frmMainBilling]![Text46]

What am I doing wrong?
--
TIA
Chuck M.


KARL DEWEY said:
If the form entry object is a textbox bound to a field then you use the name
of the textbox -- not the field name it is bound to.
--
KARL DEWEY
Build a little - Test a little


:

I'm trying to use a form to supply parameters to a query. I've created the
form, which appears to be working just fine. In the 'Criteria' of the query,
I've used the following syntax to open the form:
[Forms]![Name of form]![Field Name]
When I run the query, I get a parameter pop-up box that says:
Forms!Name of form!Field Name

Help! What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
You are correct. Queries will not open forms. You must run the query from
the form, usually done via a command button or in the AfterUpdate event of a
control.

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



Clef Dweller said:
Hi. Clef Dweller again. I tried renaming to the combo box and that didn't
work. The form itself works, but the query will not open the form. The form
is meant to supply the parameter for the first field in the query.

Dale Fye said:
Is the form open when you run the query? It must be in order for this to
work. Otherwise, you'll get the pop-up you describe.

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



Clef Dweller said:
I'm trying to use a form to supply parameters to a query. I've created the
form, which appears to be working just fine. In the 'Criteria' of the query,
I've used the following syntax to open the form:
[Forms]![Name of form]![Field Name]
When I run the query, I get a parameter pop-up box that says:
Forms!Name of form!Field Name

Help! What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Post the SQL of your query.

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



Chuck M said:
Dale - Thanks for the quick response. Changing Form to Forms did the trick.
But now I get a Data Type Mismatch in Criteria Expression. The Format
property of Text46 was blank when I got the error. I changed it to Short
Date. I get the same mismatch error. Any thoughts?
--
TIA
Chuck M.


Dale Fye said:
Try:

[Forms]![frmMainBilling]![Text46]

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



Chuck M said:
Hi - Could you help with a similar situation that I'm having?
The criteria in my query is [Form]![frmMainBilling]![Text46].
The Control Source of Text46 is ="#" & Format([cboCycleToBill],"mm/dd/yyyy")
& "#" .
cboCycleToBill allows the user to select a month and year ie. September 2009.
On the form, Text46 displays as expected #09/01/2008#
The form is open when the query runs.
The query is prompting me for [Form]![frmMainBilling]![Text46]

What am I doing wrong?
--
TIA
Chuck M.


:

If the form entry object is a textbox bound to a field then you use the name
of the textbox -- not the field name it is bound to.
--
KARL DEWEY
Build a little - Test a little


:

I'm trying to use a form to supply parameters to a query. I've created the
form, which appears to be working just fine. In the 'Criteria' of the query,
I've used the following syntax to open the form:
[Forms]![Name of form]![Field Name]
When I run the query, I get a parameter pop-up box that says:
Forms!Name of form!Field Name

Help! What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Here it is:

SELECT qryGenMthlyInv_1.CustId, qryGenMthlyInv_1.CustStatus,
qryGenMthlyInv_1.CustType, qryGenMthlyInv_1.CustName,
qryGenMthlyInv_1.RevenueTypeId, qryGenMthlyInv_1.RevTypeDesc,
qryGenMthlyInv_1.[Billing Cycle], qryGenMthlyInv_1.NextCycleStartsDt,
qryGenMthlyInv_1.BillingAmt INTO TmpGenMthlyInv_2
FROM qryGenMthlyInv_1
WHERE
(((qryGenMthlyInv_1.NextCycleStartsDt)=[Forms]![frmMainBilling]![Text46]));
--
TIA
Chuck M.


Dale Fye said:
Post the SQL of your query.

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



Chuck M said:
Dale - Thanks for the quick response. Changing Form to Forms did the trick.
But now I get a Data Type Mismatch in Criteria Expression. The Format
property of Text46 was blank when I got the error. I changed it to Short
Date. I get the same mismatch error. Any thoughts?
--
TIA
Chuck M.


Dale Fye said:
Try:

[Forms]![frmMainBilling]![Text46]

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



:

Hi - Could you help with a similar situation that I'm having?
The criteria in my query is [Form]![frmMainBilling]![Text46].
The Control Source of Text46 is ="#" & Format([cboCycleToBill],"mm/dd/yyyy")
& "#" .
cboCycleToBill allows the user to select a month and year ie. September 2009.
On the form, Text46 displays as expected #09/01/2008#
The form is open when the query runs.
The query is prompting me for [Form]![frmMainBilling]![Text46]

What am I doing wrong?
--
TIA
Chuck M.


:

If the form entry object is a textbox bound to a field then you use the name
of the textbox -- not the field name it is bound to.
--
KARL DEWEY
Build a little - Test a little


:

I'm trying to use a form to supply parameters to a query. I've created the
form, which appears to be working just fine. In the 'Criteria' of the query,
I've used the following syntax to open the form:
[Forms]![Name of form]![Field Name]
When I run the query, I get a parameter pop-up box that says:
Forms!Name of form!Field Name

Help! What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Dale - Don't know if this is helpful to you or not - but this is the
qryGenMthlyInv_1 that the failing query I sent earlier is pulling data from.

SELECT tblCustomers.CustId, tblCustomers.CustStatus, tblCustomers.CustType,
tblCustomers.CustName, tblRevenueType.RevenueTypeId,
tblRevenueType.RevTypeDesc, tblCustBillingDetail.[Billing Cycle],
tblCustBillingDetail.NextCycleStartsDt, tblCustBillingDetail.BillingAmt
FROM (tblCustomers INNER JOIN tblCustBillingDetail ON tblCustomers.CustId =
tblCustBillingDetail.CustId) INNER JOIN tblRevenueType ON
tblCustBillingDetail.RevenueTypeId = tblRevenueType.RevenueTypeId
ORDER BY tblCustomers.CustId, tblRevenueType.RevenueTypeId;

Thanks again!
--

Chuck M.


Dale Fye said:
Post the SQL of your query.

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



Chuck M said:
Dale - Thanks for the quick response. Changing Form to Forms did the trick.
But now I get a Data Type Mismatch in Criteria Expression. The Format
property of Text46 was blank when I got the error. I changed it to Short
Date. I get the same mismatch error. Any thoughts?
--
TIA
Chuck M.


Dale Fye said:
Try:

[Forms]![frmMainBilling]![Text46]

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



:

Hi - Could you help with a similar situation that I'm having?
The criteria in my query is [Form]![frmMainBilling]![Text46].
The Control Source of Text46 is ="#" & Format([cboCycleToBill],"mm/dd/yyyy")
& "#" .
cboCycleToBill allows the user to select a month and year ie. September 2009.
On the form, Text46 displays as expected #09/01/2008#
The form is open when the query runs.
The query is prompting me for [Form]![frmMainBilling]![Text46]

What am I doing wrong?
--
TIA
Chuck M.


:

If the form entry object is a textbox bound to a field then you use the name
of the textbox -- not the field name it is bound to.
--
KARL DEWEY
Build a little - Test a little


:

I'm trying to use a form to supply parameters to a query. I've created the
form, which appears to be working just fine. In the 'Criteria' of the query,
I've used the following syntax to open the form:
[Forms]![Name of form]![Field Name]
When I run the query, I get a parameter pop-up box that says:
Forms!Name of form!Field Name

Help! What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
TRY:

WHERE NextCycleStartsDt=CDATE([Forms]![frmMainBilling]![Text46])

It may be that JET is having difficulty with determining the data type of
Text46.

Another way to do this would be to actually add the reference to the textbox
as a parameter.

Parameters [Forms]![frmMainBilling]![Text46] DateTime;
SELECT CustId, CustStatus, CustType, CustName,
RevenueTypeId, RevTypeDesc, [Billing Cycle],
NextCycleStartsDt, BillingAmt
INTO TmpGenMthlyInv_2
FROM qryGenMthlyInv_1
WHERE NextCycleStartsDt=[Forms]![frmMainBilling]![Text46]


--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



Chuck M said:
Here it is:

SELECT qryGenMthlyInv_1.CustId, qryGenMthlyInv_1.CustStatus,
qryGenMthlyInv_1.CustType, qryGenMthlyInv_1.CustName,
qryGenMthlyInv_1.RevenueTypeId, qryGenMthlyInv_1.RevTypeDesc,
qryGenMthlyInv_1.[Billing Cycle], qryGenMthlyInv_1.NextCycleStartsDt,
qryGenMthlyInv_1.BillingAmt INTO TmpGenMthlyInv_2
FROM qryGenMthlyInv_1
WHERE
(((qryGenMthlyInv_1.NextCycleStartsDt)=[Forms]![frmMainBilling]![Text46]));
--
TIA
Chuck M.


Dale Fye said:
Post the SQL of your query.

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



Chuck M said:
Dale - Thanks for the quick response. Changing Form to Forms did the trick.
But now I get a Data Type Mismatch in Criteria Expression. The Format
property of Text46 was blank when I got the error. I changed it to Short
Date. I get the same mismatch error. Any thoughts?
--
TIA
Chuck M.


:

Try:

[Forms]![frmMainBilling]![Text46]

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



:

Hi - Could you help with a similar situation that I'm having?
The criteria in my query is [Form]![frmMainBilling]![Text46].
The Control Source of Text46 is ="#" & Format([cboCycleToBill],"mm/dd/yyyy")
& "#" .
cboCycleToBill allows the user to select a month and year ie. September 2009.
On the form, Text46 displays as expected #09/01/2008#
The form is open when the query runs.
The query is prompting me for [Form]![frmMainBilling]![Text46]

What am I doing wrong?
--
TIA
Chuck M.


:

If the form entry object is a textbox bound to a field then you use the name
of the textbox -- not the field name it is bound to.
--
KARL DEWEY
Build a little - Test a little


:

I'm trying to use a form to supply parameters to a query. I've created the
form, which appears to be working just fine. In the 'Criteria' of the query,
I've used the following syntax to open the form:
[Forms]![Name of form]![Field Name]
When I run the query, I get a parameter pop-up box that says:
Forms!Name of form!Field Name

Help! What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Dale - you are the man! Both of your suggestions work just fine.

Thanks for sticking with me through this problem.

Have a great day!

--

Chuck M.


Dale Fye said:
TRY:

WHERE NextCycleStartsDt=CDATE([Forms]![frmMainBilling]![Text46])

It may be that JET is having difficulty with determining the data type of
Text46.

Another way to do this would be to actually add the reference to the textbox
as a parameter.

Parameters [Forms]![frmMainBilling]![Text46] DateTime;
SELECT CustId, CustStatus, CustType, CustName,
RevenueTypeId, RevTypeDesc, [Billing Cycle],
NextCycleStartsDt, BillingAmt
INTO TmpGenMthlyInv_2
FROM qryGenMthlyInv_1
WHERE NextCycleStartsDt=[Forms]![frmMainBilling]![Text46]


--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



Chuck M said:
Here it is:

SELECT qryGenMthlyInv_1.CustId, qryGenMthlyInv_1.CustStatus,
qryGenMthlyInv_1.CustType, qryGenMthlyInv_1.CustName,
qryGenMthlyInv_1.RevenueTypeId, qryGenMthlyInv_1.RevTypeDesc,
qryGenMthlyInv_1.[Billing Cycle], qryGenMthlyInv_1.NextCycleStartsDt,
qryGenMthlyInv_1.BillingAmt INTO TmpGenMthlyInv_2
FROM qryGenMthlyInv_1
WHERE
(((qryGenMthlyInv_1.NextCycleStartsDt)=[Forms]![frmMainBilling]![Text46]));
--
TIA
Chuck M.


Dale Fye said:
Post the SQL of your query.

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



:

Dale - Thanks for the quick response. Changing Form to Forms did the trick.
But now I get a Data Type Mismatch in Criteria Expression. The Format
property of Text46 was blank when I got the error. I changed it to Short
Date. I get the same mismatch error. Any thoughts?
--
TIA
Chuck M.


:

Try:

[Forms]![frmMainBilling]![Text46]

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



:

Hi - Could you help with a similar situation that I'm having?
The criteria in my query is [Form]![frmMainBilling]![Text46].
The Control Source of Text46 is ="#" & Format([cboCycleToBill],"mm/dd/yyyy")
& "#" .
cboCycleToBill allows the user to select a month and year ie. September 2009.
On the form, Text46 displays as expected #09/01/2008#
The form is open when the query runs.
The query is prompting me for [Form]![frmMainBilling]![Text46]

What am I doing wrong?
--
TIA
Chuck M.


:

If the form entry object is a textbox bound to a field then you use the name
of the textbox -- not the field name it is bound to.
--
KARL DEWEY
Build a little - Test a little


:

I'm trying to use a form to supply parameters to a query. I've created the
form, which appears to be working just fine. In the 'Criteria' of the query,
I've used the following syntax to open the form:
[Forms]![Name of form]![Field Name]
When I run the query, I get a parameter pop-up box that says:
Forms!Name of form!Field Name

Help! What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Dale. I'm opening the form first and that solved the problem

Dale Fye said:
Is the form open when you run the query? It must be in order for this to
work. Otherwise, you'll get the pop-up you describe.

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



Clef Dweller said:
I'm trying to use a form to supply parameters to a query. I've created the
form, which appears to be working just fine. In the 'Criteria' of the query,
I've used the following syntax to open the form:
[Forms]![Name of form]![Field Name]
When I run the query, I get a parameter pop-up box that says:
Forms!Name of form!Field Name

Help! What am I doing wrong?
Click to expand...
Click to expand...
 
Back
Top