Search form - SQL works but not in VB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I'm hoping someone can help as I am totally stumped but the code is quite
long (sorry!). I have a Search Form (unbound) with a large number of fields
(34 - I know it's a lot but totally necessary). I do have this in a
different database with different fields obviously and it works there
although the joins are different.

In SQL the query works just fine but when I copy it into VB and remove the ;
it doesn't work and I can't figure out why. It has some issue with the line
that begins with "FROM ((((..."

Any suggestions are totally appreciated!

Private Sub Search_Records_Click()

Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

strSQL = "SELECT Tbl_MAIN.[Record_ID], etc......"

"FROM ((((Tbl_Province INNER JOIN ((Tbl_MAIN LEFT JOIN
Subtbl_BC_Suspensions_MAIN ON Tbl_MAIN.[Record_ID] =
Subtbl_BC_Suspensions_MAIN.[RecordID]) LEFT JOIN Subtbl_D13_MAIN ON
Tbl_MAIN.[Record_ID] = Subtbl_D13_MAIN.[RecordID]) ON Tbl_Province.[Prov_ID]
= Tbl_MAIN.[Province]) LEFT JOIN Subtbl_Abandonments ON Tbl_MAIN.[Record_ID]
= Subtbl_Abandonments.[RecordID]) LEFT JOIN SubTbl_Casing_Failures_MAIN ON
Tbl_MAIN.[Record_ID] = SubTbl_Casing_Failures_MAIN.RecordID) LEFT JOIN
Subtbl_IPT_MAIN ON Tbl_MAIN.[Record_ID] = Subtbl_IPT_MAIN.[RecordID]) LEFT
JOIN ((Tbl_SCV_Type RIGHT JOIN Subtbl_SCVF_MAIN ON Tbl_SCV_Type.[SCVType_ID]
= Subtbl_SCVF_MAIN.[SCV_Type]) LEFT JOIN (Tbl_SCV_Class RIGHT JOIN
Subtbl_SCVF_Test ON Tbl_SCV_Class.[Class_ID] =
Subtbl_SCVF_Test.[Classification]) ON Subtbl_SCVF_MAIN.[SCVF_ID] =
Subtbl_SCVF_Test.[SCVF_ID]) ON Tbl_MAIN.[Record_ID] =
Subtbl_SCVF_MAIN.[RecordID]"

strWhere = "WHERE"

strOrder = "ORDER BY Tbl_MAIN.[Field_Location];"
 
Carrie said:
Hi, I'm hoping someone can help as I am totally stumped but the code is quite
long (sorry!). I have a Search Form (unbound) with a large number of fields
(34 - I know it's a lot but totally necessary). I do have this in a
different database with different fields obviously and it works there
although the joins are different.

In SQL the query works just fine but when I copy it into VB and remove the ;
it doesn't work and I can't figure out why. It has some issue with the line
that begins with "FROM ((((..."

Any suggestions are totally appreciated!

Private Sub Search_Records_Click()

Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

strSQL = "SELECT Tbl_MAIN.[Record_ID], etc......"

"FROM ((((Tbl_Province INNER JOIN ((Tbl_MAIN LEFT JOIN
Subtbl_BC_Suspensions_MAIN ON Tbl_MAIN.[Record_ID] =
Subtbl_BC_Suspensions_MAIN.[RecordID]) LEFT JOIN Subtbl_D13_MAIN ON
Tbl_MAIN.[Record_ID] = Subtbl_D13_MAIN.[RecordID]) ON Tbl_Province.[Prov_ID]
= Tbl_MAIN.[Province]) LEFT JOIN Subtbl_Abandonments ON Tbl_MAIN.[Record_ID]
= Subtbl_Abandonments.[RecordID]) LEFT JOIN SubTbl_Casing_Failures_MAIN ON
Tbl_MAIN.[Record_ID] = SubTbl_Casing_Failures_MAIN.RecordID) LEFT JOIN
Subtbl_IPT_MAIN ON Tbl_MAIN.[Record_ID] = Subtbl_IPT_MAIN.[RecordID]) LEFT
JOIN ((Tbl_SCV_Type RIGHT JOIN Subtbl_SCVF_MAIN ON Tbl_SCV_Type.[SCVType_ID]
= Subtbl_SCVF_MAIN.[SCV_Type]) LEFT JOIN (Tbl_SCV_Class RIGHT JOIN
Subtbl_SCVF_Test ON Tbl_SCV_Class.[Class_ID] =
Subtbl_SCVF_Test.[Classification]) ON Subtbl_SCVF_MAIN.[SCVF_ID] =
Subtbl_SCVF_Test.[SCVF_ID]) ON Tbl_MAIN.[Record_ID] =
Subtbl_SCVF_MAIN.[RecordID]"

strWhere = "WHERE"

strOrder = "ORDER BY Tbl_MAIN.[Field_Location];"
Click to expand...


That's not valid code. The line "FROM . . . is not part of
a VBA statement so Access has no idea what to do with it.
Normally, people break it up in a readable way using a style
something like:

strSQL = "SELECT Tbl_MAIN.[Record_ID], etc......" _
& "FROM ((((Tbl_Province " _
& "INNER JOIN ((Tbl_MAIN " _
& "LEFT JOIN Subtbl_BC_Suspensions_MAIN " _
& "ON Tbl_MAIN.[Record_ID] = " _
Subtbl_BC_Suspensions_MAIN.[RecordID]) _
& "LEFT JOIN Subtbl_D13_MAIN " _
& "ON Tbl_MAIN.[Record_ID] =
Subtbl_D13_MAIN.[RecordID])
& "ON Tbl_Province.[Prov_ID] . . .
 
Thanks Marshall foryour response. I did not know this and have SQL stmts in
my VBA code w/out _'s and &'s that works so my guess is I've been extremely
lucky until now.

I am trying to put in the _'s and &'s as you have indicated but am getting
error messages.

I kept getting an error "Compile Error: Expected End of Statement" at the
lines that start with for example:

Subtbl_BC_Suspensions_MAIN.[RecordID]

Which in your response does not have a & " at the beginning. I decided to
try putting the & " in but now I get an error way down the page at

& "ON Tbl_SCV_Class.[Class_ID] = "

when I try to put the _ at the end, I get the error "Too many line
continuations". Here's a copy of the beginning of my code - have I made a
mistake somewhere? Or, is this just too big? If so, should I somehow be
splitting it up?

& "FROM ((((Tbl_Province " _
& "INNER JOIN((Tbl_MAIN " _
& "LEFT JOIN Subtbl_BC_Suspensions_MAIN " _
& "ON Tbl_MAIN.[Record_ID] = " _
& "Subtbl_BC_Suspensions_MAIN.[RecordID])" _
& "LEFT JOIN Subtbl_D13_MAIN " _
& "ON Tbl_MAIN.[Record_ID] =
Subtbl_D13_MAIN.[RecordID])" _


Thanks Again!!!!!

Marshall Barton said:
Carrie said:
Hi, I'm hoping someone can help as I am totally stumped but the code is quite
long (sorry!). I have a Search Form (unbound) with a large number of fields
(34 - I know it's a lot but totally necessary). I do have this in a
different database with different fields obviously and it works there
although the joins are different.

In SQL the query works just fine but when I copy it into VB and remove the ;
it doesn't work and I can't figure out why. It has some issue with the line
that begins with "FROM ((((..."

Any suggestions are totally appreciated!

Private Sub Search_Records_Click()

Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

strSQL = "SELECT Tbl_MAIN.[Record_ID], etc......"

"FROM ((((Tbl_Province INNER JOIN ((Tbl_MAIN LEFT JOIN
Subtbl_BC_Suspensions_MAIN ON Tbl_MAIN.[Record_ID] =
Subtbl_BC_Suspensions_MAIN.[RecordID]) LEFT JOIN Subtbl_D13_MAIN ON
Tbl_MAIN.[Record_ID] = Subtbl_D13_MAIN.[RecordID]) ON Tbl_Province.[Prov_ID]
= Tbl_MAIN.[Province]) LEFT JOIN Subtbl_Abandonments ON Tbl_MAIN.[Record_ID]
= Subtbl_Abandonments.[RecordID]) LEFT JOIN SubTbl_Casing_Failures_MAIN ON
Tbl_MAIN.[Record_ID] = SubTbl_Casing_Failures_MAIN.RecordID) LEFT JOIN
Subtbl_IPT_MAIN ON Tbl_MAIN.[Record_ID] = Subtbl_IPT_MAIN.[RecordID]) LEFT
JOIN ((Tbl_SCV_Type RIGHT JOIN Subtbl_SCVF_MAIN ON Tbl_SCV_Type.[SCVType_ID]
= Subtbl_SCVF_MAIN.[SCV_Type]) LEFT JOIN (Tbl_SCV_Class RIGHT JOIN
Subtbl_SCVF_Test ON Tbl_SCV_Class.[Class_ID] =
Subtbl_SCVF_Test.[Classification]) ON Subtbl_SCVF_MAIN.[SCVF_ID] =
Subtbl_SCVF_Test.[SCVF_ID]) ON Tbl_MAIN.[Record_ID] =
Subtbl_SCVF_MAIN.[RecordID]"

strWhere = "WHERE"

strOrder = "ORDER BY Tbl_MAIN.[Field_Location];"
Click to expand...


That's not valid code. The line "FROM . . . is not part of
a VBA statement so Access has no idea what to do with it.
Normally, people break it up in a readable way using a style
something like:

strSQL = "SELECT Tbl_MAIN.[Record_ID], etc......" _
& "FROM ((((Tbl_Province " _
& "INNER JOIN ((Tbl_MAIN " _
& "LEFT JOIN Subtbl_BC_Suspensions_MAIN " _
& "ON Tbl_MAIN.[Record_ID] = " _
Subtbl_BC_Suspensions_MAIN.[RecordID]) _
& "LEFT JOIN Subtbl_D13_MAIN " _
& "ON Tbl_MAIN.[Record_ID] =
Subtbl_D13_MAIN.[RecordID])
& "ON Tbl_Province.[Prov_ID] . . .
Click to expand...
 
