Solved

Where Clause in Access Table/Query select statement in combo box

Posted on 2007-12-04
3
378 Views
Last Modified: 2013-11-28
I have a search combo box on a form to pull a record from a table. I would like to select certain records  to pull into the combo box based on data (year and location) in the table.

1) I have a form where the user puts in the year and location.
2) then the user clicks on a button that opens a form. The year and location are transferring from the form in #1 to this form.
3) I have a combo box on this second form:
SELECT [tr_hip_no], [tr_name1], [tr_name2], [tr_na_num] FROM transactions WHERE sale_year=me.sale_year And sale_location=me.sale_location ORDER BY [tr_name1];

When I run the form ...it prompts for me.sale_year and me.sale_location.

I also tried: frmtransactions is the form this combox is located:
WHERE sale_year=[frmtransactions].form.sale_year And sale_location=[frmtransactions].form.sale_location  

Thanks...

Proffittware
0
Comment
Question by:proffittware
3 Comments
 
LVL 14

Expert Comment

by:ldunscombe
ID: 20406945
In your sql statement you need to refer to the "controls" on your first form. Ie the form where the user enters the year and location and this selection form must remain open when you open the second form.

Leigh
0
 
LVL 15

Accepted Solution

by:
JimFive earned 500 total points
ID: 20412099
WHERE sale_year=[frmtransactions].form.sale_year And sale_location=[frmtransactions].form.sale_location  

Should be
WHERE sale_year = Forms!frmtransactions!sale_year AND sale_location = Forms!frmtransactions!sale_location

--
JimFive
0
 

Author Comment

by:proffittware
ID: 20413031
Thanks for both of you responding.

I found the examples helpful JimFive...
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question