DoCmd.RunSQL error Help

K

Kate

I'm trying to execute an SQL statement in a macro attached
to a form but get error A RunSQL action requires an
argument consisting of an SQL statement. What is wrong?

Dim strSQL As String

strSQL = "SELECT AVAILABILITY.BookingDate,
AVAILABILITY.Period, AVAILABILITY.Room," _
& "AVAILABILITY.[Day Number], AVAILABILITY.[Booking ID] "
_
& "FROM AVAILABILITY " _
& "WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE
BOOKING AVAILABILITY]" _
& "!BookingDate) & ((AVAILABILITY.Period)=Forms![SINGLE
BOOKING AVAILABILITY]" _
& "!Combo8) & ((AVAILABILITY.Room)=Forms![SINGLE BOOKING
AVAILABILITY]!Combo10))"

DoCmd.RunSQL strSQL

SQL should look like this:

SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
AVAILABILITY.Room, AVAILABILITY.[Day Number], AVAILABILITY.
[Booking ID]
FROM AVAILABILITY
WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE BOOKING
AVAILABILITY]!BookingDate) And ((AVAILABILITY.Period)
=Forms![SINGLE BOOKING AVAILABILITY]!Combo8) And
((AVAILABILITY.Room)=Forms![SINGLE BOOKING AVAILABILITY]!
Combo10));
 
C

chris

RunSQL only works with action queries (insert, update
delete etc) that do not return recordset objects. A select
query needs a recordset object to store its results
 
F

fredg

I'm trying to execute an SQL statement in a macro attached
to a form but get error A RunSQL action requires an
argument consisting of an SQL statement. What is wrong?

Dim strSQL As String

strSQL = "SELECT AVAILABILITY.BookingDate,
AVAILABILITY.Period, AVAILABILITY.Room," _
& "AVAILABILITY.[Day Number], AVAILABILITY.[Booking ID] "
_
& "FROM AVAILABILITY " _
& "WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE
BOOKING AVAILABILITY]" _
& "!BookingDate) & ((AVAILABILITY.Period)=Forms![SINGLE
BOOKING AVAILABILITY]" _
& "!Combo8) & ((AVAILABILITY.Room)=Forms![SINGLE BOOKING
AVAILABILITY]!Combo10))"

DoCmd.RunSQL strSQL

SQL should look like this:

SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
AVAILABILITY.Room, AVAILABILITY.[Day Number], AVAILABILITY.
[Booking ID]
FROM AVAILABILITY
WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE BOOKING
AVAILABILITY]!BookingDate) And ((AVAILABILITY.Period)
=Forms![SINGLE BOOKING AVAILABILITY]!Combo8) And
((AVAILABILITY.Room)=Forms![SINGLE BOOKING AVAILABILITY]!
Combo10));
Click to expand...

RunSQL will only run Action queries.

Create a query using your SQL.
Then Run the query from your code event.
DoCmd.OpenQuery "QueryName"
 
K

Kate

Ok. So how do I run an SQL query in my code and have the
results available? Thanks.

-----Original Message-----
RunSQL only works with action queries (insert, update
delete etc) that do not return recordset objects. A select
query needs a recordset object to store its results
-----Original Message-----
I'm trying to execute an SQL statement in a macro attached
to a form but get error A RunSQL action requires an
argument consisting of an SQL statement. What is wrong?

Dim strSQL As String

strSQL = "SELECT AVAILABILITY.BookingDate,
AVAILABILITY.Period, AVAILABILITY.Room," _
& "AVAILABILITY.[Day Number], AVAILABILITY.[Booking ID] "
_
& "FROM AVAILABILITY " _
& "WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE
BOOKING AVAILABILITY]" _
& "!BookingDate) & ((AVAILABILITY.Period)=Forms![SINGLE
BOOKING AVAILABILITY]" _
& "!Combo8) & ((AVAILABILITY.Room)=Forms![SINGLE BOOKING
AVAILABILITY]!Combo10))"

DoCmd.RunSQL strSQL

SQL should look like this:

SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
AVAILABILITY.Room, AVAILABILITY.[Day Number], AVAILABILITY.
[Booking ID]
FROM AVAILABILITY
WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE BOOKING
AVAILABILITY]!BookingDate) And ((AVAILABILITY.Period)
=Forms![SINGLE BOOKING AVAILABILITY]!Combo8) And
((AVAILABILITY.Room)=Forms![SINGLE BOOKING AVAILABILITY]!
Combo10));

.
Click to expand...
.
Click to expand...
 
K

Kate

Trouble with that is I then want to test a field (Booking
ID) in the returned record and I get an error message
Object required. Any thoughts

-----Original Message-----
I'm trying to execute an SQL statement in a macro attached
to a form but get error A RunSQL action requires an
argument consisting of an SQL statement. What is wrong?

Dim strSQL As String

strSQL = "SELECT AVAILABILITY.BookingDate,
AVAILABILITY.Period, AVAILABILITY.Room," _
& "AVAILABILITY.[Day Number], AVAILABILITY.[Booking ID] "
_
& "FROM AVAILABILITY " _
& "WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE
BOOKING AVAILABILITY]" _
& "!BookingDate) & ((AVAILABILITY.Period)=Forms! [SINGLE
BOOKING AVAILABILITY]" _
& "!Combo8) & ((AVAILABILITY.Room)=Forms![SINGLE BOOKING
AVAILABILITY]!Combo10))"

DoCmd.RunSQL strSQL

SQL should look like this:

SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
AVAILABILITY.Room, AVAILABILITY.[Day Number], AVAILABILITY.
[Booking ID]
FROM AVAILABILITY
WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE BOOKING
AVAILABILITY]!BookingDate) And ((AVAILABILITY.Period)
=Forms![SINGLE BOOKING AVAILABILITY]!Combo8) And
((AVAILABILITY.Room)=Forms![SINGLE BOOKING AVAILABILITY]!
Combo10));
Click to expand...

RunSQL will only run Action queries.

Create a query using your SQL.
Then Run the query from your code event.
DoCmd.OpenQuery "QueryName"
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
F

fredg

Trouble with that is I then want to test a field (Booking
ID) in the returned record and I get an error message
Object required. Any thoughts
-----Original Message-----
I'm trying to execute an SQL statement in a macro attached
to a form but get error A RunSQL action requires an
argument consisting of an SQL statement. What is wrong?

Dim strSQL As String

strSQL = "SELECT AVAILABILITY.BookingDate,
AVAILABILITY.Period, AVAILABILITY.Room," _
& "AVAILABILITY.[Day Number], AVAILABILITY.[Booking ID] "
_
& "FROM AVAILABILITY " _
& "WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE
BOOKING AVAILABILITY]" _
& "!BookingDate) & ((AVAILABILITY.Period)=Forms! [SINGLE
BOOKING AVAILABILITY]" _
& "!Combo8) & ((AVAILABILITY.Room)=Forms![SINGLE BOOKING
AVAILABILITY]!Combo10))"

DoCmd.RunSQL strSQL

SQL should look like this:

SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
AVAILABILITY.Room, AVAILABILITY.[Day Number], AVAILABILITY.
[Booking ID]
FROM AVAILABILITY
WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE BOOKING
AVAILABILITY]!BookingDate) And ((AVAILABILITY.Period)
=Forms![SINGLE BOOKING AVAILABILITY]!Combo8) And
((AVAILABILITY.Room)=Forms![SINGLE BOOKING AVAILABILITY]!
Combo10));
Click to expand...

RunSQL will only run Action queries.

Create a query using your SQL.
Then Run the query from your code event.
DoCmd.OpenQuery "QueryName"
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
Click to expand...

You can use a DLookUp() in a form control to find any particular
record in the query (without actually running the query).
See Access help for
DLookUp and
Where clause + restrict data to a subset of records.

In any event, you CANNOT run a Select query using RunSQL.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax error 1
Too few parameters error. Help needed 1
Help passing parameters to a query 1
Process multiple records 1
Checking values in returned records 0
syntax error? Help 2
Running a query using data from a form 1
Update not working 1

Top