VBA-Query Loop - Unable to convert to Form reference

[AKATemp]

All,
I've tried searching through the site and now, I am unable to find an answer to this question. If someone could point me to the right direction- I'd appreciate it.

Background: I have a form with 10 combo-boxes. Once user selects a value in CB1- CB2 becomes visible and active.
I am trying to run a dynamic query- where the selection of the combo-box is used to select a column from my table called "dbo_animals"
To elaborate: CB1 contains the following values

 elephant, giraffe, bufffalo, tiger, lion

Once the user selects elephant in CB1, CB2 becomes active and I select tiger next. So the query becomes like. The process can go on and the user can select up to 10 animals

**SELECT elephant, tiger FROM dbo_animals**

Problem: I am able to create the query with string manipulation- Unfortunately due to the way the loop through controls is set up- The query is unable to convert the text into a reference
If I hard code it as

tempquery = "SELECT [Form]![Animal Finder]![CB1] FROM dbo_animals" MsgBox(tempquery)

it looks like

SELECT Elephant from dbo_animals

This is how I want. But, since i am looping through CB controls, I have it set up as

tempquery = "SELECT" & " [Form]![Animal Finder]![CB" & i & "] FROM dbo_animals"

and this shows up as

 SELECT [Form]![Animal Finder]![CB1] FROM dbo_animals

Thereby giving me an error saying that the reference is not valid and asking me for a parameter value.

This makes sense, since it is unable to evaluate the text as a reference.

Question: How can I fix this? Or how do I correct the text into a reference? Or How do I build a query with adjusting columns based off selections from a combo-box and loop through the comboboxes in the form while auto-updating the query?

 

[CJ_London]

you are missing an ampersand, a space and double quotation. I've also added in some square brackets in case one of your choices is [White Rhino]
try.

tempquery =[COLOR=red] "SELECT ["[/COLOR] & " [Form]![Animal Finder]![CB" & i] [COLOR=red]& "] & "[/COLOR] FROM dbo_animals"

However this does seem a strange table design - it implies you have a number of columns all named after animals, is this correct?

With regards your loop, be aware of null values in your comboboxes, if nothing is selected it will be null so you might want to consider this (also note that since you are building this query in the form, you use me rather than the forms collection:

tempquery="SELECT "
 
for i=1 to 10
    if nz(me.cb(i))<>"" then tempquery =tempquery & "[" & me.cb(i) & "], "
Next i
 
if len(tempquery)>7 then 
    tempquery=left(tempquery,len(tempquery)-2) & " FROM dbo_animals"
else
    tempquery=""
end if

 

[AKATemp]

Hi CJ,

Thanks for the prompt reply. I appreciate it.

With regards to table design- Yes, the column names are named after animals. Once the animals are in column, I can put a 'weight' limitation for the animals and get a list of all the zoos with the following animals within a weight criteria. It's weird, I know. But, I didn't want to bombard with 3 pages of code- so I tried to make it as conscise as possible

So after running query I get
SDZoo has Elephants and Tigers (insert weight criteria)
DenZoo has Elephants and Tigers (insert weight parameter)

The loop works and the data in the comboboxes (names of animals) are listed from a different table (dbo_zooproperty), so it does not have a null value

I am very new to access programming, so I don't understand your loop code- Could you comment it?

Unfortunately, fixing syntax is not helping. The syntax query is generating fine, but it is unable to replace the text with reference.

So, I still see it as

SELECT [Form]![Animal Finder]![CB1] FROM dbo_animals

(The very fact that I get CB1 means the loop is running fine, but it's not converting to Elephant)

instead of

SELECT Elephant FROM dbo_animals

 

[CJ_London]

You need to 'assemble' your sql string like this

SELECT " & [Form]![Animal Finder]![CB1]  & " FROM dbo_animals"

But as mentioned in previous post, if you are assembling this in a form you should be using the Me collection, not the Forms collection.

Re documenting the loop

tempquery="SELECT " [COLOR=seagreen]'start of SQL code

[/COLOR]for i=1 to 10[COLOR=seagreen] 'for checkboxes CB1....CB10
[/COLOR]    if nz(me.cb(i))<>"" then tempquery =tempquery & "[" & me.cb(i) & "], " [COLOR=seagreen]'if something has been selected then add it to the SQL code
[/COLOR]Next i

if len(tempquery)>7 then [COLOR=seagreen]'if at least one thing has been selected
[/COLOR]    tempquery=left(tempquery,len(tempquery)-2) & " FROM dbo_animals" '[COLOR=seagreen] so remove the superfluous comma and space and add the name of the table to complete the string
[/COLOR]else
    tempquery=""[COLOR=seagreen] 'reset the tempquery to a zero length string - so it can be tested later if required
[/COLOR]end if

 

[AKATemp]

But as mentioned in previous post, if you are assembling this in a form you should be using the Me collection, not the Forms collection.

THIS HELPED! Thanks!