Set SQL through VBA (1 Viewer)

nstratton

Registered User
Joined
Aug 30, 2015
Messages
85
I am attempting to build SQL on a form load that will populate a chart on the form. The form is unbound with its sole purpose to hold the chart, no other information is need on the form. I am using the below code to get it together however I keep running into Error 3143 Syntax Error in TRANSFORM Statement.

This is a testing type of form and I just copied the SQL over from a form that I know works without using VBA to build it. The difference being I am wanting to use a multi-select listbox to narrow down the results. The bolded line is where the debugger takes me but that doesn't really help me any.

There is more code under this but it is used for formatting the chart and not relevant to the issue.
Note: The database is being built in Access 2010 desktop version.

Code:
Private Sub Form_Load()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Dim chtWeek As Chart

'Set chtWeek = Me.Graph0.Object
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryTest")

For Each varItem In Forms!frmChartMenu!lstSuppliers.ItemsSelected
   strCriteria = strCriteria & ",'" & Forms!frmChartMenu!lstSuppliers.ItemData(varItem) & "'"
Next varItem

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

strSQL = "PARAMETERS [Forms]![frmChartMenu]![lstSuppliers] Text ( 255 ), [Forms]![frmChartMenu]![txtTargets] Text ( 255 );" & _
         "TRANSFORM Count(*) As [Count]" & _
         "SELECT Format([FileDate],'Short Date') AS Expr1, [Forms]![frmChartMenu]![txtTargets] AS [DailyGoal] " & _
         "FROM tblRawData" & _
         "WHERE tblRawData.Supplier IN(" & strCriteria & ")" & _
         "GROUP BY (Format([FileDate],'Short Date')), (Int([FileDate])), [Forms]![frmChartMenu]![txtTargets]" & _
         "PIVOT qryTest.[Supplier];"

         
         
[B]qdf.SQL = strSQL[/B]

DoCmd.OpenQuery "qryTest"

Set db = Nothing
Set qdf = Nothing
 

Minty

AWF VIP
Joined
Jul 26, 2013
Messages
6,539
Put a debug.print strSQL in before you set the qdf. I think it's because you can't refer to the form controls in SQL , it doesn't understand them, as they are an access object.
Set the parameters as variables outside the StrSQL definition then add those variables in to your SQL statement.
 

JHB

Have been here a while
Joined
Jun 17, 2012
Messages
7,720
Use a Debug.Print to find out if the strSQL is correct setup.
Take the output from the Debug.Print (from the Immediate window) and paste it into a new query.
 

jdraw

Super Moderator
Staff member
Joined
Jan 23, 2006
Messages
12,558
As the others have said, you have a mixture of variables and form controls.
You need to "render" the form control values before substituting into the SQL.
Also, in the strSQL, I would start each line with a space character
eg
Code:
 " FROM tblRawData" & _
 " WHERE tblRawData.Supplier IN(" & strCriteria & ")" & _
 

nstratton

Registered User
Joined
Aug 30, 2015
Messages
85
So, for example, everytime I need to use the listbox, in SQL I would define it as strCriteria? Even in the parameters section?
When I posted the immediate window into a new query, I appeared as one block of jumbled mess. Once I put everything on its own line I discovered an issue with the PIVOT line. Corrected that and the query ran correctly. Made the same change in VBA with no luck.
 

nstratton

Registered User
Joined
Aug 30, 2015
Messages
85
What I ended up doing was adding a Chr(13) & Chr(10) to the end of the PARAMETERS line and it worked. Not really sure why that worked.

Since that worked like expected, how do I make it the rowsource for a chart?
I assume it is Me.GraphName.RecordSource = strSQL or something close to it.
The charts won't work if the main query is in crosstab format (or I can't get it to at least) so I would need the main query to remain SELECT and then the chart by formatted as CROSSTAB
 
Last edited:

JHB

Have been here a while
Joined
Jun 17, 2012
Messages
7,720
..
When I posted the immediate window into a new query, I appeared as one block of jumbled mess. Once I put everything on its own line I discovered an issue with the PIVOT line. Corrected that and the query ran correctly.
Exactly what I expected. :)
..
The charts won't work if the main query is in crosstab format ...
It shouldn't be a problem or else I'm misunderstanding what you're writing.
Post a stripped down version of your database with some sample data + name of the form.
 

nstratton

Registered User
Joined
Aug 30, 2015
Messages
85
I have attached the database. I removed everything but what this question is dealing with. Since my post I learned I have actually been building the charts wrong and I am not sure if the code I posted will help me anymore.

I am apparently supposed to be building at least 7 different charts broken down by various criteria then 1 extra where they can pick their own criteria which is where the listbox came in.

I have attached a powerpoint of the different graph types they want. All of which need to be able to be filtered by at least a date range. The goals are calculated by Production Days * Daily Target which are fields on the ChartMenu form.

If there is a way to create these charts without having to create a crazy amount of them for every situation imaginable I would greatly appreciate some guidance.

I feel like I didn't explain my process of what I have been doing to create the charts. I have a base query, in this case qryCharts that all charts are based on initially to use the Chart Wizard. I then go into the chart rowsource and adjust it as needed. So what ends up happening is the base query remains select and the chart rowsource query becomes crosstab or I assume it will in some cases. Any changes I make to the base query do not always make it to the chart because I modify its rowsource.
 

Attachments

Last edited:

JHB

Have been here a while
Joined
Jun 17, 2012
Messages
7,720
I have attached the database. I removed everything but what this question is dealing with. Since my post I learned I have actually been building the charts wrong and I am not sure if the code I posted will help me anymore.
If this is so, then this thread probably not have the same relevance as originally where your question was how to set SQL using VBA.
And also your second problem "The graph will not work if the main query is in Crosstab format" which was what I responded to.

I can't help you building the reports you want shown in the Powerpoint file, I don't know your data good enough and I don't have the time, sorry.
 

nstratton

Registered User
Joined
Aug 30, 2015
Messages
85
It's all good. I appreciate your input. The original question was answered so I will mark it solved and give the thanks where appropriate. The rest was just pushing my luck and seeing what the geniuses on this site might be able to come up with. As you could probably see, the data is not very friendly for charts since there are no actual numbers and having to use counts and such. Thank you everyone, you guys have helped tremendously in my first database project and hopefully not last
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom