Building an SQL query

[Lionel Fridjhon]

I have a table tblInventory with 4 fields and I need to
extract 12 records into a temporary table that a report
has as a record source.

I have written the following code - the query is created
but comes up empty and the new table "tblTwoPages" is not
created:
========================================
Private Sub cmdRunReport1_Click()
Dim Response As Integer
Dim qdf As AccessObject

'delete old query and table , if they exist
On Error Resume Next
DoCmd.DeleteObject acQuery, "qryTwoPages"
DoCmd.DeleteObject acTable, "tblTwoPages"
'create query
Set qdf = CurrentDb.CreateQueryDef
("qryTwoPages")
qdf.SQL = "SELECT [tblInventory1].#, [
tblInventory1].Location, " _

& "[tblInventory1].Description, [tblInventory1].FileName"
_
& "INTO tblTwoPages " _
& "FROM [tblInventory1] " _
& "WHERE [tblInventory1].#
Between 1 And 12"
qdf.Execute
qdf.Close

DoCmd.OpenReport ("rptInventory1"),
acViewDesign
DoCmd.RunCommand acCmdPrint
DoCmd.Close acReport, "rptInventory1"
End Sub
===============================================

What am I doing wrong?

Lionel

 

[Marshall Barton]

I have a table tblInventory with 4 fields and I need to
extract 12 records into a temporary table that a report
has as a record source.

I have written the following code - the query is created
but comes up empty and the new table "tblTwoPages" is not
created:
========================================
Private Sub cmdRunReport1_Click()
Dim Response As Integer
Dim qdf As AccessObject

'delete old query and table , if they exist
On Error Resume Next
DoCmd.DeleteObject acQuery, "qryTwoPages"
DoCmd.DeleteObject acTable, "tblTwoPages"
'create query
Set qdf = CurrentDb.CreateQueryDef
("qryTwoPages")
qdf.SQL = "SELECT [tblInventory1].#, [
tblInventory1].Location, " _

& "[tblInventory1].Description, [tblInventory1].FileName"
_
& "INTO tblTwoPages " _
& "FROM [tblInventory1] " _
& "WHERE [tblInventory1].#
Between 1 And 12"
qdf.Execute
qdf.Close

DoCmd.OpenReport ("rptInventory1"),
acViewDesign
DoCmd.RunCommand acCmdPrint
DoCmd.Close acReport, "rptInventory1"
End Sub

The above code uses # as a field name. You can not use a
name with spaces or other funky characters unless you
enclose it in square brackets - [tblInventory1].[#] but
that is going to be very misleading somewhere along the road
and you should definitely change it to something else such
as InvNum.

Further, you probably would have spotted this (or other
errors) if you had not disabled error trapping. If you
think you can get away without real error trapping code, you
should at least re-enable Access' default error handling by
using On Error GoTo 0 immediately after the DeleteObjects.
Much better to use On Error GoTo yourownerrorhandler with
you own custom error handling code.

FYI, because of bloat and stability issues, it is not
recommended that you delete and recreate objects like that.
If you must approach things this way, try to reuse the table
by just deleting its records instead. You can set the
querydef's SQL property instead of deleting and recreating
the entire querydef.

Another point is that you should normally use the Execute
method's dbFailOnError argument so you can trap any failures
when the query runs.

OTOH, I don't see any reason to go through all this just to
filter the report's data source. Why not just set the
report's RecordSource to tblInventory1 and filter it using
the OpenReport method's WhereCondition argument??
Alternatively, if I missed the reason for all this, you
could use the report's Open event to assign the SQL directly
to the report's RecordSource property.

 

[Tammy]

I am responding to your comments below asking why this
process is necessary. I am relatively new to reports/vba
and am wondering what would be the best way to handle my
situation. The user fills out a form to create a
newsletter for his clients. In certain areas there is
the possibility of creating/entering "personal" remarks.
Once he has filled out the form we have a list box where
he selects which clients will be sent the newsletter, I
have it set up so that he double clicks on a user in
order to enter the personal data which is then saved to a
separate table. I need to be able to create the report
after selecting multiple clients from the list. I have
the "for each varItem" code set up, but I am a bit
confused as to how to ensure I am creating a report for
each selection without printing after each individual.
Also, I need to be able to use the personalized
information on the occassion that one of the clients has
an entry in the separate table. Hope this makes sense!!!
We have one table (the main one for the report) which
contains the basic newsletter information and one which
contains persoanlized information according to a
customerid and recordid from the main table. I was going
to build a new table with each user name for the
recordsource and then use the filter for the rest.
SUGGESTIONS!?

-----Original Message-----

I have a table tblInventory with 4 fields and I need to
extract 12 records into a temporary table that a report
has as a record source.

I have written the following code - the query is created
but comes up empty and the new table "tblTwoPages" is not
created:
========================================
Private Sub cmdRunReport1_Click()
Dim Response As Integer
Dim qdf As AccessObject

'delete old query and table , if they exist
On Error Resume Next
DoCmd.DeleteObject acQuery, "qryTwoPages"
DoCmd.DeleteObject acTable, "tblTwoPages"
'create query
Set qdf = CurrentDb.CreateQueryDef
("qryTwoPages")
qdf.SQL = "SELECT [tblInventory1].#, [
tblInventory1].Location, " _

