databarracks
asked on
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
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
ASKER
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.
ASKER
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.
ASKER
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;"
" 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.
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.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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).
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).
ASKER
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.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER
Very appreciative of the help from all. Thank you Peter!
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