Query Directly From A Form

[Kevin]

I'm working on a database that started out as a small project and has
turned into something massive.

I have a form that has several queries hard coded to buttons on the
form which I run with macros. The database has gotten very complex and
running queries with all these buttons just isn't going to cut it
anymore.

I would like to be able to have the user enter data on my existing form
and run a query based on what was entered. At the present time when I
run my query no values are returned, my query does not find any
records. Here is a synopsis of what I'm doing.

Table "t_purchase" contains fields "Type", "Date",
"Quantity" and "Height".

My form "f_test" has all these fields listed where the record
source points back to the table "t_purchase" and I have the
respected field names defined in the control source on the form by
name.

My query "q_test_query_form_only" has the following in the Criteria
field, "=[forms]![f_test]![Type]". Field is "Type" and Table is
"t_purchase".

What am I doing wrong?

I have also tried the include "contains" in my Criteria field with
the same results. If someone can point me in the right direction I
would appreciate it.

 

[Jeff L]

Remove the quotes. You are telling your query to match the string
"=[forms]![f_test]![Type]" when what you want is the value of
[forms]![f_test]![Type].

Hope that helps!

 

[Douglas J. Steele]

What happens when you try running your query?

What's the actual SQL of your query? To see this, open the query in Design
view, then choose SQL View from the View menu. Copy what's in that window,
and paste it back here.


One potential problem is your choice of names. Both Type and Date are
reserved words, and using them for your own purposes can lead to problems.
For more information about reserved words, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

 

[Kevin]

I placed [forms]![f_test]![Type] in the Criteria field and I get the
same results. No records are returned.

Thanks for trying!

 

[Kevin]

The interesting this is if I run the query using the data from the
combo box on the form and click the button, no records are returned.
When I run the query alone without using the form I get a Enter
Parameter Value dialog box with Forms!f_test!Type listed. If I manually
enter Criteria and the query returns any records I have in the
database.


SQL:
SELECT t_purchase.Date, t_purchase.Type, t_purchase.Height,
t_purchase.Quantity
FROM t_purchase
WHERE (((t_purchase.Type)=[forms]![f_test]![Type]));

What happens when you try running your query?

What's the actual SQL of your query? To see this, open the query in Design
view, then choose SQL View from the View menu. Copy what's in that window,
and paste it back here.


One potential problem is your choice of names. Both Type and Date are
reserved words, and using them for your own purposes can lead to problems.
For more information about reserved words, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm working on a database that started out as a small project and has
turned into something massive.

I have a form that has several queries hard coded to buttons on the
form which I run with macros. The database has gotten very complex and
running queries with all these buttons just isn't going to cut it
anymore.

I would like to be able to have the user enter data on my existing form
and run a query based on what was entered. At the present time when I
run my query no values are returned, my query does not find any
records. Here is a synopsis of what I'm doing.

Table "t_purchase" contains fields "Type", "Date",
"Quantity" and "Height".

My form "f_test" has all these fields listed where the record
source points back to the table "t_purchase" and I have the
respected field names defined in the control source on the form by
name.

My query "q_test_query_form_only" has the following in the Criteria
field, "=[forms]![f_test]![Type]". Field is "Type" and Table is
"t_purchase".

What am I doing wrong?

I have also tried the include "contains" in my Criteria field with
the same results. If someone can point me in the right direction I
would appreciate it.