SQL statement to run on the OnClick event

Below is some code that contains a SQL statement to run on the OnClick event of a button. When copying the SQL statement produced by the "stSQL" variable, and pasting into the SQL view of a new query, I get results just fine.
But I keep getting error 2342: "A RunSQL action requires an argument consisting of an SQL statement.", when it runs in code.
Variable stSQL, after being assigned returns in the immediate window as:

Code...

Sponsored Links:

Related Topics

RunSQL statement
Can anybody tell me why this SQL statement won't run in in the RunSQL macro action.

SELECT Open_Issues.* FROM Open_Issues WHERE ((Open_Issues.Account_No)=(Forms.Open_Issues_Viewe r.Text64));

It tells me that the RunSQL action must have an SQL statement for an arguement when I try to run the macro. I thought that was a perfectly valid SQL statement considering I checked it with a query


current row not selected on datasheet view (access 2010)
I have a subform in datasheet view - three fields { PersonID, Surname, Forenames }. The PersonID has an onClick event that opens another form filtered to that ID. The issue that I'm having is that whichever row I click, the topmost row's onClick event fires - unless I select the row and then click - in which case the desired onClick event fires. Are there any known issues that could cause this to occur?


OLE Object
I am using MS Access 2003 to link to an 11g Oracle Database. There is an Oracle Table that has an OLE Object column field and I want to be able to get the information from that column. I can run the following SQL statement from an SQL Plus session and get the values but I want to run the same type of statement in MS Access.

How can I convert the statement below so it will run in MS Access?


Refresh combobox
With the help of this forum I was able to:

in my form I have a input box that the user types in a house number. When the user clicks the street name combobox a query runs that selects the unique street names with that house number.

In the row source of the combobox I Have this:
SELECT [qry_license_combobox_populate].[Street_Name] FROM qry_license_combobox_populate ORDER BY [Street_Name];

This works fine.but if I go back and type in a new house number and click the button it does not refresh the combobox. Just trying to figure out how to re-run the query and repopulate the combobox every time the user clicks the combobox.can I fire the query to populate the combobox on the onclick event? I copied and paste the line above to the Onclick event and get errors.although I now have that line on the row source and the onclick event.


Create Command Button with Onclick Event
I am writing code to dynamically create a report each time a button on a form is clicked. I have the majority of the report complete but I am getting stuck on some functionality issues. I want to create a variable number of buttons that will do something when clicked (I know how to write that portion of the code). I am getting stuck though because I need to change each button's "onclick" event as it is created.

Does anyone know how to edit the code in the "onclick" event immediately after it is created in vba?

I hope I have been clear enough in my question


Running two SQL Statements
I have one query, where I added the leading "0's" and the SQL statement I used to do this was:

SELECT =Format([SKP B],"00000") AS Expr1
FROM Sheet1;

