SQL Not Working

[Noemi]

Hi

I have the following sql in vba and it keeps getting the following error and
will not work.
Run-time error "3075", Syntax error in query expression '[tbl_Comments.[No]]'.

Any idea what i have done wrong

table is called Comments and fields are EnterDate, Comments, No
me!StaffID is a text box on a form

strWhere = "(tbl_Comments.[No])= " & Me!StaffID & ")"

DoCmd.RunSQL "SELECT [tbl_Comments.EnterDate], [tbl_Comments.Comments],
[tbl_Comments.[No]] " & _
"FROM [tbl_CasualsDetails] INNER JOIN [tbl_Comments] ON
[tbl_CasualsDetails.[No]] = [tbl_Comments.[No]]" & _
"WHERE " & strWhere & ""

Thanks
Noemi

 

[dymondjack]

Try changing this:

[tbl_Comments.[No]]

to this:

[tbl_Comments].[No]


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[Dirk Goldgar]

Hi

I have the following sql in vba and it keeps getting the following error
and
will not work.
Run-time error "3075", Syntax error in query expression
'[tbl_Comments.[No]]'.

Any idea what i have done wrong

table is called Comments and fields are EnterDate, Comments, No
me!StaffID is a text box on a form

strWhere = "(tbl_Comments.[No])= " & Me!StaffID & ")"

DoCmd.RunSQL "SELECT [tbl_Comments.EnterDate], [tbl_Comments.Comments],
[tbl_Comments.[No]] " & _
"FROM [tbl_CasualsDetails] INNER JOIN [tbl_Comments] ON
[tbl_CasualsDetails.[No]] = [tbl_Comments.[No]]" & _
"WHERE " & strWhere & ""

It's a case of bad bracketing, plus a missing space before the WHERE
keyword. Where you have

[tbl_CasualsDetails.[No]] = [tbl_Comments.[No]]" & _

.... you should have:

[tbl_CasualsDetails].[No] = [tbl_Comments].[No]" & _

However, so far as I can see in what you've posted, you don't need the
brackets at all, except maybe around the field name "No". Try this:

DoCmd.RunSQL _
"SELECT tbl_Comments.EnterDate, tbl_Comments.Comments,
tbl_Comments.[No] " & _
"FROM tbl_CasualsDetails INNER JOIN tbl_Comments " & _
"ON tbl_CasualsDetails.[No] = tbl_Comments.[No] " & _
"WHERE " & strWhere & ""

 

[Noemi]

Hi Jack

Now I get the following error

A RunSQL action requires an argument consisting of an SQL statement.

I really am not good with SQL as I dont fully understand how they work.

Thanks
Noemi

Try changing this:

[tbl_Comments.[No]]

to this:

[tbl_Comments].[No]


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Hi

I have the following sql in vba and it keeps getting the following error and
will not work.
Run-time error "3075", Syntax error in query expression '[tbl_Comments.[No]]'.

Any idea what i have done wrong

table is called Comments and fields are EnterDate, Comments, No
me!StaffID is a text box on a form

strWhere = "(tbl_Comments.[No])= " & Me!StaffID & ")"

DoCmd.RunSQL "SELECT [tbl_Comments.EnterDate], [tbl_Comments.Comments],
[tbl_Comments.[No]] " & _
"FROM [tbl_CasualsDetails] INNER JOIN [tbl_Comments] ON
[tbl_CasualsDetails.[No]] = [tbl_Comments.[No]]" & _
"WHERE " & strWhere & ""

Thanks
Noemi

 

[Dirk Goldgar]

DoCmd.RunSQL _
"SELECT tbl_Comments.EnterDate, tbl_Comments.Comments,
tbl_Comments.[No] " & _
"FROM tbl_CasualsDetails INNER JOIN tbl_Comments " & _
"ON tbl_CasualsDetails.[No] = tbl_Comments.[No] " & _
"WHERE " & strWhere & ""

Noemi -

Your repIy to Jack just called my attention to the fact that you're trying
to use RunSQL on a SELECT query. That doesn't work, as RunSQL is only to be
used with action queries, not SELECT queries.

What is it you want to do when you execute this query? Get at the values it
returns so that you can manipulate them in code? In that case, you'll need
to open a recordset on the query:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset( _
"SELECT " & _
"tbl_Comments.EnterDate, tbl_Comments.Comments, " & _
"tbl_Comments.[No] " & _
"FROM tbl_CasualsDetails INNER JOIN tbl_Comments " & _
"ON tbl_CasualsDetails.[No] = tbl_Comments.[No] " & _
"WHERE " & strWhere)

With rs

If Not .EOF Then

' ... in here, do something with the values of rs!EnterDate,
rs!Comments,
' and rs!No. If there could be multiple records returned, you'd
probably
' want to loop through the records in the recordset using the
..MoveNext
' method until rs.EOF = True.

End If
.Close
End With

 

[Noemi]

Hi Dirk

Following is what I am trying to do

I have a form which contains a combobox with names. Once a name has been
selected on another Form which has been attached as a sub form I have page
tabs and I need to get the comments field to populate with any comments that
is currently in the Comments table for the selected person. The way my tab is
set up will allow additional comments to be entered as a new record with a
date.


So it goes:
Form1 contains a tabcontrol with 2 pages & Form2 is located on page 2
including text boxes and combobox
Form2 contains tabcontrol which has 4 pages and page 1 has Comments table
which I only want comments shown for person select from combobox

I hope this makes sense and you can help me bring up the correct information
and allow data entry at the same time

Thanks
Noemi

DoCmd.RunSQL _
"SELECT tbl_Comments.EnterDate, tbl_Comments.Comments,
tbl_Comments.[No] " & _
"FROM tbl_CasualsDetails INNER JOIN tbl_Comments " & _
"ON tbl_CasualsDetails.[No] = tbl_Comments.[No] " & _
"WHERE " & strWhere & ""

Noemi -

Your repIy to Jack just called my attention to the fact that you're trying
to use RunSQL on a SELECT query. That doesn't work, as RunSQL is only to be
used with action queries, not SELECT queries.

What is it you want to do when you execute this query? Get at the values it
returns so that you can manipulate them in code? In that case, you'll need
to open a recordset on the query:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset( _
"SELECT " & _
"tbl_Comments.EnterDate, tbl_Comments.Comments, " & _
"tbl_Comments.[No] " & _
"FROM tbl_CasualsDetails INNER JOIN tbl_Comments " & _
"ON tbl_CasualsDetails.[No] = tbl_Comments.[No] " & _
"WHERE " & strWhere)

With rs

If Not .EOF Then

' ... in here, do something with the values of rs!EnterDate,
rs!Comments,
' and rs!No. If there could be multiple records returned, you'd
probably
' want to loop through the records in the recordset using the
..MoveNext
' method until rs.EOF = True.

End If
.Close
End With


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)