Solved

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

Posted on 2007-12-04
3
384 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

707 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