& "[tblInventory1].Description, [tblInventory1].FileName"
_
& "INTO tblTwoPages " _
& "FROM [tblInventory1] " _
& "WHERE [tblInventory1].#
Between 1 And 12"
qdf.Execute
qdf.Close

DoCmd.OpenReport ("rptInventory1"),
acViewDesign
DoCmd.RunCommand acCmdPrint
DoCmd.Close acReport, "rptInventory1"
End Sub

The above code uses # as a field name. You can not use a
name with spaces or other funky characters unless you
enclose it in square brackets - [tblInventory1].[#] but
that is going to be very misleading somewhere along the road
and you should definitely change it to something else such
as InvNum.

Further, you probably would have spotted this (or other
errors) if you had not disabled error trapping. If you
think you can get away without real error trapping code, you
should at least re-enable Access' default error handling by
using On Error GoTo 0 immediately after the DeleteObjects.
Much better to use On Error GoTo yourownerrorhandler with
you own custom error handling code.

FYI, because of bloat and stability issues, it is not
recommended that you delete and recreate objects like that.
If you must approach things this way, try to reuse the table
by just deleting its records instead. You can set the
querydef's SQL property instead of deleting and recreating
the entire querydef.

Another point is that you should normally use the Execute
method's dbFailOnError argument so you can trap any failures
when the query runs.

OTOH, I don't see any reason to go through all this just to
filter the report's data source. Why not just set the
report's RecordSource to tblInventory1 and filter it using
the OpenReport method's WhereCondition argument??
Alternatively, if I missed the reason for all this, you
could use the report's Open event to assign the SQL directly
to the report's RecordSource property.

 

[Marshall Barton]

I am responding to your comments below asking why this
process is necessary. I am relatively new to reports/vba
and am wondering what would be the best way to handle my
situation. The user fills out a form to create a
newsletter for his clients. In certain areas there is
the possibility of creating/entering "personal" remarks.
Once he has filled out the form we have a list box where
he selects which clients will be sent the newsletter, I
have it set up so that he double clicks on a user in
order to enter the personal data which is then saved to a
separate table. I need to be able to create the report
after selecting multiple clients from the list. I have
the "for each varItem" code set up, but I am a bit
confused as to how to ensure I am creating a report for
each selection without printing after each individual.

Before you worry about the report, make sure you have
created a query (for the report) that contains the data you
want to see in the report. See if you can get that put
together before worrying about how to filter the data to
just the people sselected in the list.

Also, I need to be able to use the personalized
information on the occassion that one of the clients has
an entry in the separate table. Hope this makes sense!!!
We have one table (the main one for the report) which
contains the basic newsletter information and one which
contains persoanlized information according to a
customerid and recordid from the main table. I was going
to build a new table with each user name for the
recordsource and then use the filter for the rest.
SUGGESTIONS!?

I doubt that you need to build another table just for the
report. A properly constructed query is almost always the
right way to go.
--
Marsh
MVP [MS Access]

-----Original Message-----

I have a table tblInventory with 4 fields and I need to
extract 12 records into a temporary table that a report
has as a record source.

I have written the following code - the query is created
but comes up empty and the new table "tblTwoPages" is not
created:
========================================
Private Sub cmdRunReport1_Click()
Dim Response As Integer
Dim qdf As AccessObject

'delete old query and table , if they exist
On Error Resume Next
DoCmd.DeleteObject acQuery, "qryTwoPages"
DoCmd.DeleteObject acTable, "tblTwoPages"
'create query
Set qdf = CurrentDb.CreateQueryDef
("qryTwoPages")
qdf.SQL = "SELECT [tblInventory1].#, [
tblInventory1].Location, " _

& "[tblInventory1].Description, [tblInventory1].FileName"
_
& "INTO tblTwoPages " _
& "FROM [tblInventory1] " _
& "WHERE [tblInventory1].#
Between 1 And 12"
qdf.Execute
qdf.Close

DoCmd.OpenReport ("rptInventory1"),
acViewDesign
DoCmd.RunCommand acCmdPrint
DoCmd.Close acReport, "rptInventory1"
End Sub

The above code uses # as a field name. You can not use a
name with spaces or other funky characters unless you
enclose it in square brackets - [tblInventory1].[#] but
that is going to be very misleading somewhere along the road
and you should definitely change it to something else such
as InvNum.

Further, you probably would have spotted this (or other
errors) if you had not disabled error trapping. If you
think you can get away without real error trapping code, you
should at least re-enable Access' default error handling by
using On Error GoTo 0 immediately after the DeleteObjects.
Much better to use On Error GoTo yourownerrorhandler with
you own custom error handling code.

FYI, because of bloat and stability issues, it is not
recommended that you delete and recreate objects like that.
If you must approach things this way, try to reuse the table
by just deleting its records instead. You can set the
querydef's SQL property instead of deleting and recreating
the entire querydef.

Another point is that you should normally use the Execute
method's dbFailOnError argument so you can trap any failures
when the query runs.

OTOH, I don't see any reason to go through all this just to
filter the report's data source. Why not just set the
report's RecordSource to tblInventory1 and filter it using
the OpenReport method's WhereCondition argument??
Alternatively, if I missed the reason for all this, you
could use the report's Open event to assign the SQL directly
to the report's RecordSource property.