creating a report from a recordset

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

Guest

I need help please. I have a form that allows the user, manager, to select a
dept and geographic region which then sets a variable strSQl to create a
recordset in adodb. I can verify that I am getting the correct results, but
I do not know how to send this data to a report so the data can be viewed or
printed. The user creates the query and therefore the recordset. I don't
really know where to go next. Please point me in the right direction.
 
You can modify the SQL of a saved query (I do this with DAO) and base your
report on the saved query.

CurrentDb.QueryDefs("qselMyReportQuery").SQL = strSQL
 
Thank you for your reply. I apologize for the delay in responding. I work
on a militayr base and someone has deemed it okay to see the communities, but
I can no longer reply to them.

If possible, can you explain this better. I am slow sometimes to catch new
ideas.
My code looks something like this:

dim rst as adodb.recordset
dim strSQL as string

here is some code that is manipulated by the user through option groups and
variables. Ultimately it builds the variable strSQL.

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 created a qry47DNC as you suggested and a report rpt47DNC.

What do I do now, because I do not quite get what you mean? If my rst is
defined in adodb, do I need to do something else? I do have the DAO library,
at least I believe it is part of this database.

Hope this is clear. Thanks again.
 
After the msgbox code
MsgBox strSQL
CurrentDb.QueryDefs("qry47DNC").SQL = strSQL
If your report is based on qry47DNC then it will show the records from your
strSQL.
 
Duane,

Thanks, this works perfectly. In fact the director was impressed and had me
show his boss. I have one small problem that I was hoping you might be able
to look at and recognize quickly. I have these two queries being generated
to run two similar reports. One runs perfectly as I said. The other will
run when I remove one table from the inner join, so I know it is the problem,
but I can't see what the problem is. I should say that other queries and
reports run off this table just fine. I believe the problem is in the VB
coding and translation to sql or visa versa.

This code works and is generated by the user through option boxes and
displayed in a msgbox as the variable strSQL:

SELECT Employees.LastName, Employees.FirstName, Employees.OrgUnit,
Employees.POSH, Employees.StarCS, tblOrg.Org, tblSubRegion.SubRegion,
Employees.ATAwareness, Employees.ComputerInfoSec, Employees.Active

FROM ((Employees INNER JOIN tblOrg ON Employees.OrgID = tblOrg.OrgID) INNER
JOIN tblSubRegion ON Employees.SubRegionID = tblSubRegion.SubRegionID)

WHERE tblOrg.Org = 'N472' AND Employees.Active = -1 AND
[tblSubRegion].SubRegion = 'Smokey Point' ORDER BY [OrgUnit],[LastName]

This is the code for the one that works incorrectly. The added table is the
DeficiencyReportInput. The error received is to few parameters.

SELECT Employees.LastName, Employees.FirstName, Employees.OrgUnit,
tblOrg.Org, tblSubRegion.SubRegion, Employees.Active,
DeficiencyReportInput.Title, DeficiencyReportInput.RequiredByDate

FROM (((Employees INNER JOIN tblOrg ON Employees.OrgID = tblOrg.OrgID) INNER
JOIN tblSubRegion ON Employees.SubRegionID = tblSubRegion.SubRegionID) INNER
JOIN DeficiencyReportInput ON Employees.LastName =
DeficiencyReportInput.[LastName])

WHERE tblOrg.Org = 'N472' AND Employees.Active = -1 AND
[tblSubRegion].SubRegion = 'Smokey Point' ORDER BY [OrgUnit],[LastName]

As I said, it is only when I add the Inner Join to the last table that I
receive the error. If you could kindly point me in the right direction I
would be grateful.

Thanks,
Mike
 
To trouble shoot these issues, I paste the SQL into a query and play with
it. I can't do that with your tables. I remove parts of the criteria and
change joins to LEFT or RIGHT JOINS.
BTW: I would never create a join on a last name field.

--
Duane Hookom
MS Access MVP


geomike said:
Duane,

Thanks, this works perfectly. In fact the director was impressed and had
me
show his boss. I have one small problem that I was hoping you might be
able
to look at and recognize quickly. I have these two queries being
generated
to run two similar reports. One runs perfectly as I said. The other will
run when I remove one table from the inner join, so I know it is the
problem,
but I can't see what the problem is. I should say that other queries and
reports run off this table just fine. I believe the problem is in the VB
coding and translation to sql or visa versa.

This code works and is generated by the user through option boxes and
displayed in a msgbox as the variable strSQL:

SELECT Employees.LastName, Employees.FirstName, Employees.OrgUnit,
Employees.POSH, Employees.StarCS, tblOrg.Org, tblSubRegion.SubRegion,
Employees.ATAwareness, Employees.ComputerInfoSec, Employees.Active

FROM ((Employees INNER JOIN tblOrg ON Employees.OrgID = tblOrg.OrgID)
INNER
JOIN tblSubRegion ON Employees.SubRegionID = tblSubRegion.SubRegionID)

WHERE tblOrg.Org = 'N472' AND Employees.Active = -1 AND
[tblSubRegion].SubRegion = 'Smokey Point' ORDER BY [OrgUnit],[LastName]

This is the code for the one that works incorrectly. The added table is
the
DeficiencyReportInput. The error received is to few parameters.

SELECT Employees.LastName, Employees.FirstName, Employees.OrgUnit,
tblOrg.Org, tblSubRegion.SubRegion, Employees.Active,
DeficiencyReportInput.Title, DeficiencyReportInput.RequiredByDate

