Change SQL string via VBA

Hi there,

I have attached a sample database for you to hopefully assist me with my quandry. Basically my code in my [cmdRun] button on [frmTest] works with [qryComparison] but my query only refreshes when I close the query and open it again not on click or via the refresh button.

The subform on [frmTest] should also refresh itself after the [cmdRun] has been executed. I have two expression fields in my query that are controlled by the two combo boxes of my [frmTest] when the [cmdRun] button is pressed.

I am trying to create a comparison form which looks at data increases by percentage + amount, depenmding on the month parameters set by the user via the form. Your assistance is much appreciated.
SampleExperts1-1.accdb
databarracksAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
peter57rConnect With a Mentor Commented:
I still don't know why your code didin't work.
However, here is something that does.  It avoids the use of the saved query altogether.


Private Sub cmdRun_Click()

Dim txtFrom As String
Dim txtTo As String
Dim txtFinal As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef
Dim txtFromVB As String
Dim txtToVB As String
Dim StrSQL As String

txtFrom = [Forms]![frmTest]![cmbFrom]
txtTo = [Forms]![frmTest]![cmbTo]
txtFinal = "[" + txtTo + "]-[" + txtFrom + "]"
txtFromVB = "[" + txtFrom + "]"
txtToVB = "[" + txtTo + "]"


 StrSQL = "SELECT Company.CompanyName, tblStorage.january, tblStorage.february, tblStorage.march, tblStorage.april, tblStorage.may, tblStorage.june, tblStorage.july, tblStorage.august, tblStorage.september, tblStorage.october, tblStorage.november, tblStorage.december," & txtFinal & " AS StorageDiff,[StorageDiff]/" & txtFromVB & " AS PercentageDiff" & vbCrLf & _
"FROM Company INNER JOIN tblStorage ON Company.company_id = tblStorage.company_id "
'& vbCrLf & _
'"GROUP BY Company.CompanyName, tblStorage.january, tblStorage.february, tblStorage.march, tblStorage.april, tblStorage.may, tblStorage.june, tblStorage.july, tblStorage.august, tblStorage.september, tblStorage.october, tblStorage.november, tblStorage.december;"

Me!subComparison.Form.RecordSource = StrSQL

End Sub
0
 
peter57rCommented:
I haven't looked at the file, so I am using just what you have said about your problem.

There is no Refresh capability for a query.  The only way to get a query to 'refresh' is to close and re-open.

A form can be requeried or refreshed.

If the recordsource might have new/deleted records not currently included/excluded and you need these then you need to use code in the form which says..
me.requery

If you only want updates to the existing recordsource records then you can use
me.refresh
0
 
databarracksAuthor Commented:
Ok thank you for that. How is it that if you reference a forms control on a form as criteria it updates automatically or as you said refresh the form it works. I have tried both the refresh method and requery on my form and it still doesn't work.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
databarracksAuthor Commented:
All I need it to do is recalculate my expressions within my query after I have pressed run. My query registers the SQL string definition changes though which is a start. Basically  the user can choose between which months they would like to compare data from, and my query is simply meant to return the difference in the sizes and percentage of growth between the parameters.
0
 
databarracksAuthor Commented:
I have made it work only by creating a temporary sql query that changes the record source of my form, but I would really like it to work from within a stored query, as I am worried about exerting to much work on my code as the table in reality is very large? Changed my code to the below.
Dim txtFrom As String
Dim txtTo As String
Dim txtFinal As String
Dim qryTest As QueryDef
Dim txtFromVB As String
Dim txtToVB As String



txtFrom = [Forms]![frmTest]![cmbFrom]
txtTo = [Forms]![frmTest]![cmbTo]
txtFinal = "[" + txtTo + "]-[" + txtFrom + "]"
txtFromVB = "[" + txtFrom + "]"
txtToVB = "[" + txtTo + "]"


[Forms]![frmComparison].RecordSource = "SELECT Company.CompanyName, tblStorage.january, tblStorage.february, tblStorage.march, tblStorage.april, tblStorage.may, tblStorage.june, tblStorage.july, tblStorage.august, tblStorage.september, tblStorage.october, tblStorage.november, tblStorage.december," & txtFinal & " AS StorageDiff,[StorageDiff]/" & txtFromVB & " AS PercentageDiff" & vbCrLf & _
"FROM Company INNER JOIN tblStorage ON Company.company_id = tblStorage.company_id " & vbCrLf & _
"GROUP BY Company.CompanyName, tblStorage.january, tblStorage.february, tblStorage.march, tblStorage.april, tblStorage.may, tblStorage.june, tblStorage.july, tblStorage.august, tblStorage.september, tblStorage.october, tblStorage.november, tblStorage.december;"

Open in new window

0
 
peter57rCommented:
" How is it that if you reference a forms control on a form as criteria it updates automatically "

It doesn't; in code you have to use a .requery to apply the new parameter values.

However I've now looked at the file and there is definitely something odd going on.  As you say the displayed results do not reflect the new query  and on a brief look I believe they should change on a requery.  I'll investigate further but it could be a while before I post again.
0
 
databarracksAuthor Commented:
Thank you for the advice, really appreciate the time you have taken out to help me and apologies for the misunderstanding just wasn't clear enough. What I meant was that if you have a form control set as the criteria of one of the fields in a query, upon a refresh of the control on the form and the query you would be able to see the new results.

AS you have witnessed, this isn't the case on my sample. I mentioned in my last comment that by forcing the code to simply change the recordsource of my subform has made it work, but the problem lies with saved or permanent queries. Would you advise against in doing it this way and rather plug away at making my permanent query work?
0
 
databarracksAuthor Commented:
Hi,

That is similar to what I proposed in ID:35748294 so I guess that would have tp be the way I have to do it. Could you please tell me the drawbacks of using this method if any?
0
 
peter57rCommented:
Sorry hadn't seen your post. I still had a previous version of the page open on my machine.

I don't see any drawbacks in this contex.  It's a perfectly fine way to do it.  You would only have issues arising if you needed the results of the query in another process.
But ther's nothing stopping you setting the query sql at the same time as you set the recordsource property so that they are always in line (if they need to be).
0
 
databarracksAuthor Commented:
Ok then, suits me fine and it would only ever be used in this process with the possibility of a report but I think I can make a plan. If you ever figure out why my current code didn't  work I would be very pleased as it mystifying as to why it has failed. That would have been my first choice, but if this delivers the result and is not awfully detrimental to the performance of the system, then so be it.
0
 
QlemoDeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
databarracksAuthor Commented:
Very appreciative of the help from all. Thank you Peter!
0
All Courses

From novice to tech pro — start learning today.