Performing a make table query from a form button

[Guest]

I have an existing access db (MS Access 2000) which has a form with subforms
which are buttons.

Someone else designed this and I have NEVER really been in here before, but
I can write a mean query in Access. Right now, the form presents them with
buttons. They press them and it runs a report in preview mode. We want this
to stay the same, however, we need a make-table query to run before this
report.

How do I insert the command to run the make table query before the command
to run and preview the report?

I've made all the modifications and tested and I just need to automate the
make-table.

 

[Guest]

Try

Docmd.OpenQuery "WriteHereTheQueryName"
Docmd.OpenReport "ReportName"

 

[Guest]

One more thing, you might want to remove the question you prompt with when
the query runs, in that case use

Docmd.SetWarnings False
Docmd.OpenQuery "Queryname"
Docmd.SetWarnings True

 

[Guest]

Open the form with the button where you want to run the make table query in
design view.
Right click on the button and select Properties from the menu.
Select the Events tab of the properties dialog and select the Click event.
Since it is running a report, it will probably say [Event Procedure].
Once you have positioned your cursor in the Click Event text box, you will
see an icon on the right with 3 dots ...
Click on the dots and it will open the VB editor to that procedure.
If it is running a report, you will see a line that is will start with:
Docmd.OpenReport .......
Put the command to run the make table query just before that line:
CurrentDb.Execute("YourQueryNameGoesHere"), dbFailOnError

That's it!

 

[Guest]

The Excute method is about 5 times faster than an OpenQuery.

 

[Guest]

I tried to find something about that with no success, why would that be?

 

[Guest]

Because the OpenQuery method has to pass everything through Access and the
Execute method goes directly to Jet.

I saw some discussion in one of these newsgroups some time back and was
curious about it so I ran some tests using both methods with the same data.
I was really suprised at the difference in execution speed.

 

[Guest]

WOW!! You guys (or girls) are GREAT!! The last one did in fact get me through
what I needed. Only issue now, is that it's giving me an error (message)
that the table already exists for the Make Table query. I'm looking for
something (setting?) that allows this to be created without a warning.
thoughts?

Open the form with the button where you want to run the make table query in
design view.
Right click on the button and select Properties from the menu.
Select the Events tab of the properties dialog and select the Click event.
Since it is running a report, it will probably say [Event Procedure].
Once you have positioned your cursor in the Click Event text box, you will
see an icon on the right with 3 dots ...
Click on the dots and it will open the VB editor to that procedure.
If it is running a report, you will see a line that is will start with:
Docmd.OpenReport .......
Put the command to run the make table query just before that line:
CurrentDb.Execute("YourQueryNameGoesHere"), dbFailOnError

That's it!

I have an existing access db (MS Access 2000) which has a form with subforms
which are buttons.

Someone else designed this and I have NEVER really been in here before, but
I can write a mean query in Access. Right now, the form presents them with
buttons. They press them and it runs a report in preview mode. We want this
to stay the same, however, we need a make-table query to run before this
report.

How do I insert the command to run the make table query before the command
to run and preview the report?

I've made all the modifications and tested and I just need to automate the
make-table.

 

[Guest]

There cannot be two like objects in the same mdb with the same name, so no
suppression of error messages or warnings is going to resolve this. There
are two ways to approach this.
One would be to use the DeleteObject method to delete the old table before
running the make table query to create a new one.
The other (the one I would use, I will explain why later) would be to delete
the data from the table and run an Append query rather than a Make Table
query to load the data. The reason is that a Make Table query uses the
default field length for text fields and guesses at what the data type should
be for numeric fields. This creates unneeded bloat, degrades performance,
and in rare cases can cause lose of decimals for numeric fields.

If I were doing it, I would open the table in design mode, set the field
properties approriately, and leave it in place. Then the sequence would be:

CurrentDb.Execute("DELETE * FROM mytable;"), dbFailOnError
CurrentDb.Execute("qappMyAppendQuery"), dbFailOnError

WOW!! You guys (or girls) are GREAT!! The last one did in fact get me through
what I needed. Only issue now, is that it's giving me an error (message)
that the table already exists for the Make Table query. I'm looking for
something (setting?) that allows this to be created without a warning.
thoughts?

