SQL statement in a form VB (1 Viewer)

[bobbybeers]

I want to create a button that executes an SQL statement in a form. The reason I can't just save a query and run the query from the form, is because the SQL statement needs to use a combobox value from the form. I've learned that
docmd.runsql will not work for a simple select query.....does anyone know what will work??

Thanks

 

[Beetle]

The reason I can't just save a query and run the query from the form, is because the SQL statement needs to use a combobox value from the form.

The fact that the query needs a value form the form as criteria does not mean that you can't use a saved query. What brought you to that conclusion?

If you give us a few more details about what you're doing, I'm sure we can point you in the right direction.

 

[bobbybeers]

I tried your suggestion but it errors saying it cannot find the form object...any suggestions?

SELECT [C1_PROJ] & [C2_PROJ] & [PS1A_PROJ] & [PS2A_PROJ] & [PS1B_PROJ]
FROM Forms!CC_MAIN.CB_PROJ;

 

[Beetle]

I tried your suggestion but it errors saying it cannot find the form object...any suggestions?

I don't recall offering any suggestions, I simply said that your conclusion that you can't reference a form control from a saved query was wrong.

SELECT [C1_PROJ] & [C2_PROJ] & [PS1A_PROJ] & [PS2A_PROJ] & [PS1B_PROJ]
FROM Forms!CC_MAIN.CB_PROJ;

This is wrong on multiple levels;

First, you don't select multiple fields from a record set using the ampersand (&). Doing this will actually concatenate all those field values together in one string and return the concatenated value in one column. Seems unlikely that's actually what you want.

Second, a query returns records from a record set (a table or another query). A form is not a record set. You can't use it as the source for a query.

As an example, a query that selects records from a table, but uses a reference to a control on a form as criteria to restrict the results might look like;

Select Field1, Field2, Field3 From MyTable
Where Field1 = Forms!MyForm!MyComboBox

However, I'll say this again, if you will provide more insight as to what exactly you're trying to do, we can offer more specific advice.

 

[bobbybeers]

First just let me say thanks for providing insight on this, it is much appreciated. Now to get down to it, my query is quite complex which is why I didn't want to get into too much detail...essentially, what I am trying to do is run a complicated query that uses DLOOKUP to find a multitude of values from a table, however, I want to make the table that the query runs off of a variable that comes from a combobox populated by the user. I have a working query but it has the table name "hard coded" in the query...see the table name at the very bottom?? the table name is ZZ_PROJECT_X but I will have several projects and I want to be able to run this query for any of them thus the form combobox which states the project: In a nutshell, I want to run the query below except that instead of running it on table ZZ_PROJECT_X, it runs for a table specified by the user in a form.
SELECT [C1_PROJ] & [C2_PROJ] & [PS1A_PROJ] & [PS2A_PROJ] & [PS1B_PROJ] & [PS2B_PROJ] & [EP1_PROJ] & [EP2_PROJ] & [M1_PROJ] & [M2_PROJ] & [M3_PROJ] & [G1_PROJ] & [G2_PROJ] & [SA1_PROJ] & [SA2_PROJ] AS commodity_code, DLookUp("[COMP_DESC]","01_COMP_TYPE","[C1] = '" & [C1_PROJ] & "' and [C2] = '" & [C2_PROJ] & "'") & ", " & DLookUp("[MATL]","04_MATL_CODE","[M1] = '" & [M1_PROJ] & "' and [M2] = '" & [M2_PROJ] & "' and [M3] = '" & [M3_PROJ] & "'") & ", " & IIf([C1_PROJ]="P" And [C2_PROJ]="P",DLookUp("[PS_DESC]","02_PRESS_SCH","[PS1] = '" & [PS1A_PROJ] & "' and [PS2] = '" & [PS2A_PROJ] & "'") & " " & DLookUp("[END_PREP]","03_END_PREP","[EP] = '" & [EP1_PROJ] & "'"),DLookUp("[PS_DESC]","02_PRESS_SCH","[PS1] = '" & [PS1A_PROJ] & "' and [PS2] = '" & [PS2A_PROJ] & "'") & " " & DLookUp("[END_PREP]","03_END_PREP","[EP] = '" & [EP1_PROJ] & "'") & " by " & DLookUp("[PS_DESC]","02_PRESS_SCH","[PS1] = '" & [PS1B_PROJ] & "' and [PS2] = '" & [PS2B_PROJ] & "'") & " " & DLookUp("[END_PREP]","03_END_PREP","[EP] = '" & [EP2_PROJ] & "'")) & ", " & DLookUp("[GEOM_STD]","05_GEOM_STD","[G1] = '" & [G1_PROJ] & "' and [G2] = '" & [G2_PROJ] & "'") & IIf([SA1_PROJ]="Z" And [SA2_PROJ]="Z",".",", " & DLookUp("[SA_DESC]","06_SPECIAL_ATT","[SA1] = '" & [SA1_PROJ] & "' and [SA2] = '" & [SA2_PROJ] & "'")) AS FULL_DESCRIPTION
FROM ZZ_PROJECT_X;

 

[Beetle]

So you have a different table for every project? When you start a new project do you have to create a new table? This looks like a nightmare.

Anyway, to do what you want you'll likely need to build the SQL statement in code then use querydefs to set it as the SQL of a query or something similar. Something like;

Dim strSQL As String
Dim strTableName As String

strTableName = Forms!YourForm!YourComboBox

strSQL = "Select Field1, Field2, Field3 " _
          & "From " & strTableName