FROM (((Employees INNER JOIN tblOrg ON Employees.OrgID = tblOrg.OrgID)
INNER
JOIN tblSubRegion ON Employees.SubRegionID = tblSubRegion.SubRegionID)
INNER
JOIN DeficiencyReportInput ON Employees.LastName =
DeficiencyReportInput.[LastName])

WHERE tblOrg.Org = 'N472' AND Employees.Active = -1 AND
[tblSubRegion].SubRegion = 'Smokey Point' ORDER BY [OrgUnit],[LastName]

As I said, it is only when I add the Inner Join to the last table that I
receive the error. If you could kindly point me in the right direction I
would be grateful.

Thanks,
Mike


Duane Hookom said:
After the msgbox code
MsgBox strSQL
CurrentDb.QueryDefs("qry47DNC").SQL = strSQL
If your report is based on qry47DNC then it will show the records from
your
strSQL.
Click to expand...
Click to expand...
 
Thanks. I know about the LastName field. Actually, he uses a combination of
LastName and FirstName, still with 1100 employees, we have 6 sets that have
the same first and last names. The source file uses a completely different
ID code as they are a contractor and we are just a small part of their
business. I'll try it tomorrow and let you know how it works. I did try
removing the Deficiency table and wouldn't you know, everything works great.
Thanks again,
Mike

Duane Hookom said:
To trouble shoot these issues, I paste the SQL into a query and play with
it. I can't do that with your tables. I remove parts of the criteria and
change joins to LEFT or RIGHT JOINS.
BTW: I would never create a join on a last name field.

--
Duane Hookom
MS Access MVP


geomike said:
Duane,

Thanks, this works perfectly. In fact the director was impressed and had
me
show his boss. I have one small problem that I was hoping you might be
able
to look at and recognize quickly. I have these two queries being
generated
to run two similar reports. One runs perfectly as I said. The other will
run when I remove one table from the inner join, so I know it is the
problem,
but I can't see what the problem is. I should say that other queries and
reports run off this table just fine. I believe the problem is in the VB
coding and translation to sql or visa versa.

This code works and is generated by the user through option boxes and
displayed in a msgbox as the variable strSQL:

SELECT Employees.LastName, Employees.FirstName, Employees.OrgUnit,
Employees.POSH, Employees.StarCS, tblOrg.Org, tblSubRegion.SubRegion,
Employees.ATAwareness, Employees.ComputerInfoSec, Employees.Active

FROM ((Employees INNER JOIN tblOrg ON Employees.OrgID = tblOrg.OrgID)
INNER
JOIN tblSubRegion ON Employees.SubRegionID = tblSubRegion.SubRegionID)

WHERE tblOrg.Org = 'N472' AND Employees.Active = -1 AND
[tblSubRegion].SubRegion = 'Smokey Point' ORDER BY [OrgUnit],[LastName]

This is the code for the one that works incorrectly. The added table is
the
DeficiencyReportInput. The error received is to few parameters.

SELECT Employees.LastName, Employees.FirstName, Employees.OrgUnit,
tblOrg.Org, tblSubRegion.SubRegion, Employees.Active,
DeficiencyReportInput.Title, DeficiencyReportInput.RequiredByDate

FROM (((Employees INNER JOIN tblOrg ON Employees.OrgID = tblOrg.OrgID)
INNER
JOIN tblSubRegion ON Employees.SubRegionID = tblSubRegion.SubRegionID)
INNER
JOIN DeficiencyReportInput ON Employees.LastName =
DeficiencyReportInput.[LastName])

WHERE tblOrg.Org = 'N472' AND Employees.Active = -1 AND
[tblSubRegion].SubRegion = 'Smokey Point' ORDER BY [OrgUnit],[LastName]

As I said, it is only when I add the Inner Join to the last table that I
receive the error. If you could kindly point me in the right direction I
would be grateful.

Thanks,
Mike


Duane Hookom said:
After the msgbox code
MsgBox strSQL
CurrentDb.QueryDefs("qry47DNC").SQL = strSQL
If your report is based on qry47DNC then it will show the records from
your
strSQL.


--
Duane Hookom
MS Access MVP


Thank you for your reply. I apologize for the delay in responding. I
work
on a militayr base and someone has deemed it okay to see the
communities,
but
I can no longer reply to them.

If possible, can you explain this better. I am slow sometimes to catch
new
ideas.
My code looks something like this:

dim rst as adodb.recordset
dim strSQL as string

here is some code that is manipulated by the user through option groups
and
variables. Ultimately it builds the variable strSQL.

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 created a qry47DNC as you suggested and a report rpt47DNC.

What do I do now, because I do not quite get what you mean? If my rst
is
defined in adodb, do I need to do something else? I do have the DAO
library,
at least I believe it is part of this database.

Hope this is clear. Thanks again.

:

You can modify the SQL of a saved query (I do this with DAO) and base
your
report on the saved query.

CurrentDb.QueryDefs("qselMyReportQuery").SQL = strSQL

--
Duane Hookom
MS Access MVP
--

I need help please. I have a form that allows the user, manager, to
select
a
dept and geographic region which then sets a variable strSQl to
create
a
recordset in adodb. I can verify that I am getting the correct
results,
but
I do not know how to send this data to a report so the data can be
viewed
or
printed. The user creates the query and therefore the recordset. I
don't
really know where to go next. Please point me in the right
direction.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top