Change field queried on by combo box 1

[MICKI0220]

I need help on creating a procedure that will run a query....Here is the scenario....I have about 25 different fields in a table.....On my form I have created a pop up form with a single combo box on it, so the user can select one of those 25 fields....I need my procedure to take that field identified in the combo box and base it's query on the results selected from that combo box.....Therefor the query will be changing what it queries everytime a different choice is made....In the end I will create a report based on the selection....Does that make sense, can someone help me out...

Thank you in advance

 

[AlexCuse]

I believe the best way to go about this is to create an SQL Statement like this (example to select where defined column is not null):

Dim sql1, sql2, Var as String

sql1 = "select * from TABLE where "
sql2 = " is not null" 

Var = Forms("FormName").Controls("ComboBox").Value

SQLRUN = sql1 & Var & sql2

DoCmd.RunSQL SQLRUN

HTH,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[MICKI0220]

Thank you Alex for your response, however I am confused on it's meaning.....I may be wrong, because I am a Network administrator first, programmer second, but are you selecting all the fields...I only need it to query the field identified in my combo box, but as I first implied, that will change given the different report they need. Can you explain it any further for me.

Thanks
Micki

 

[AlexCuse]

So do you want to get just one field from the table, where field value = combo box value? That would be as follows:

Dim sql1, Var as String

sql1 = "select FIELD from TABLE where FIELD = "


Var = Forms("FormName").Controls("ComboBox").Value

SQLRUN = sql1 & Var

DoCmd.RunSQL SQLRUN

If this does not work, please post an example of the query you would like, specifying what portion you need to be variable.

Good Luck,

Alex



It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[AlexCuse]

I think I've got it, you want to select just a single column from your table, based on which column is chosen by the user? That would be as follows:

Dim sql1, sql2, Var as String

sql1 = "select "
sql2 = " from TABLENAME"

Var = Forms("FormName").Controls("ComboBox").Value

SQLRUN = sql1 & Var & sql2

DoCmd.RunSQL SQLRUN

If you try running this code, but replace DoCmd.RunSQL SQLRUN with debug.print SQLRUN, you should see a properly formatted SQL Statement.

THe runsql command will run this statement.

(If the Combo box value has anything funky like spaces in it you may need to put brackets around Var)

Let me know if you need anythign else.

Take care,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[MICKI0220]

Alex

This is what I typed in on the print button in a access form....when I click it, it does nothing...at first it gave me an error indicating it needed an "insert, update, procedure or some command....If I comment out either docmd, I get the same results. Can you tell me what I am doing wrong.



Private Sub cmdprint_Enter()
Dim sql1, sql2, var As String

sql1 = "selelect"
sql2 = "from constant"
var = Forms("phaseselection").Controls("combo0").Value

sqlrun = sql1 & var & sql2
'DoCmd.RunSQL sqlrun
Debug.Print sqlrun


End Sub

 

[MICKI0220]

By the way, most of the values are values such as this "BC_10", "CDC", or "SD_15"

Thanks

 

[AlexCuse]

You don't have a space between "select" and var and "from..."

So your statement reads "selectvarfrom constant"

Change SQL1 & SQL 2 to look like this:

sql1 = "select "
sql2 = " from CONSTANT"

Note the spaces between select and end quote in SQL1 and begin quote and from in SQL2. This will give you proper formatting of your SQL Statement.

Hope this helps,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[MICKI0220]

Alex, It still doesn't do anything when I click the forms button. Any other suggestions I should try to view the SQL statement.

Micki

 

[AlexCuse]

I am still not 100% clear on what you're trying to do here. Are you trying to assign a data source for a report, and then print that report? If that is the case you need to create the SQL statement, and then programmatically assign it as the report's data source prior to printing.

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[AlexCuse]

If you are trying to change a reports' data source, this may do the trick

Dim sql1, sql2, Var as String

sql1 = "select "
sql2 = " from CONSTANT"

Var = Forms("FormName").Controls("ComboBox").Value

SQLRUN = sql1 & Var & sql2



DoCmd.SetWarnings WarningsOff                                               DoCmd.OpenReport "XXX",acDesign                                  'Open Report in Design Mode
Reports("XXX").RecordSource = SQLRUN  
'change data source
DoCmd.CloseacReport, "XXX"             'Close Design Mode
DoCmd.SetWarnings WarningsOn

DoCmd.SelectObject acReport, "XXX",True
DoCmd.PrintOut acPrintAll

Of course, "XXX" needs to be replaced with your report name. Let me know if this works for you.

Also, make sure to look at the default printer for your report.

Alex




It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[Golom]

Can you tell me what I am doing wrong?
In your code you had

sql1 = "[red]selelect[/red]"

and that is not how you spell SELECT. As others mentioned, you also needed a space after it (after correcting the spelling.)

Not sure why you're messing with all these strings

Private Sub cmdprint_Enter()
Dim SQL As String

