Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2006
  • Last Modified:

On Click Event Procedure in Access

Any clue what is causing Access to display this error when attempting to run this event procedure?

Receiving Run-time error '2342':
A RunSQL action requires an argument consisting of an SQL statement.

Here is the statement in the On Click [Event Procedure]:
DoCmd.RunSQL "SELECT FileTree.Location FROM FileTree LEFT JOIN tblBilder ON FileTree.[Location] = tblBilder.[location] WHERE (((tblBilder.location) Is Null))"
0
clock1
Asked:
clock1
  • 7
  • 5
  • 2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Try,

DoCmd.RunSQL "SELECT FileTree.Location FROM FileTree LEFT JOIN tblBilder ON FileTree.[Location] = tblBilder.[location] WHERE tblBilder.location Is Null;"

 and see if that flies.   If not, open the query designer, switch to SQL view, paste in the statement, then try switching to design view, and if it does, execute it.

 I don't see anything really right off, but there must be something in there.

Jim.
0
 
clock1Author Commented:
I pasted the statement which included (;) at the end of the statement and still seeing same error.
0
 
Gustav BrockCIOCommented:
Too many beers yesterday Jim??

It's because your SQL is a simple select which returns records - not meaningful here.
You need an action query - update, append, or delete - or a statement that opens the selection: DoCmd.OpenQuery ... or the like.

/gustav
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
clock1Author Commented:
/gustav:

I used this statement to open the query that contains the SQL:
DoCmd.OpenQuery ("FileTree Without Matching tblBilder")

However this actually opens the query and displays the results on the screen. My objective is that the On Click [Event Procedure] runs the query and my menu (where button is located) continues to display.  Making any sense?
0
 
Gustav BrockCIOCommented:
No that doesn't make sense.
Why should you run the query if you neither wish to watch the result or use the records?

Note the difference between a select and an action query.

/gustav
0
 
clock1Author Commented:
/gustav:

Once the select query runs and the records are selected, I run an append query to add the selected records to a table.
0
 
clock1Author Commented:
/gustav:

To be clear, there are 2 queries because I'm appending to a table that is used in the select query. Would be fantastic If there's a way to do that in one-shot.
0
 
clock1Author Commented:
/gustav:

Here's the SQL to append:

DoCmd.RunSQL "INSERT INTO tblBilder ( Location )SELECT [FileTree Without Matching tblBilder].Location FROM [FileTree Without Matching tblBilder];"
0
 
Gustav BrockCIOCommented:
That's easy. Create and test that append query and call it with CurrentDb.Execute:

http://msdn.microsoft.com/en-us/library/office/ff821728(v=office.15).aspx

/gustav
0
 
clock1Author Commented:
/gustav:

When using DoCmd.OpenQuery

Can one prevent Access from displaying the query results?
0
 
Gustav BrockCIOCommented:
No. That's the purpose of this command.
So if you don't want to display records, you don't use that command.

As said, if you want to append records, run (call) an append query. It's that simple.

/gustav
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Too many beers yesterday Jim??>>

  I was so focused on the statement, I didn't even think about what was being done.

Jim.
0
 
clock1Author Commented:
/gustav:

Took a few trys, and works like a charm. Thanks!
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now