Topics Search

SQL statement to run on the OnClick event

SQL statement to run on the OnClick event
Views: 92
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:

Sponsored Links:

Similar posts...


RunSQL statement

RunSQL statement Icon
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)

current row not selected on datasheet view (access 2010) Icon
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

OLE Object Icon
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?

Running two SQL Statements

Running two SQL Statements Icon
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?

Refresh combobox

Refresh combobox Icon
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

Create Command Button with Onclick Event Icon
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

Is variable accessible with onClick event

Is variable accessible with onClick event Icon
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?

Use a SQL statement to delete data

Use a SQL statement to delete data Icon
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

Set onclick via VBA Icon
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

Which is better? checking in VBA or SQL

Which is better? checking in VBA or SQL Icon
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.