Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 23rd, 2007, 02:30 PM
Registered User
 
Join Date: Aug 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default ListBox as Query criteria?

Is it possible to make a list box that includes all of the options available for filtering a query?

I have a database tracking multiple contacts, and my boss wants a report that can pull up info on a single contact and sub contacts. I've set up a query that prompts me to input the name of the contact (using [Enter Contact] on the criteria line inside the query), but it would be easier if I could have Access prompt me with a list box that has all of my contacts in it, from which I can choose the one I want to isolate.

How would I go about doing that?
 
Old August 24th, 2007, 06:31 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

If you just want to select one at a time, it is better to use a combo box. Then once the user has made their selection, create a button to open the report. In the Query designer for the query behind the report, on the contactID line (I am assuming that when you build the contact combo, you will bind to the PK for the contact record) put this:

[Forms]![frmYourFormName].[cboYourComboName]

Then when you open the report, it will check for a value in the combo box to collect the criteria. Make sure you put code on the On No Data event of the report to cancel in case the user does not select a contact.

There are other ways to do this to make sure the user selects something before they click the button to open the report. It involves code to pass the criteria on the button's on click event, and allows you to check for values and prompt the user, etc. Let me know if you need that instead of, or to work with this.

Did that help?

mmcdonal
 
Old August 24th, 2007, 06:33 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I should mention that this will work with a list box. You just need to make sure the bound column is the PK.

mmcdonal
 
Old August 24th, 2007, 02:04 PM
Registered User
 
Join Date: Aug 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm sorry, I'm a bit of Access beginner. Up until I had to build this project, I'd never actually used Access before.

Where do I put the "[Forms]![frmYourFormName].[cboYourComboName]" at within my query? And I have no idea what you mean by bound column or PK...
 
Old August 24th, 2007, 02:20 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

No problem:

Open the query in Query Designer, then in the Criteria: line, add the text that I posted, with the values filled in. Like if your form name is frmContactSelection, and your combo box name is cboContactSelect, then the line would be:

[Forms]![frmContactSelection].[cboContactSelect]

This tells the query to go out to the combo box and get the parameter, which is the currently selected user.

When you create the combo box, you should show meaningful data, like ContactLastName, ContactFirstName, but you also hide the primary key for the Contact table, which might be the column called ContactID. This is a unique identifier for each record, so Access knows which record it wants to get.

So the criteria line should be added on the ContactID column, in this case.

The way that data is captured in the combo box query is:

Column Count: 3
Bound Column: 1
Column Width: 0";1";1"

This shows the first and last names, and hides the Primary Key column. The combo box passes the PK value when asked, unless you specify another column. So if you wanted the last name instead, you would specify cboContactSelect.Column(1) (which is really the second column, but the count starts with 0.

Did that help any?


mmcdonal
 
Old August 24th, 2007, 02:43 PM
Registered User
 
Join Date: Aug 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm not sure what I'm doing incorrectly, I put together the form, but whenever I input it as you said it gives me a prompt. The prompt is just a text box that acts the same way as the text box I designed to come up previously, prompting me to input the information manually, but with the string you gave me as the "title"...

This is what comes up:
http://i153.photobucket.com/albums/s...HJM/result.jpg

Also I noticed in the form that it is showing each name once for every time it shows up in the table. With this there are several contacts related to each of these names, so how would I set it up not to duplicate these names within the form's combo box?
 
Old August 27th, 2007, 06:20 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You need to make sure that in the criteria line for the field that is the bound column from the combo box, you type exactly this:

[Forms]![Decedent].[cboDeceent]

NOT Forms!Decedent.cboDecedent.

The square brackets need to be there.

Did that help?


mmcdonal
 
Old August 27th, 2007, 01:16 PM
Registered User
 
Join Date: Aug 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I did put it in exactly as you had it, with the brackets, and it still came up with that prompt.
 
Old August 28th, 2007, 06:14 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

The issues then are:

1. Is the form "Decedent" open when the query runs?

2. Is the criteria expression in the same field as the bound column in "cboDecedent?"



mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional criteria in query dfuas Access VBA 1 January 21st, 2006 03:46 AM
Conditional criteria in query dfuas BOOK: Beginning Access VBA 0 January 15th, 2006 03:19 PM
Query : listbox column in criteria Freddyfred Access 0 August 12th, 2004 08:00 PM
Criteria in Query lryckman Access 1 June 23rd, 2004 11:11 AM
Query Criteria Clive Astley Access 4 March 25th, 2004 03:27 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.