Open the form with the button where you want to run the make table query in
design view.
Right click on the button and select Properties from the menu.
Select the Events tab of the properties dialog and select the Click event.
Since it is running a report, it will probably say [Event Procedure].
Once you have positioned your cursor in the Click Event text box, you will
see an icon on the right with 3 dots ...
Click on the dots and it will open the VB editor to that procedure.
If it is running a report, you will see a line that is will start with:
Docmd.OpenReport .......
Put the command to run the make table query just before that line:
CurrentDb.Execute("YourQueryNameGoesHere"), dbFailOnError

That's it!

I have an existing access db (MS Access 2000) which has a form with subforms
which are buttons.

Someone else designed this and I have NEVER really been in here before, but
I can write a mean query in Access. Right now, the form presents them with
buttons. They press them and it runs a report in preview mode. We want this
to stay the same, however, we need a make-table query to run before this
report.

How do I insert the command to run the make table query before the command
to run and preview the report?

I've made all the modifications and tested and I just need to automate the
make-table.

 

[Guest]

Thanks Klatuu, I'll give it a try.

 

[Guest]

Okay...makes perfect sense. I did that and it's giving me an error that an
action query cannot be used as a row source.

What I'm doing is running this first query to build a table that is used in
the report that is immediately called thereafter. When I do it manually, run
the "F1207" query to populate the "F1207_50s" table, then the "PM Report"
kicks off with this table "F1207_50s" as well as two others already in the
database.

The append works, when I change the query to do that but not off the button.

There cannot be two like objects in the same mdb with the same name, so no
suppression of error messages or warnings is going to resolve this. There
are two ways to approach this.
One would be to use the DeleteObject method to delete the old table before
running the make table query to create a new one.
The other (the one I would use, I will explain why later) would be to delete
the data from the table and run an Append query rather than a Make Table
query to load the data. The reason is that a Make Table query uses the
default field length for text fields and guesses at what the data type should
be for numeric fields. This creates unneeded bloat, degrades performance,
and in rare cases can cause lose of decimals for numeric fields.

If I were doing it, I would open the table in design mode, set the field
properties approriately, and leave it in place. Then the sequence would be:

CurrentDb.Execute("DELETE * FROM mytable;"), dbFailOnError
CurrentDb.Execute("qappMyAppendQuery"), dbFailOnError

WOW!! You guys (or girls) are GREAT!! The last one did in fact get me through
what I needed. Only issue now, is that it's giving me an error (message)
that the table already exists for the Make Table query. I'm looking for
something (setting?) that allows this to be created without a warning.
thoughts?

Open the form with the button where you want to run the make table query in
design view.
Right click on the button and select Properties from the menu.
Select the Events tab of the properties dialog and select the Click event.
Since it is running a report, it will probably say [Event Procedure].
Once you have positioned your cursor in the Click Event text box, you will
see an icon on the right with 3 dots ...
Click on the dots and it will open the VB editor to that procedure.
If it is running a report, you will see a line that is will start with:
Docmd.OpenReport .......
Put the command to run the make table query just before that line:
CurrentDb.Execute("YourQueryNameGoesHere"), dbFailOnError

That's it!

:

I have an existing access db (MS Access 2000) which has a form with subforms
which are buttons.

Someone else designed this and I have NEVER really been in here before, but
I can write a mean query in Access. Right now, the form presents them with
buttons. They press them and it runs a report in preview mode. We want this
to stay the same, however, we need a make-table query to run before this
report.

How do I insert the command to run the make table query before the command
to run and preview the report?

I've made all the modifications and tested and I just need to automate the
make-table.

 

[Guest]

My current code reads:

CurrentDb.Execute ("DELETE * FROM F1207_50s;"), dbFailOnError
CurrentDb.Execute ("qappF1207"), dbFailOnError

I wonder if the file name should be cleaned-up? No underscores?

There cannot be two like objects in the same mdb with the same name, so no
suppression of error messages or warnings is going to resolve this. There
are two ways to approach this.
One would be to use the DeleteObject method to delete the old table before
running the make table query to create a new one.
The other (the one I would use, I will explain why later) would be to delete
the data from the table and run an Append query rather than a Make Table
query to load the data. The reason is that a Make Table query uses the
default field length for text fields and guesses at what the data type should
be for numeric fields. This creates unneeded bloat, degrades performance,
and in rare cases can cause lose of decimals for numeric fields.

If I were doing it, I would open the table in design mode, set the field
properties approriately, and leave it in place. Then the sequence would be:

