SQL and Access VB Problem

[Guest]

As I can't write SQL, I use the query builder to build a query, then show me
the SQL version and then I copy and paste this into my Access VB to define my
recordsets.

This usally works fine, but on this occassion I've spent ages trying to get
it right and can't.

My application books people onto courses and sends them an email if a
certain box is ticked.

The form "Bookings" has a sub form "Attendees", so I can scroll through each
record in "Bookings" and see which "Attendees" are due to attend the course
and if their box is ticked include them in the recordset for the email.

The direct copy from the SQL design is:-

SELECT tblAttendees.UCLANEmail
FROM tblAttendees
WHERE (((tblAttendees.EventRef)=[Forms]![frmBookings]![EventRef]) AND
((tblAttendees.Email)=Yes));

If for example I want to get a recordset for course number 1, the following
SQL works fine:

SELECT tblAttendees.UCLANEmail
FROM tblAttendees
WHERE tblAttendees.EventRef = 1 AND (tblAttendees.Email = True)

but any attempt to substiute the "1" for the ref from the "bookings" form
results in a crash.

If anyone one could help, and show me where I'm going wrong I'd be very
grateful.

thanks

Winger

 

[Guest]

Hi Winger,

Try printing the results of your select string to the immediate window,
which you can open with Ctrl G. For example:

Dim strSQL As String

strSQL = "SELECT tblAttendees.UCLANEmail " _
& "From tblAttendees " _
& "WHERE (((tblAttendees.EventRef)= " _
& [Forms]![frmBookings]![EventRef] _
& " AND ((tblAttendees.Email)=Yes));"

Debug.Print strSQL

Of course, frmBookings must be open, and the control named EventRef must
have a saved value (ie. record not dirty). This SQL statement also assumes
that EventRef is numeric; if EventRef is text, then you need to wrap it in
quotes, like this:

strSQL = "SELECT tblAttendees.UCLANEmail " _
& "From tblAttendees " _
& "WHERE (((tblAttendees.EventRef)= " & Chr(34) _
& [Forms]![frmBookings]![EventRef] & Chr(34) _
& " AND ((tblAttendees.Email)=Yes));"


If the resulting SQL statement that is printed to the immediate window looks
okay (ie. proper spacing between SQL keywords), then try copying it to the
Windows clipboard, and pasting it into the SQL view of a new query.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Tom,

Thanks for you note.

frmBookings is open as this is where the control sits, and EventRef is
numeric.

I copied your code to replace my SQL, but when I try to run it I get a
syntax error that still shows the old code, even though I replaced it, and I
can't find it in the module anywhere!!

I reset the db each time it crashes, so do I need to reset or clear
something else? I've not seen something like that before.

thanks

Winger



Do I need to

Hi Winger,

Try printing the results of your select string to the immediate window,
which you can open with Ctrl G. For example:

Dim strSQL As String

strSQL = "SELECT tblAttendees.UCLANEmail " _
& "From tblAttendees " _
& "WHERE (((tblAttendees.EventRef)= " _
& [Forms]![frmBookings]![EventRef] _
& " AND ((tblAttendees.Email)=Yes));"

Debug.Print strSQL

Of course, frmBookings must be open, and the control named EventRef must
have a saved value (ie. record not dirty). This SQL statement also assumes
that EventRef is numeric; if EventRef is text, then you need to wrap it in
quotes, like this:

strSQL = "SELECT tblAttendees.UCLANEmail " _
& "From tblAttendees " _
& "WHERE (((tblAttendees.EventRef)= " & Chr(34) _
& [Forms]![frmBookings]![EventRef] & Chr(34) _
& " AND ((tblAttendees.Email)=Yes));"


If the resulting SQL statement that is printed to the immediate window looks
okay (ie. proper spacing between SQL keywords), then try copying it to the
Windows clipboard, and pasting it into the SQL view of a new query.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

As I can't write SQL, I use the query builder to build a query, then show me
the SQL version and then I copy and paste this into my Access VB to define my
recordsets.

This usally works fine, but on this occassion I've spent ages trying to get
it right and can't.

My application books people onto courses and sends them an email if a
certain box is ticked.

The form "Bookings" has a sub form "Attendees", so I can scroll through each
record in "Bookings" and see which "Attendees" are due to attend the course
and if their box is ticked include them in the recordset for the email.

The direct copy from the SQL design is:-

SELECT tblAttendees.UCLANEmail
FROM tblAttendees
WHERE (((tblAttendees.EventRef)=[Forms]![frmBookings]![EventRef]) AND
((tblAttendees.Email)=Yes));

If for example I want to get a recordset for course number 1, the following
SQL works fine:

