Access 2010 SQL Statement with tables and query

[MCRetro]

I am using Access 2010.

I have a table that I am using to pull my data from other tables and a query.
My table is called tblMyData.

One of the field names is level1. This field points to another table, and gives the user the choices for states (examples California, Texas, Maine).

Another field name is level2. This field points to another table and give the user the choice for type of customer (examples Business, Consumer)
The field name level3 points to a query. The query, qryFinalChoice matches up the choices based on level1 and level2.

For example, the user can pick California for level1, business in level2 and California Widgets or Los Angeles Clothing store in level3 (plus about 20 other choices).

If the user picks Texas for level1, business in level2, business in level2 and Houston rugs, or Texas style restaurant in level3 (or about 15 other choices).

I am recording 1 for California on level1, 2 for Texas and 3 for Maine in level1.

I am recording 1 for business and 2 for consumer on level2.

The query qryFinalChoice has all the combinations for state, business or consumer, and lastly business name or consumer name.

qryFinalChoice has line1 to match up the choices for level1 in my table.

qryFinalChoice has line2 to match up the choices for level2 in my table.

qryFinalChoice has line3 to match up the choices for level3 in my table.

I do not want any of the Texas business names appearing when the user picks California, or vice versa.

My SQL in my tblMyData tab for level3 looks like this:
Select line1 from qryFinalChoice where line1=1;

I am able to get all the line items where California is a selection.
How do I change my SQL to pull all the line1 choices where I have selected from level1, and all the line2 choices where I have selected from level2 automatically based on my pulldowns? I know I am close, please let me know how to do this.

 

[Privateer]

MCRetro, I don't know where you are doing all this, building a query or on a form, so I can't give you an exact answer, but it looks like you are referring to cascading combos. They work on a form where you have multiple combo boxes and the selection from combo one is used as the criteria for combo two, and both are used for the criteria for combo three.

So, how does that work? Well with a little behind the scenes code of course. Each combo has a row source to give the user something to select. The first combo, states, has SQL to get the records from the tblStates, which has a primary key next to each one. So the user picks California, but the value of the combo box is 31. Assuming you can have both businesses and consumers in all states, you don't need to filter this one by the state so either Business or Consumer can be selected. These options should also be in a table with a primary key next to each. So business is 1, consumer is 2. The third table listing the businesses should have, as foreign keys, a field called StatesID and BusinessTypeID. And any business in California should have a 31 in the StatesID field and in the BusinessTypeID field a 1 or 2. Ok, that's the setup, now for the good part.

The big picture is you want to restrict the businesses displayed in the third combo box with a criteria string based on the first two. So take that query you have and remove any criteria, so it displays everything. Then copy the SQL to the "GotFocus" event of the third combo. Then the criteria is "StatesID = 31 and BusinessTypeID=1".

It looks something like this:

private sub BusinessOption_GotFocus

dim Criteria as string
dim strSQL as string

if isnull(me.stateoption.value) = false then
Criteria = "([StateID]= & me.stateoption.value & ") AND "
end if
if isnull(me.businesstypeoption.value) = false then
Criteria = criteria + "([BusinessTypeID]= & me.businesstypeoption.value & ") AND "
end if

strSQL = "SELECT BusinessID, BusinessName FROM tblBusiness"

IF len(criteria) > 1 then
Criteria = Left(Criteria,len(Criteria)-5) 'The space, AND space at the end is five charactures.
strSQL = strSQL & " WHERE (" & Criteria & ");"
ELSE
strSQL = strSQL & ";"
end if
me.businessoption.rowsource = strSQL
end sub

That's the general idea. Good luck
Privateer