Query 1 or 2 comboboxes

[StumblingDrunk]

Hi

I am new to the forum, but it looks like you may be able to give me some much needed guidance

A summary of my issue:

I have a form with two combo boxes. The first is the Country and the second is the County/State. I want to create a query that will look at the first combo box (country) to populate a form, and then use the second combo box, if it has been filled in.

Not sure if this makes much sense but for example:

One person just wants all the results for England on the form, whilst another person may just want the results from Essex in England.

I have tried multiple thngs like IIf isnull etc, but I cannot get it quite right. It either populates everything for that country everytime, or only works if the country and county/state are populated.

I have had a look on multiple forums and posts, but have had no luck so far (although I am sure this is relatively straight forward).

Any help will be greatly appreciated

 

[pbaldy]

This type of thing?

http://www.baldyweb.com/CascadingCombo.htm

 

[AC5FF]

Paul
I'm definatly going home and bookmarking that website of yours! Looks like you have a lot of info there!
may have to put in a request to mgmt here to 'unblock' this URL

 

[pbaldy]

I'm trying to put a lot of useful info there, and unless GoDaddy gets infected, I guarantee no spam or virus issues.

 

[Nina Tryggvason]

I have a follow up question

all the examples I've seen of these QBF are pointing to fields where the data is typed


I've been working on creating one, where the field "City" is filled by selecting from a list from 150 drawn from another table in the database

even a simple straightforward parameter query is returning zero records

does it make a different how the field is filled to how it handles in the query?

 

[pbaldy]

Is the list multiselect?

 

[StumblingDrunk]

Thanks for that, it got my combo boxes working a treat.

The issue I am now having is the query to get results using these selections and populating my form. It is a simple matter when I have both city and state selected, but if I just select a city, and not a state, it will produce nothing in the form, when it should popluate everything for that city.

 

[pbaldy]

Sounds like this would help:

http://www.mvps.org/access/queries/qry0001.htm

 

[Nina Tryggvason]

Sounds like this would help:

http://www.mvps.org/access/queries/qry0001.htm

no, but thank you

I'll explore that site more and see if I can find an answer

I don't want to return all 370 records, I only want the records that match the city selection

mostly I want to know if Access treats fields where the information is typed differently than if the information is selected as a drop down from another table

 

[pbaldy]

Did you try it (on both fields)?

 

[Nina Tryggvason]

Did you try it (on both fields)?

when I point the form's combo box to the agreement table, the drop down list shows only the ID number from the table with the list of sites

when I point the combo box to the site table - and used the code to tell the query to use the combo box data as the query parameter,

they both return zero records



when I do it as a plain parameter query (no form), and I enter the site, it returns all 370 records, replace all site entries with the one in the parametre



and yet,when I do a query to select records that match on a field where the information was typed, not selected from the list sourced from another table, it works just fine



I am totally new to Access (but am an advanced user of Excel), so either I simply don't know enough enough - mostly likely -

or I've found a truely obscure issue

also I am stuck with Access 2000, and it will be years before my office upgrades

 

[pbaldy]

Can you post the db? Offhand, it sounds like you just need to put the criteria on the ID field in the query instead of the site name.

 

[Nina Tryggvason]

Can you post the db? Offhand, it sounds like you just need to put the criteria on the ID field in the query instead of the site name.

the ID field in the site table is a meaningless autonumber



the file size is 2 GB - there's 3 embedded MS Word docs in each record in the table for detailed rent, sublease and narrative history



the query code is:

SELECT AIMS.[Customer Legal Name], AIMS.[Rent - Annual], AIMS.[Site Codes]
FROM AIMS
WHERE (((AIMS.[Site Codes])=[Forms]![bysitequeries]![sitelist]));



AIMS is the list of agreements what I want is to select a site and get a selected list of all agreements at the site, so I know how much rent is generated at the site




the form (screenshot attached) has a combo box that draws from the SITE table in the same database that lists the sites


so, what I want is to be able to select a site

and then run either of the 2 queries from that selected data list

one to show me just the records for the site that do a rent report

the other so show me for one site, all the bring forward dates for various actions within a range

 

[pbaldy]

Is the value in the bound column of the combo the same as contained in Site Codes?

 

[Nina Tryggvason]

Is the value in the bound column of the combo the same as contained in Site Codes?

the combo box on the form points to the Site table and the field with the city (field 3)

when I pointed to the AIMS.Sit Code - it only picked up the index number (field 1 from SITE)

 

[Nina Tryggvason]

I made a small version of the database

I need to know how to code it so that the user can pick the site from the form combo box, and click the button to get a list of the records with just that one site

and not have it return all records and change the site to the selected site

 

[pbaldy]

For starters, the criteria is on the Annual Rent field, not the site code. Second, the name of the combo isn't SiteCode, it's Combo0. Third, as suspected, the bound column of the combo is the ID field, but the criteria was on the text field. I added the Site table to the query and put the criteria on the ID field, and it works as expected.

 

[Nina Tryggvason]

For starters, the criteria is on the Annual Rent field, not the site code. Second, the name of the combo isn't SiteCode, it's Combo0. Third, as suspected, the bound column of the combo is the ID field, but the criteria was on the text field. I added the Site table to the query and put the criteria on the ID field, and it works as expected.

the uploaded one was a quickie sample version that I through together this morning to show what I wanted to accomplish, because I don't think I know enough about Access to explain it in a meanful way to experienced users, not my actual database -

and I didn't take the time to name everything because it's not the database


I have no idea what the last sentence means to replicate that in my actual database

 

[pbaldy]

Here's the SQL of the query I have working:

SELECT AIMS.SiteCode, AIMS.Customer, AIMS.[Annual Rent], SITE.ID
FROM SITE INNER JOIN AIMS ON SITE.ID = AIMS.SiteCode
WHERE (((SITE.ID)=[Forms]![frmSiteQueries]![combo0]));

 

[Nina Tryggvason]

thank you for your help

I was on the verge of giving up and just running a regualr query on all records and then copying and pasting the ones I wanted into MS word for a report