Using SQL in Sub to fill box on mouseclick

  • Thread starter Thread starter JayVZ
  • Start date Start date
J

JayVZ

In the form that I am working on, I have a number of text boxes that are
filled with a query telling me which people are scheduled to work a shift. I
also have a text box (I may need to make this a sub form, but would prefer
not) where I want a list of all available employees for the specific shift
to show. When I click, I want to see the list, so that I can make changes if
needed and only chose those employees that are available for the shift.

I have:
Private Sub Ctl8a_4pb_Click()
Me!txtAvailEmployees = "SELECT tblAvailabilities.Date, [First]+" "+[Last] AS
[Full Name]
FROM tblAvailabilities INNER JOIN tblStaff ON tblAvailabilities.EmployeeNum
= tblStaff.EmployeeID
GROUP BY tblAvailabilities.Date, [First]+" "+[Last],
tblAvailabilities.Shift1
HAVING (((tblAvailabilities.Shift1)=-1))
WHERE tblAvailabilities.Date=[form]![frmScheduleEdit]![CalDate];"
End Sub

I get a syntax error. Is is even possible to pass SQL in a manner as this?
If it is, where would I be messing up?
 
Your SQL statement is incorrect.

The WHERE clause comes before the GROUP BY clause.

From the Help file:

SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [,
[table.]field2 [AS alias2] [, ...]]}
FROM tableexpression [, ...] [IN externaldatabase]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
 
In addition to Doug's point on the SQL syntax, your VB syntax is incorrect.
This is because the first " character in your SQL string will be seen by VBA
as the termination of the string, and everything after it will be junk.

To put a " character inside a string in VB(A) you must double it. This has
a special meaning and is not seen as the end of one string and the start of
a new one. For example:

strSQL = "SELECT tblAvailabilities.Date, [First]+"" ""+[Last] AS ...

Alternatively, SQL understands both single and double quotes, so you could
use:

strSQL = "SELECT tblAvailabilities.Date, [First]+' '+[Last] AS ...

However, the whole thing is flawed because what you will see in your textbox
is not a list of names, but your SQL statement (SELECT ...).

I think what you really want to do is use a listbox, not a textbox, and set
its RowSource to the SQL statement. Set the listbox's Columns property to
the number of visible columns in your query.
 
Thanks Douglas,

That didn't work, but I just created subforms that I make visible or not
depending on the clicks. It would be nice to do it in the text box, but I'm
not even sure that this is the right type of object.

The SQL still comes upon a syntax error, even with changing the order.

Thanks for the help
Jay
 
Thanks Graham,

I did see that and changed it to "" and ', still giving me an error. I will
try the listbox.

Jay

Graham Mandeno said:
In addition to Doug's point on the SQL syntax, your VB syntax is
incorrect. This is because the first " character in your SQL string will
be seen by VBA as the termination of the string, and everything after it
will be junk.

To put a " character inside a string in VB(A) you must double it. This
has a special meaning and is not seen as the end of one string and the
start of a new one. For example:

strSQL = "SELECT tblAvailabilities.Date, [First]+"" ""+[Last] AS ...

Alternatively, SQL understands both single and double quotes, so you could
use:

strSQL = "SELECT tblAvailabilities.Date, [First]+' '+[Last] AS ...

However, the whole thing is flawed because what you will see in your
textbox is not a list of names, but your SQL statement (SELECT ...).

I think what you really want to do is use a listbox, not a textbox, and
set its RowSource to the SQL statement. Set the listbox's Columns
property to the number of visible columns in your query.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

JayVZ said:
In the form that I am working on, I have a number of text boxes that are
filled with a query telling me which people are scheduled to work a
shift. I also have a text box (I may need to make this a sub form, but
would prefer not) where I want a list of all available employees for the
specific shift to show. When I click, I want to see the list, so that I
can make changes if needed and only chose those employees that are
available for the shift.

I have:
Private Sub Ctl8a_4pb_Click()
Me!txtAvailEmployees = "SELECT tblAvailabilities.Date, [First]+" "+[Last]
AS [Full Name]
FROM tblAvailabilities INNER JOIN tblStaff ON
tblAvailabilities.EmployeeNum = tblStaff.EmployeeID
GROUP BY tblAvailabilities.Date, [First]+" "+[Last],
tblAvailabilities.Shift1
HAVING (((tblAvailabilities.Shift1)=-1))
WHERE tblAvailabilities.Date=[form]![frmScheduleEdit]![CalDate];"
End Sub

I get a syntax error. Is is even possible to pass SQL in a manner as
this?
If it is, where would I be messing up?
Click to expand...
Click to expand...
 
Back
Top