Ah Marshall - I should have done a search on line continuations before I
replied. I now understand that you are limited in the number and I just
popped a few on to one line and it works like a charm. Eternally
Grateful!!!!!

Carrie said:
Thanks Marshall foryour response. I did not know this and have SQL stmts in
my VBA code w/out _'s and &'s that works so my guess is I've been extremely
lucky until now.

I am trying to put in the _'s and &'s as you have indicated but am getting
error messages.

I kept getting an error "Compile Error: Expected End of Statement" at the
lines that start with for example:

Subtbl_BC_Suspensions_MAIN.[RecordID]

Which in your response does not have a & " at the beginning. I decided to
try putting the & " in but now I get an error way down the page at

& "ON Tbl_SCV_Class.[Class_ID] = "

when I try to put the _ at the end, I get the error "Too many line
continuations". Here's a copy of the beginning of my code - have I made a
mistake somewhere? Or, is this just too big? If so, should I somehow be
splitting it up?

& "FROM ((((Tbl_Province " _
& "INNER JOIN((Tbl_MAIN " _
& "LEFT JOIN Subtbl_BC_Suspensions_MAIN " _
& "ON Tbl_MAIN.[Record_ID] = " _
& "Subtbl_BC_Suspensions_MAIN.[RecordID])" _
& "LEFT JOIN Subtbl_D13_MAIN " _
& "ON Tbl_MAIN.[Record_ID] =
Subtbl_D13_MAIN.[RecordID])" _


Thanks Again!!!!!

Marshall Barton said:
Carrie said:
Hi, I'm hoping someone can help as I am totally stumped but the code is quite
long (sorry!). I have a Search Form (unbound) with a large number of fields
(34 - I know it's a lot but totally necessary). I do have this in a
different database with different fields obviously and it works there
although the joins are different.

In SQL the query works just fine but when I copy it into VB and remove the ;
it doesn't work and I can't figure out why. It has some issue with the line
that begins with "FROM ((((..."

Any suggestions are totally appreciated!

Private Sub Search_Records_Click()

Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

strSQL = "SELECT Tbl_MAIN.[Record_ID], etc......"

"FROM ((((Tbl_Province INNER JOIN ((Tbl_MAIN LEFT JOIN
Subtbl_BC_Suspensions_MAIN ON Tbl_MAIN.[Record_ID] =
Subtbl_BC_Suspensions_MAIN.[RecordID]) LEFT JOIN Subtbl_D13_MAIN ON
Tbl_MAIN.[Record_ID] = Subtbl_D13_MAIN.[RecordID]) ON Tbl_Province.[Prov_ID]
= Tbl_MAIN.[Province]) LEFT JOIN Subtbl_Abandonments ON Tbl_MAIN.[Record_ID]
= Subtbl_Abandonments.[RecordID]) LEFT JOIN SubTbl_Casing_Failures_MAIN ON
Tbl_MAIN.[Record_ID] = SubTbl_Casing_Failures_MAIN.RecordID) LEFT JOIN
Subtbl_IPT_MAIN ON Tbl_MAIN.[Record_ID] = Subtbl_IPT_MAIN.[RecordID]) LEFT
JOIN ((Tbl_SCV_Type RIGHT JOIN Subtbl_SCVF_MAIN ON Tbl_SCV_Type.[SCVType_ID]
= Subtbl_SCVF_MAIN.[SCV_Type]) LEFT JOIN (Tbl_SCV_Class RIGHT JOIN
Subtbl_SCVF_Test ON Tbl_SCV_Class.[Class_ID] =
Subtbl_SCVF_Test.[Classification]) ON Subtbl_SCVF_MAIN.[SCVF_ID] =
Subtbl_SCVF_Test.[SCVF_ID]) ON Tbl_MAIN.[Record_ID] =
Subtbl_SCVF_MAIN.[RecordID]"

strWhere = "WHERE"

strOrder = "ORDER BY Tbl_MAIN.[Field_Location];"
Click to expand...


That's not valid code. The line "FROM . . . is not part of
a VBA statement so Access has no idea what to do with it.
Normally, people break it up in a readable way using a style
something like:

strSQL = "SELECT Tbl_MAIN.[Record_ID], etc......" _
& "FROM ((((Tbl_Province " _
& "INNER JOIN ((Tbl_MAIN " _
& "LEFT JOIN Subtbl_BC_Suspensions_MAIN " _
& "ON Tbl_MAIN.[Record_ID] = " _
Subtbl_BC_Suspensions_MAIN.[RecordID]) _
& "LEFT JOIN Subtbl_D13_MAIN " _
& "ON Tbl_MAIN.[Record_ID] =
Subtbl_D13_MAIN.[RecordID])
& "ON Tbl_Province.[Prov_ID] . . .
Click to expand...
Click to expand...
 
Back
Top