recordset opening query

[geomike]

Please help. I have a form that I had hoped would open a recordset, allow
the user to select the query results through option groups, and then display
the results or link them to a graphical display in a excel/word document. I
have been working on a very small part of the query before I go on to the
more complex sections. I have the sql string and the selection method works
in VB. However, I am having trouble with the rst.open statement. I believe
it has to do with the SQL string being in a variable, but I am not sure where
to go next. Thanks for the help.

vSelect = "Employees.LastName, tblOrg.Org"
vSelect = vSelect & ", Employees.Active"

vFrom = "Employees INNER JOIN tblORG ON "
vFrom = vFrom & "Employees.OrgID = tblOrg.OrgID"
'vFrom = "Employees LEFT JOIN tblJobTitle ON Employees.JobTitleID ="
'vFrom = vFrom & " tblJobTitle.JobTitleID"

Dim Code As String
If SelDept = 1 Then Code = "tblOrg.Org = N472"
If SelDept = 2 Then Code = "tblOrg.Org = N473"
If SelDept = 3 Then Code = "tblOrg.Org = N474"
If SelDept = 4 Then Code = "tblOrg.Org = N47S"
If SelDept = 5 Then Code = "tblOrg.Org = Like '*'"

vWhere = Code
vWhere = vWhere & " AND Employees.Active = -1"

strSQL = "SELECT " & vSelect & " FROM " & vFrom & " WHERE " & vWhere
MsgBox strSQL
rst.Open strSQL, Application.CurrentProject.Connection, adOpenKeyset,
adLockPessimistic

I should say that the variable strSQL is defined and appears exactly as I
expect it to. I think the problem is in the rst.open line. If you could
tell me how I could send the results of the recordset to the printer or the
screen, I would also be appreciative.

 

[smk23]

geomike:
Could I make the following suggestion as an alternate way to write your SQL
statement? This will be much easier to maintain and debug.
I suspect the problem is somewhere in your SQL statement.
The rs.Open arguments and syntax look fine to me assuming that
Application.CurrentProject.Connection is valid.

vSelect = "SELECT Employees.LastName, tblOrg.Org" & _
", Employees.Active" & _
" FROM (Employees INNER JOIN tblORG" & _
" ON Employees.OrgID = tblOrg.OrgID)" & _
" LEFT JOIN tblJobTitle" & _
" ON Employees.JobTitleID = tblJobTitle.JobTitleID"

Dim Code as String
Select Case SelDept
Case 1
Code = "tblOrg.Org = N472"
Case 2
Code = "tblOrg.Org = N473"
Case 3
Code = "tblOrg.Org = N474"
Case 4
Code = "tblOrg.Org = N475"
Case 5
Code = "tblOrg.Org = Like'*'"
End Select

vWhere = " WHERE " & Code & " AND Employees.Active = -1"

strSQL = vSelect & vWhere
MsgBox strSQL
rst.Open strSQL, Application.CurrentProject.Connection, adOpenKeyset,
adLockPessimistic

HTH
Sam

 

[geomike]

Thanks for the help. I have done as you suggested with a slight
modification. I removed the Left Join to JobTitle for now until I get at
least positive results. However, I am still receiving an error, (80040e10),
to few parameters. When I debug, it takes me to the line:

rst.Open strSQL, Application.CurrentProject.Connection, adOpenKeyset,
adLockPessimistic

I examined the SQL view of the query and used a msgbox to look at the value
of strSQL. The only difference I see is that N47S (alias CODE) has quotation
marks around it and when using the variable, those quotation marks do not
exist. Any suggestions?

Here is the code again, as it currently exists:

Dim count As Integer
Dim objVirtualRecord As VirtualRecord
Set objVirtualRecord = New VirtualRecord

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

Dim vSelect As String
Dim vWhere As String

vSelect = "SELECT Employees.LastName, tblOrg.Org" & _
", Employees.Active" & _
" FROM (Employees INNER JOIN tblOrg" & _
" ON Employees.OrgID = tblOrg.OrgID)"

Dim strSQL As String
Dim Code As String
Select Case Dept

Case 1
Code = "tblOrg.Org = N472"
Case 2
Code = "tblOrg.Org = N473"
Case 3
Code = "tblOrg.Org = N474"
Case 4
Code = "tblOrg.Org = N47S"
Case 5
Code = "tblOrg.Org = Like '*'"
End Select

vWhere = " WHERE " & Code & " AND Employees.Active = -1"
strSQL = vSelect & vWhere
MsgBox strSQL
rst.Open strSQL, Application.CurrentProject.Connection, adOpenKeyset,
adLockPessimistic

count = rst.RecordCount
MsgBox count

End Sub