Open recordset to 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.

 

[Tim Ferguson]

However, I am having trouble with the rst.open statement.

"I am having trouble" does not help anybody to know what is happening. What
trouble? Synax error? Runtime error? Wrong records? No records?...

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.

Use a report. Bear in mind that it will take some clever jiggery-pokery to
create a report that takes an arbitrary number and type of fields.

As a very ugly solution, you could create a QueryDef object and open in in
the UI. If the users have access to the Access menus, they can print out
onto paper. Not nice or user-friendly though.



HTH


Tim F