CurrentDb.Execute("DELETE * FROM mytable;"), dbFailOnError
CurrentDb.Execute("qappMyAppendQuery"), dbFailOnError

WOW!! You guys (or girls) are GREAT!! The last one did in fact get me through
what I needed. Only issue now, is that it's giving me an error (message)
that the table already exists for the Make Table query. I'm looking for
something (setting?) that allows this to be created without a warning.
thoughts?

Open the form with the button where you want to run the make table query in
design view.
Right click on the button and select Properties from the menu.
Select the Events tab of the properties dialog and select the Click event.
Since it is running a report, it will probably say [Event Procedure].
Once you have positioned your cursor in the Click Event text box, you will
see an icon on the right with 3 dots ...
Click on the dots and it will open the VB editor to that procedure.
If it is running a report, you will see a line that is will start with:
Docmd.OpenReport .......
Put the command to run the make table query just before that line:
CurrentDb.Execute("YourQueryNameGoesHere"), dbFailOnError

That's it!

:

I have an existing access db (MS Access 2000) which has a form with subforms
which are buttons.

Someone else designed this and I have NEVER really been in here before, but
I can write a mean query in Access. Right now, the form presents them with
buttons. They press them and it runs a report in preview mode. We want this
to stay the same, however, we need a make-table query to run before this
report.

How do I insert the command to run the make table query before the command
to run and preview the report?

I've made all the modifications and tested and I just need to automate the
make-table.

 

[Rob Oldfield]

I think you're probably setting the make table query as the row source
instead of the table that you just made.

Okay...makes perfect sense. I did that and it's giving me an error that an
action query cannot be used as a row source.

What I'm doing is running this first query to build a table that is used in
the report that is immediately called thereafter. When I do it manually, run
the "F1207" query to populate the "F1207_50s" table, then the "PM Report"
kicks off with this table "F1207_50s" as well as two others already in the
database.

The append works, when I change the query to do that but not off the button.

There cannot be two like objects in the same mdb with the same name, so no
suppression of error messages or warnings is going to resolve this. There
are two ways to approach this.
One would be to use the DeleteObject method to delete the old table before
running the make table query to create a new one.
The other (the one I would use, I will explain why later) would be to delete
the data from the table and run an Append query rather than a Make Table
query to load the data. The reason is that a Make Table query uses the
default field length for text fields and guesses at what the data type should
be for numeric fields. This creates unneeded bloat, degrades performance,
and in rare cases can cause lose of decimals for numeric fields.

If I were doing it, I would open the table in design mode, set the field
properties approriately, and leave it in place. Then the sequence would be:

CurrentDb.Execute("DELETE * FROM mytable;"), dbFailOnError
CurrentDb.Execute("qappMyAppendQuery"), dbFailOnError

WOW!! You guys (or girls) are GREAT!! The last one did in fact get me through
what I needed. Only issue now, is that it's giving me an error (message)
that the table already exists for the Make Table query. I'm looking for
something (setting?) that allows this to be created without a warning.
thoughts?

:

Open the form with the button where you want to run the make table query in
design view.
Right click on the button and select Properties from the menu.
Select the Events tab of the properties dialog and select the Click event.
Since it is running a report, it will probably say [Event Procedure].
Once you have positioned your cursor in the Click Event text box, you will
see an icon on the right with 3 dots ...
Click on the dots and it will open the VB editor to that procedure.
If it is running a report, you will see a line that is will start with:
Docmd.OpenReport .......
Put the command to run the make table query just before that line:
CurrentDb.Execute("YourQueryNameGoesHere"), dbFailOnError

That's it!

:

I have an existing access db (MS Access 2000) which has a form with subforms
which are buttons.

Someone else designed this and I have NEVER really been in here before, but
I can write a mean query in Access. Right now, the form presents them with
buttons. They press them and it runs a report in preview mode. We want this
to stay the same, however, we need a make-table query to run before this
report.

How do I insert the command to run the make table query before the command
to run and preview the report?

I've made all the modifications and tested and I just need to automate the
make-table.

 

[Guest]

To clarify:

I have a report called PM Report based on the PM Report Query. It *was* all
working. I created another Query called F1207 which builds (was a
make-table, now an append) the F1207_50s. I then modified the PM Report
Query to have the F1207_50s table, added a few fields and then modified the
PM report layout to pull in a new field in the query. It all works fine
EXCEPT that if I push the button (that in the past simply called the PM
Report (calling the PM Report Query), it will not get fresh data in the
F1207_50s table - it must be refreshed first. THEN, if the PM Report/Query
runs, it'll work.

My code for the button:

Private Sub Frankie_s_PM_Report_Click()
On Error GoTo Err_Frankie_s_PM_Report_Click

CurrentDb.Execute ("DELETE * FROM F1207_50s;"), dbFailOnError
CurrentDb.Execute ("qappF1207"), dbFailOnError

Dim stDocName As String

stDocName = "PM Report"
DoCmd.OpenReport stDocName, acPreview

Exit_Frankie_s_PM_Report_Click:
Exit Sub

Err_Frankie_s_PM_Report_Click:
MsgBox Err.Description
Resume Exit_Frankie_s_PM_Report_Click

I think you're probably setting the make table query as the row source
instead of the table that you just made.

Okay...makes perfect sense. I did that and it's giving me an error that an
action query cannot be used as a row source.

What I'm doing is running this first query to build a table that is used in
the report that is immediately called thereafter. When I do it manually, run
the "F1207" query to populate the "F1207_50s" table, then the "PM Report"
kicks off with this table "F1207_50s" as well as two others already in the
database.

The append works, when I change the query to do that but not off the button.

There cannot be two like objects in the same mdb with the same name, so no
suppression of error messages or warnings is going to resolve this. There
are two ways to approach this.
One would be to use the DeleteObject method to delete the old table before
running the make table query to create a new one.
The other (the one I would use, I will explain why later) would be to delete
the data from the table and run an Append query rather than a Make Table
query to load the data. The reason is that a Make Table query uses the
default field length for text fields and guesses at what the data type should
be for numeric fields. This creates unneeded bloat, degrades performance,
and in rare cases can cause lose of decimals for numeric fields.

If I were doing it, I would open the table in design mode, set the field
properties approriately, and leave it in place. Then the sequence would be:

CurrentDb.Execute("DELETE * FROM mytable;"), dbFailOnError
CurrentDb.Execute("qappMyAppendQuery"), dbFailOnError

:

WOW!! You guys (or girls) are GREAT!! The last one did in fact get me through
what I needed. Only issue now, is that it's giving me an error (message)
that the table already exists for the Make Table query. I'm looking for
something (setting?) that allows this to be created without a warning.
thoughts?

:

Open the form with the button where you want to run the make table query in
design view.
Right click on the button and select Properties from the menu.
Select the Events tab of the properties dialog and select the Click event.
Since it is running a report, it will probably say [Event Procedure].
Once you have positioned your cursor in the Click Event text box, you will
see an icon on the right with 3 dots ...
Click on the dots and it will open the VB editor to that procedure.
If it is running a report, you will see a line that is will start with:
Docmd.OpenReport .......
Put the command to run the make table query just before that line:
CurrentDb.Execute("YourQueryNameGoesHere"), dbFailOnError

That's it!

:

I have an existing access db (MS Access 2000) which has a form with subforms
which are buttons.

Someone else designed this and I have NEVER really been in here before, but
I can write a mean query in Access. Right now, the form presents them with
buttons. They press them and it runs a report in preview mode. We want this
to stay the same, however, we need a make-table query to run before this
report.

How do I insert the command to run the make table query before the command
to run and preview the report?

I've made all the modifications and tested and I just need to automate the
make-table.

 

[Rob Oldfield]

So the query qappF1207 is appending to the same table that PM Report is
based on?

What does opening PM Report in preview give you?

(Apologies if you replied that response directly Annie, sent to the wrong
place first time round.)

To clarify:

I have a report called PM Report based on the PM Report Query. It *was* all
working. I created another Query called F1207 which builds (was a
make-table, now an append) the F1207_50s. I then modified the PM Report
Query to have the F1207_50s table, added a few fields and then modified the
PM report layout to pull in a new field in the query. It all works fine
EXCEPT that if I push the button (that in the past simply called the PM
Report (calling the PM Report Query), it will not get fresh data in the
F1207_50s table - it must be refreshed first. THEN, if the PM Report/Query
runs, it'll work.

My code for the button:

Private Sub Frankie_s_PM_Report_Click()
On Error GoTo Err_Frankie_s_PM_Report_Click

CurrentDb.Execute ("DELETE * FROM F1207_50s;"), dbFailOnError
CurrentDb.Execute ("qappF1207"), dbFailOnError

Dim stDocName As String

stDocName = "PM Report"
DoCmd.OpenReport stDocName, acPreview

Exit_Frankie_s_PM_Report_Click:
Exit Sub

Err_Frankie_s_PM_Report_Click:
MsgBox Err.Description
Resume Exit_Frankie_s_PM_Report_Click

I think you're probably setting the make table query as the row source
instead of the table that you just made.

Okay...makes perfect sense. I did that and it's giving me an error that an
action query cannot be used as a row source.

What I'm doing is running this first query to build a table that is

used
in

the report that is immediately called thereafter. When I do it

manually,
run

the "F1207" query to populate the "F1207_50s" table, then the "PM Report"
kicks off with this table "F1207_50s" as well as two others already in the
database.

The append works, when I change the query to do that but not off the button.


:

There cannot be two like objects in the same mdb with the same name,

so
no

suppression of error messages or warnings is going to resolve this. There
are two ways to approach this.
One would be to use the DeleteObject method to delete the old table before
running the make table query to create a new one.
The other (the one I would use, I will explain why later) would be

to
delete

the data from the table and run an Append query rather than a Make Table
query to load the data. The reason is that a Make Table query uses the
default field length for text fields and guesses at what the data

type
should

be for numeric fields. This creates unneeded bloat, degrades performance,
and in rare cases can cause lose of decimals for numeric fields.

If I were doing it, I would open the table in design mode, set the field
properties approriately, and leave it in place. Then the sequence

would
be:

CurrentDb.Execute("DELETE * FROM mytable;"), dbFailOnError
CurrentDb.Execute("qappMyAppendQuery"), dbFailOnError

:

WOW!! You guys (or girls) are GREAT!! The last one did in fact get

me
through

what I needed. Only issue now, is that it's giving me an error (message)
that the table already exists for the Make Table query. I'm

looking
for

something (setting?) that allows this to be created without a warning.
thoughts?

:

Open the form with the button where you want to run the make

table
query in

design view.
Right click on the button and select Properties from the menu.
Select the Events tab of the properties dialog and select the

Click
event.

Since it is running a report, it will probably say [Event Procedure].
Once you have positioned your cursor in the Click Event text

box,
you will

see an icon on the right with 3 dots ...
Click on the dots and it will open the VB editor to that procedure.
If it is running a report, you will see a line that is will

start
with:

Docmd.OpenReport .......
Put the command to run the make table query just before that line:
CurrentDb.Execute("YourQueryNameGoesHere"), dbFailOnError

That's it!

:

I have an existing access db (MS Access 2000) which has a form with subforms
which are buttons.

Someone else designed this and I have NEVER really been in

here
before, but

I can write a mean query in Access. Right now, the form

presents
them with

buttons. They press them and it runs a report in preview

mode.
We want this

to stay the same, however, we need a make-table query to run before this
report.

How do I insert the command to run the make table query before

the
command

to run and preview the report?

I've made all the modifications and tested and I just need to automate the
make-table.

 

[Guest]

Sorry for the delay - I'm still trying to fix this.

The F1207 query does populate a table that is used in the PM Report (one of
three tables that the PM report is based on).

When I run the PM Report, it displays just fine.

What I'm trying to do is connect two tables to another table in the query.
For the purposes of the query and the report, I only need certain records
from the F1207. We have a one-to-many relationship. Consider that I have
records of individual cars in the first two tables and I'm linking to all the
work tickets in the F1207. I don't want to see closed tickets - I only want
to see open tickets at a certain status. I tried creating the link between
the files and then just filtering on the status I wanted, but it wasn't
working. So, I built a table that was a direct query on the F1207 only for
the status I needed and then linked to that. It worked just fine.

So, I need to kick off the query to build this subset of data before the
report runs. I'd rather have it dynamic and I'll try fiddling with it again
to see if I can do it.

Annie

So the query qappF1207 is appending to the same table that PM Report is
based on?

What does opening PM Report in preview give you?

(Apologies if you replied that response directly Annie, sent to the wrong
place first time round.)

To clarify:

I have a report called PM Report based on the PM Report Query. It *was* all
working. I created another Query called F1207 which builds (was a
make-table, now an append) the F1207_50s. I then modified the PM Report
Query to have the F1207_50s table, added a few fields and then modified the
PM report layout to pull in a new field in the query. It all works fine
EXCEPT that if I push the button (that in the past simply called the PM
Report (calling the PM Report Query), it will not get fresh data in the
F1207_50s table - it must be refreshed first. THEN, if the PM Report/Query
runs, it'll work.

My code for the button:

Private Sub Frankie_s_PM_Report_Click()
On Error GoTo Err_Frankie_s_PM_Report_Click

CurrentDb.Execute ("DELETE * FROM F1207_50s;"), dbFailOnError
CurrentDb.Execute ("qappF1207"), dbFailOnError

Dim stDocName As String

stDocName = "PM Report"
DoCmd.OpenReport stDocName, acPreview

Exit_Frankie_s_PM_Report_Click:
Exit Sub

Err_Frankie_s_PM_Report_Click:
MsgBox Err.Description
Resume Exit_Frankie_s_PM_Report_Click

I think you're probably setting the make table query as the row source
instead of the table that you just made.


Okay...makes perfect sense. I did that and it's giving me an error that an
action query cannot be used as a row source.

What I'm doing is running this first query to build a table that is used
in
the report that is immediately called thereafter. When I do it manually,
run
the "F1207" query to populate the "F1207_50s" table, then the "PM Report"
kicks off with this table "F1207_50s" as well as two others already in the
database.

The append works, when I change the query to do that but not off the
button.


:

There cannot be two like objects in the same mdb with the same name, so
no
suppression of error messages or warnings is going to resolve this.
There
are two ways to approach this.
One would be to use the DeleteObject method to delete the old table
before
running the make table query to create a new one.
The other (the one I would use, I will explain why later) would be to
delete
the data from the table and run an Append query rather than a Make Table
query to load the data. The reason is that a Make Table query uses the
default field length for text fields and guesses at what the data type
should
be for numeric fields. This creates unneeded bloat, degrades
performance,
and in rare cases can cause lose of decimals for numeric fields.

If I were doing it, I would open the table in design mode, set the field
properties approriately, and leave it in place. Then the sequence would
be:

CurrentDb.Execute("DELETE * FROM mytable;"), dbFailOnError
CurrentDb.Execute("qappMyAppendQuery"), dbFailOnError

:

WOW!! You guys (or girls) are GREAT!! The last one did in fact get me
through
what I needed. Only issue now, is that it's giving me an error
(message)
that the table already exists for the Make Table query. I'm looking
for
something (setting?) that allows this to be created without a warning.
thoughts?

:

Open the form with the button where you want to run the make table
query in
design view.
Right click on the button and select Properties from the menu.
Select the Events tab of the properties dialog and select the Click
event.
Since it is running a report, it will probably say [Event
Procedure].
Once you have positioned your cursor in the Click Event text box,
you will
see an icon on the right with 3 dots ...
Click on the dots and it will open the VB editor to that procedure.
If it is running a report, you will see a line that is will start
with:
Docmd.OpenReport .......
Put the command to run the make table query just before that line:
CurrentDb.Execute("YourQueryNameGoesHere"), dbFailOnError

That's it!

:

I have an existing access db (MS Access 2000) which has a form
with subforms
which are buttons.

Someone else designed this and I have NEVER really been in here
before, but
I can write a mean query in Access. Right now, the form presents
them with
buttons. They press them and it runs a report in preview mode.
We want this
to stay the same, however, we need a make-table query to run
before this
report.

How do I insert the command to run the make table query before the
command
to run and preview the report?

I've made all the modifications and tested and I just need to
automate the
make-table.

 

[Rob Oldfield]

The usual way to do that would be just to open the report from a button on a
form - AFTER running the queries to build the relevant tables. The code
would be:

docmd.openquery.....
(or db.execute....)
docmd.openreport....

If that's too simple an answer, then I'm missing the point of what your
problem is.

Sorry for the delay - I'm still trying to fix this.

The F1207 query does populate a table that is used in the PM Report (one of
three tables that the PM report is based on).

When I run the PM Report, it displays just fine.

What I'm trying to do is connect two tables to another table in the query.
For the purposes of the query and the report, I only need certain records
from the F1207. We have a one-to-many relationship. Consider that I have
records of individual cars in the first two tables and I'm linking to all the
work tickets in the F1207. I don't want to see closed tickets - I only want
to see open tickets at a certain status. I tried creating the link between
the files and then just filtering on the status I wanted, but it wasn't
working. So, I built a table that was a direct query on the F1207 only for
the status I needed and then linked to that. It worked just fine.

So, I need to kick off the query to build this subset of data before the
report runs. I'd rather have it dynamic and I'll try fiddling with it again
to see if I can do it.

Annie

So the query qappF1207 is appending to the same table that PM Report is
based on?

What does opening PM Report in preview give you?

(Apologies if you replied that response directly Annie, sent to the wrong
place first time round.)

To clarify:

I have a report called PM Report based on the PM Report Query. It

*was*
all

working. I created another Query called F1207 which builds (was a
make-table, now an append) the F1207_50s. I then modified the PM Report
Query to have the F1207_50s table, added a few fields and then

modified
the

PM report layout to pull in a new field in the query. It all works fine
EXCEPT that if I push the button (that in the past simply called the PM
Report (calling the PM Report Query), it will not get fresh data in the
F1207_50s table - it must be refreshed first. THEN, if the PM Report/Query
runs, it'll work.

My code for the button:

Private Sub Frankie_s_PM_Report_Click()
On Error GoTo Err_Frankie_s_PM_Report_Click

CurrentDb.Execute ("DELETE * FROM F1207_50s;"), dbFailOnError
CurrentDb.Execute ("qappF1207"), dbFailOnError

Dim stDocName As String

stDocName = "PM Report"
DoCmd.OpenReport stDocName, acPreview

Exit_Frankie_s_PM_Report_Click:
Exit Sub

Err_Frankie_s_PM_Report_Click:
MsgBox Err.Description
Resume Exit_Frankie_s_PM_Report_Click

:

I think you're probably setting the make table query as the row source
instead of the table that you just made.


Okay...makes perfect sense. I did that and it's giving me an error that an
action query cannot be used as a row source.

What I'm doing is running this first query to build a table that

is
used

in
the report that is immediately called thereafter. When I do it manually,
run
the "F1207" query to populate the "F1207_50s" table, then the "PM Report"
kicks off with this table "F1207_50s" as well as two others

already in
the

database.

The append works, when I change the query to do that but not off the
button.


:

There cannot be two like objects in the same mdb with the same

name,
so

no
suppression of error messages or warnings is going to resolve this.
There
are two ways to approach this.
One would be to use the DeleteObject method to delete the old table
before
running the make table query to create a new one.
The other (the one I would use, I will explain why later) would

be
to

delete
the data from the table and run an Append query rather than a

Make
Table

query to load the data. The reason is that a Make Table query

uses
the

default field length for text fields and guesses at what the

data
type

should
be for numeric fields. This creates unneeded bloat, degrades
performance,
and in rare cases can cause lose of decimals for numeric fields.

If I were doing it, I would open the table in design mode, set

the
field

properties approriately, and leave it in place. Then the

sequence
would

be:

CurrentDb.Execute("DELETE * FROM mytable;"), dbFailOnError
CurrentDb.Execute("qappMyAppendQuery"), dbFailOnError

:

WOW!! You guys (or girls) are GREAT!! The last one did in fact

get
me

through
what I needed. Only issue now, is that it's giving me an error
(message)
that the table already exists for the Make Table query. I'm looking
for
something (setting?) that allows this to be created without a warning.
thoughts?

:

Open the form with the button where you want to run the make table
query in
design view.
Right click on the button and select Properties from the menu.
Select the Events tab of the properties dialog and select

the
Click

event.
Since it is running a report, it will probably say [Event
Procedure].
Once you have positioned your cursor in the Click Event text box,
you will
see an icon on the right with 3 dots ...
Click on the dots and it will open the VB editor to that procedure.
If it is running a report, you will see a line that is will start
with:
Docmd.OpenReport .......
Put the command to run the make table query just before that line:
CurrentDb.Execute("YourQueryNameGoesHere"), dbFailOnError

That's it!

:

I have an existing access db (MS Access 2000) which has a form
with subforms
which are buttons.

Someone else designed this and I have NEVER really been in here
before, but
I can write a mean query in Access. Right now, the form presents
them with
buttons. They press them and it runs a report in preview mode.
We want this
to stay the same, however, we need a make-table query to run
before this
report.

How do I insert the command to run the make table query

before
the

command
to run and preview the report?

I've made all the modifications and tested and I just need to
automate the
make-table.