Run SQL statement using a command button

[jake7363]

Hello,

If I have not placed this request in the proper forum, I apologize.
It has been quite a while since I have been here and quite a while since I used Access (365). I consider myself a novice bordering on moderate user.

I have utilized a tool from online which was created by a developer that converts Access SQL to VBA. The purpose of the SQL is to allow a user to select a date range of records to view in a continuous form. The source of the SQL was obtained by using the SQL button after creating a query; it then was converted to VBA (using the tool) without issue, as far as I can tell. I won't be sure until I can run the code, which is now my issue. While the answer to the question might seem simple to many, it is my current obstacle; so, how can I execute this SQL/VBA by using a command button?

Any help is appreciated.

Cheers,
Jake

[Minty]

What does the SQL statement do, is it a SELECT query to be used as a record source for a listbox or a form.
or is it action query that does an UPDATE, INSERT, Or DELETE to a table?

[ranman256]

queries can do this too and save lots of coding.
The button click code can be:

docmd.runSql "update table set [field2]=4 where [field1]=true

or

docmd.OPenquery "quUpdateMyTbl"

Simple Sql is ok, but some get very big & complex so a Query would work better.
Queries can also use paramaters best given from a form:

quUpdateMyTbl : "update table set [field2]=4 where [Date] between forms!fMyForm!txtStartDate and forms!fMyForm!txtEndDate

[Bob Fitz]

Hello,

If I have not placed this request in the proper forum, I apologize.
It has been quite a while since I have been here and quite a while since I used Access (365). I consider myself a novice bordering on moderate user.

I have utilized a tool from online which was created by a developer that converts Access SQL to VBA. The purpose of the SQL is to allow a user to select a date range of records to view in a continuous form. The source of the SQL was obtained by using the SQL button after creating a query; it then was converted to VBA (using the tool) without issue, as far as I can tell. I won't be sure until I can run the code, which is now my issue. While the answer to the question might seem simple to many, it is my current obstacle; so, how can I execute this SQL/VBA by using a command button?

Any help is appreciated.

Cheers,
Jake

Can you show us the code that you want to run

[jake7363]

It is a SELECT query with the user inputting 2 dates for the range from a PurchDate field. Here is the code. (Remember, I am a novice).

Me.Recordsource = "SELECT tblHHInv.DOCNO, tblHHInv.Qty, tblHHInv.Item, tblHHInv.Desc, tblHHInv.PurchDate, tblHHInv.Internet, tblHHInv.Vendor, tblHHInv.PyMthd, tblHHInv.Retail, tblHHInv.Total, tblHHInv.IJOR, tblHHInv.[IJOR#], tblHHInv.Transno, tblHHInv.ReferenceNo, tblHHInv.Manufacturer, tblHHInv.Mod_Sty, tblHHInv.[M/S Number], tblHHInv.[Product ID], tblHHInv.ItemNo, tblHHInv.SerialNo, tblHHInv.RegNo, tblHHInv.RegNo, tblHHInv.SKU, tblHHInv.MfrWebsite, tblHHInv.PartNo, tblHHInv.Room, tblHHInv.Category, tblHHInv.Attach1, tblHHInv.Path, tblHHInv.Comments" & _
"FROM tblHHInv" & _
"WHERE (((tblHHInv.PurchDate)>=[Start Date] And (tblHHInv.PurchDate)<=[End Date]));"

[Micron]

Try this in your button click event:

DoCmd.OpenQuery "SELECT tblHHInv.DOCNO..."

I agree with Ranman; better to just open a stored query rather than code it all in vba. It's far easier to modify the query (e.g. add a field) than it is to edit code.

[jake7363]

I understand about the simplicity of opening the query, but the thing I am trying to accomplish is to keep the results in a Continuous Form. Is there a way to do that using OpenQuery?

[jake7363]

I understand about the simplicity of opening the query, but the thing I am trying to accomplish is to keep the results in a Continuous Form. Is there a way to do that using OpenQuery?

Also, I ran the app and I got an Error 7874, stating that it could not find the object.

[Micron]

Then your question is how to set a form recordsource, not how to run sql with a button?
If the button is for opening the form and being prompted by those parameters, then the button click event opens the form. In the form open event:
Me.Recordsource = "SELECT tblHHInv.DOCNO..."

Better to assign the sql to a string variable so that you can output it to the immediate window to review it for accuracy.

If the button is to change the recordsource on the already open form then:
Me.Recordsource = "SELECT tblHHInv.DOCNO..."

IIRC, changing the recordsource will requery the form, so no need to do that in code. Another approach might be to filter the form records rather than continually altering its recordsource property.

[Gicu]

Are the [Start Date] and [End Date] controls on the continuous form you want to display the records in (they should be unbound controls placed in the form header)? If yes then you need to isolate those in your SQL statement:

"WHERE (((tblHHInv.PurchDate)>=#" & [Start Date] & "# And (tblHHInv.PurchDate)<=#" & [End Date] & "#));"

Cheers,

[jake7363]

Micron -
I hope my question didn't confuse things. If you view the reply I gave to Gicu, it will imply that my recordset is to be filtered by selecting two days from PurchDate that the user wants to view for whatever reason, not to change recordset but to filter a particular set of records from within that recordset and when done. remvoe the filter. Example: I want to see all the records between January 15, 2011 and March 30, 2011 because I am looking for a particular purchase that is somewhere between those dates.

If you look at the code from the original post, -Me.Recordsource = "SELECT..." - is at the beginning of the code, which is the result of the SQL button from the query design. So, the button is to be the trigger that sets off the code that prompts for the two dates that set the range for the filter to run.

I hope I did a little better at explaining the issue.

[jake7363]

Gicu-

There is only one date control source: "PurchDate" the code is to allow two inputs - one is the beginning of the range, the other at the end of the range - being a filter. Hope that clarifies things.

[Micron]

I'm lost now. I don't see what I last posted that suggests I didn't understand what you wrote in post 11. Since PurchDate seems to be a field and you say that both dates come from that field then what you have there are parameter prompts so post 10 suggestion would not apply.

Asked but not answered:

Then your question is how to set a form recordsource, not how to run sql with a button?

Seems to me your issue is about how to alter the recordsource property of a form. That's a bit more complicated than simply filtering its records.

[jake7363]

Micron,

It is more that I am not able to completely follow what you are explaining; again, I am a bit rusty. You are correct that post 10 does not apply. Perhaps I can give the concept to assist.
In my continuous form. I want the user to be able to click a button that asks for two dates to set a range of records to view within the form. Once done, a close button would clear the filter. That's all there is to it.

Several years ago, I thought I did such a thing, but it was in a different version of Access; but, as I say, that is years ago. Now, I know the query(filter) works, it's just how to implement it that I am not getting. Sorry for the confusion.

[Micron]

Did you notice the link in my prior post. It seems to cover what you are asking for.