SELECT tblAttendees.UCLANEmail
FROM tblAttendees
WHERE tblAttendees.EventRef = 1 AND (tblAttendees.Email = True)

but any attempt to substiute the "1" for the ref from the "bookings" form
results in a crash.

If anyone one could help, and show me where I'm going wrong I'd be very
grateful.

thanks

Winger

 

[Guest]

Tom,

I also tryed cutting and pasting your code into a blank query design, but as
it gave an error "missing operator". I put the code all into one line and it
ran the query fine, and picked up the correct info form frmBookings etc.

I put the one line into the SQL to generate the record set but get an error
message on the line following the definition of the SQL which is: -

Set rst = db.OpenRecordset(mySQL, dbOpenDynaset)

The error msg is: -
Too few parameters. Expected 1.

So still not sure what I'm doing wrong.

thanks

winger

Tom,

Thanks for you note.

frmBookings is open as this is where the control sits, and EventRef is
numeric.

I copied your code to replace my SQL, but when I try to run it I get a
syntax error that still shows the old code, even though I replaced it, and I
can't find it in the module anywhere!!

I reset the db each time it crashes, so do I need to reset or clear
something else? I've not seen something like that before.

thanks

Winger



Do I need to

Hi Winger,

Try printing the results of your select string to the immediate window,
which you can open with Ctrl G. For example:

Dim strSQL As String

strSQL = "SELECT tblAttendees.UCLANEmail " _
& "From tblAttendees " _
& "WHERE (((tblAttendees.EventRef)= " _
& [Forms]![frmBookings]![EventRef] _
& " AND ((tblAttendees.Email)=Yes));"

Debug.Print strSQL

Of course, frmBookings must be open, and the control named EventRef must
have a saved value (ie. record not dirty). This SQL statement also assumes
that EventRef is numeric; if EventRef is text, then you need to wrap it in
quotes, like this:

strSQL = "SELECT tblAttendees.UCLANEmail " _
& "From tblAttendees " _
& "WHERE (((tblAttendees.EventRef)= " & Chr(34) _
& [Forms]![frmBookings]![EventRef] & Chr(34) _
& " AND ((tblAttendees.Email)=Yes));"


If the resulting SQL statement that is printed to the immediate window looks
okay (ie. proper spacing between SQL keywords), then try copying it to the
Windows clipboard, and pasting it into the SQL view of a new query.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

As I can't write SQL, I use the query builder to build a query, then show me
the SQL version and then I copy and paste this into my Access VB to define my
recordsets.

This usally works fine, but on this occassion I've spent ages trying to get
it right and can't.

My application books people onto courses and sends them an email if a
certain box is ticked.

The form "Bookings" has a sub form "Attendees", so I can scroll through each
record in "Bookings" and see which "Attendees" are due to attend the course
and if their box is ticked include them in the recordset for the email.

The direct copy from the SQL design is:-

SELECT tblAttendees.UCLANEmail
FROM tblAttendees
WHERE (((tblAttendees.EventRef)=[Forms]![frmBookings]![EventRef]) AND
((tblAttendees.Email)=Yes));

If for example I want to get a recordset for course number 1, the following
SQL works fine:

SELECT tblAttendees.UCLANEmail
FROM tblAttendees
WHERE tblAttendees.EventRef = 1 AND (tblAttendees.Email = True)

but any attempt to substiute the "1" for the ref from the "bookings" form
results in a crash.

If anyone one could help, and show me where I'm going wrong I'd be very
grateful.

thanks

Winger

 

[Van T. Dinh]

I think Tom's SQL construction may have a typo with an extra ( just after
WHERE.

Try:

strSQL = "SELECT tblAttendees.UCLANEmail " & _
" FROM tblAttendees " & _
" WHERE (tblAttendees.EventRef= " & [Forms]![frmBookings]![EventRef] &
_
" ) AND (tblAttendees.Email=True)"

Use the Debug.Print as Tom advised to check the result of the SQL String
construction.

 

[Guest]

Try disabling the VBA Compile on Demand option, just in case this might be
the culprit. More information here (scroll down the page a little, but you
might as well make sure that you have require variable declaration set at the
same time):

http://www.access.qbuilt.com/html/VBEOptions

After disabling Compile on Demand, click on OK to dismiss the VBA Options
dialog. Then click on Debug > Compile ProjectName. Does your code compile
without any errors? If not, fix the indicated errors or comment out the
offending line(s) of code. Then try re-executing the code.

If you still see weird behavior, try closing Access. Then click on Start >
Run and enter:

msaccess /decompile

Navigate to your database. Hold down the shift key to disable any startup
code. Immediately perform a compact and repair operation (Tools > Database
Utilities > Compact and Repair Database).


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________