Then from this query, I want to combine the SKP B (with the leading zero's) and SKP A into one row in Access, and use the following SQL statement to do that:

SELECT [Field1] & "-" & [Field2] AS Field1_Field2
FROM Sheet1;

The problem that I run into is that when I run the 2nd SQL statement the leading "O's" are dropped.

I know that you can do this in two separate queries but I would much rather do it in one if that is possible.
So my question is, is there a way to run two SQL statements in one query?
OR is there a way for me to combine both of these statements into one SQL query?


Is variable accessible with onClick event
When using variables in a forms vba module (not sure the actual name of that, but NOT a separate module, but the one attached to a form), If I declare a variable in the forms onOpen event and set a value to it, would that variable and value be accessible in lets say that same forms onClick event of a button?


Create an onclick event for a button created by vba
I've created a form using vba and I have used the createControl function to add a button to the form. I want to include code in the button creation which specifies what I would like to happen when the button is pressed (run an sql insert) ...


Use a SQL statement to delete data
Writing SQL to delete data from an MS Access table is just about the easiest SQL statement you can write.
This example will use the SQL statement to create an Access query, but you can use the statement in a macro or VBA as well.


Set onclick via VBA
I have a form that's used for setting parameters for various reports as they open. I have a procedure set on the OnLoad of each report to open this form. In that event, I need to change the OnClick event procedure for the form's Cancel button so that it would close both the form and the report.

Report: rptAgentByDate (as a sample)
Form: frmReportParams
Button: frmReportParams!cmdCancel

Trying to search for this proved useless, as "onclick" isn't terribly useful in a search


What happens to an event handler's parameter when I specify a custom handler?
If I type in the following in the properties editor for a form's event handler, say onClick:

=myfunc(10)

then when the onClick event fires, my custom function is called with parameter 10.

But what about when I type the same thing in for a function like onUnload that passes a Cancel parameter to an event procedure? Can I access that parameter from a custom event handler function? I tried writing something like:

Public Function myfunc(i as integer, ByRef param as variant)

to see if the Cancel parameter would "show up." Can someone shed some light on what happens to the Cancel parameter or how I can access it


Run a SQL UDF in Access Pass through query
I am converting a very old, very cumbersome Access db to SQL. I've been successful with most of it, still using Access (2010, now) as the front-end for the forms and stuff.
However, I have two small UDF's on the SQL server (SQL 2005) that I want to run from Access via a pass through query. Here is the SQL statement:

USE CCT
SELECT dbo.PROPERCASE(vendor_name)
FROM CCT..Contracts;

when I run this in SQL, it works perfectly, converting all the records in the "vendor_name" field in the table "Contracts".
However, when I run it in the pass through query in Access, nothing happens - no errors, but no case-change either.


Which is better? checking in VBA or SQL
I currently have a VBA statement that runs SQL to append to a table. I want to first check if there is already a record with the same name in the table to avoid duplicate entries.

Is it best to check before the query is run in the VBA, or in the SQL statement it's self to avoid this problem better?

I'm not sure how I even would check the table with SQL.


SQL Recordset Versus Filtering
Currently, I have a form that uses SQL to generate a recordset on the "On Open" event. The form's "On Activate" is then used to filter the recordset based on a command button pressed in the calling form.

Instead of doing this two step process, I am contemplating revising the code to move recordset creation from the "On Open" event to the "On Activate" event. This would eliminate the filtering step, but then I would be creating a new recordset each time the form is called. Of course, I would be adding the "Where" clause to the SQL statement.


Clear values in a table column
All I want is a button that will clear all data in a specific column of a table. I created my form, added a button, and entered the following SQL statement.

Sub Command3_Click()

Update RecipeList
Set DayID = Null

End Sub

When I run it, I get "Compile error: Sub or Function not defined". I can run other sql statements, such as to display a message box. It just does not seem to recognize the "Update" statement.


Use a control in an SQL statement in Access
In Access 2003/XP/2000/97, I've created a form where the user enters a value in a textbox. I want to use this value to create an SQL statement. How do I use a control's value in an SQL statement in VBA code?


If Statement wont evaluate values
I have a command button on a form that shows two text boxes when clicked. The two text boxes are the date range in which I want a connecting report to print. There is a start date and an end date.

The problem is that I don't want an error message to pop up whenever someone doesn't enter the date range. I have placed the following code within the onclick event but the statements go right past the if statement. (The two date range text boxes are unbound)

Here is what I have:


How To Use An SQL Statement In A TransferSpreadsheet Command
I have some some data I would like to output to an excel ss using a SQL statement in my code. Problem is, I don't know how to refer to that sql statement in the transferspreadsheet statement.

Below I have an example. The strSQL variable in the transferspreadsheet statement is obviously incorrect, but just placed to show what I want.

I know I could do it by creating a table and then referring to that created table in the transferspreadsheet function, but would like avoid this if possible.

strSQL = "SELECT table1.SUBID from Table1 where table1.loc = somecriteria"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strSQL, filepathandname, True


input new record to table
I tried this

Dim rec as Recordset
Set rec = CurrentDb.OpenRecordset("EVENT NUMBERS", dbopendynaset)
with rec
.addnew
!F1 = MCC
!F2 =-
.update
end with
set rec = nothing

but get this

Invalid SQL statement expected; delete, insert, procedure, select, update

can some one fix the above code.i build a macro and at the end I "run SQL" with the above


How to Write a SELECT Statement in SQL
A SQL SELECT statement can be broken down into four basic parts. More advanced SQL statements can contain other keywords, but these four make up the core of most queries.