SQL = "Select [" & _ 
      Forms("phaseselection").Controls("combo0").Value & "] " & _
      "FROM constant"

Debug.Print SQL

End Sub

It doesn't do anything because "DoCmd RunSql" is intended for action queries like INSERT, UPDATE, DELETE, MAKE TABLE, etc. This is a SELECT query ... not an action query.

As AlexCuse says, if you want to see a report containing the result then assign the generated SQL statement to the report's recordsource.

 

[AlexCuse]

I apologize for the bad formatting in my previous post, guess I should've previewed. It should look like this at the start of the report modification:

DoCmd.SetWarnings WarningsOff
DoCmd.OpenReport "XXX",acDesign                         
'Open Report in Design Mode 
Reports("XXX").RecordSource = SQLRUN

Also, Golom is correct about not needing all the strings. I was anticipating a more complex query, and the strings often make it easier to put them together. For a simple select statement they are completely unnecessary (I was just lazy and wanted to copy/paste from code I had).

Hope this works for ya!

Alex



It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[MICKI0220]

Alex

sorry for the late response...I got called off the project and I will try to make this work in the next couple of days. I will let you know how it pans out.

Micki

 

[MICKI0220]

I have finally been able to get back on this.....This is my code.


Private Sub cmdreport_Click()
Dim SQL As String

SQL = "Select" & ["job_id" & "job_description" & Forms("phaseselection").Controls("Combo0").Value & " ] & _
"From Constant" & "Where" & Forms("phaseselection").Controls("Combo0").Value & "]" & "Is not Null"
Debug.Print SQL

DoCmd.SetWarnings Warningsoff
DoCmd.OpenReport "Phase Selection", acViewDesign
Reports("Phase Selection").RecordSource = SQLRUN
End Sub


I have created the report, but it the box where the selection will change based on the combo, what will it's control source be....will it be the combo box or some other code? Is this code logical. I had to add a where clause.

 

[AlexCuse]

Couple problems with your code -

1. You reference SQLRUN when changing your record source, this should just be .RecordSource = SQL
1a. You also should include DoCmd.SetWarnings.WarningsOn before the end of the sub.
1b. Don't you want to print the report from this sub as well?

2. You need to include spaces inside the quotes for your SQL Statement IE

"Select "
rather than
"Select"

in order to generate a properly formatted SQL Statement. Lack of spaces will lead to an error when trying to generate report.



The control source for your combo box should be a list of options you want the user to have, I would just place a small table somewhere.

Let me know how this works for you,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[MICKI0220]

The control source for your combo box should be a list of options you want the user to have, I would just place a small table somewhere".

Could you explain this statement in a little more detail...can the results of that sql statement be placed in a table that the report can draw from?

 

[AlexCuse]

If you want to create a table to be the source of data for your report, that would kind of negate the whole process of setting your data source query programmatically.

Because the value in combo needs to be field names, I would create a table that looks like this:

Field1
Field2
Field3
Field4
and so on....

This table can be the control source for your combo box, and the selected value will be used in the sql query.

The REPORT control source will be the field name specified in your query. One hint, I would do select AS (name for report) so that there is no confusion caused for report when column (control source) has a different name.

Hope this helps,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[MICKI0220]

In the combo box my control is a list where I put the field names into the list. I am referring to the report. what is the control source of the value that will be the field referenced.....Also, I am getting a runtime error, '2465'...It says...Microsoft Access can't find the field '|' refered to in your expression....do I have the correct syntax to select the two other fields?


Private Sub cmdreport_Click()
Dim SQL As String

SQL = "Select " & ["job_id" & "job_description" & Forms("phaseselection").Controls("Combo0").Value & " ] & _
"From Constant " & "Where" & Forms("phaseselection").Controls("Combo0").Value & "]" & "Is not Null"
Debug.Print SQL

DoCmd.SetWarnings Warningsoff
DoCmd.OpenReport "Phase Selection", acViewDesign
Reports("Phase Selection").RecordSource = SQL
DoCmd.SetWarnings Warningson
End Sub



And how can I show the results that will go into my report?
Thanks again for your help

Micki

 

[AlexCuse]

Your SQL Statement is a nightmare. You have a [ outside your quotes, and my hunch is that you don't have a field named [ in your table. You are also missing comma's and spaces. Take a look in the debug window when you get error, that should confirm this. Try this:

SQL = "Select job_id, job_description " & Forms("phaseselection").Controls("Combo0").Value & _
" [COLOR=red]AS FormValue[/color] From Constant Where " & Forms("phaseselection").Controls("Combo0").Value & " Is not Null"

Note the spaces before closing quotes, these are very important.

I can't test in access right now, as it is busy generating a huge report for me, but this should give you a properly formatted SQL Statement.

*Note in Red, if you change a field in your report to 'FormValue' and add this code, then the report will pick up the correct value regardless of which column you are selecting.

HTH,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.