Running make a table query before final query

J

Johanna Gronlund

Hello,

I have a make-a-table query that is used by query1 for my report. Is it
possible to run the make a table query as well as query1 when the report is
opened. I know how to do this by using macros but would prefer to use code if
possible.

Many thanks is advance,

Johanna
 
J

John Spencer

Simplest way is one line of code - assuming you are using DAO (an .mdb
database)

CurrentDb().Execute "Name of Make Table Query"
'Followed by a line of code to open the report
DoCmd.OpenReport "Name of Report"
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
J

Johanna Gronlund

Thanks John. I am using mdb database so this should work fine. However, there
is an error because the old table is not deleted. I would need Table1 to be
deleted every time make-a-table query is run. Is this possible?

Johanna
 
J

John Spencer

Assuming that the table exists already

Dim DbAny as DAO.Database

Set DbAny = CurrentDb()
On Error Resume Next 'Turn off error handling
DbAny.TableDefs.Delete "Name of Make Table Query"
On Error Goto 0 'Turn error handling back on
DbAny.Execute "Name of Make Table Query"
'Followed by a line of code to open the report
DoCmd.OpenReport "Name of Report"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
J

Johanna Gronlund

Hi again John,

Thanks for your reply. I have been trying to get it to work by pasting it to
the code builder. However, I am very new to using any code and do not have
good enough understanding to make it work. I have just copied what you wrote
into the field and only changing the names of the table and query as below:

Private Sub Report_Open(Cancel As Integer)

Dim DbAny As DAO.Database

Set DbAny = CurrentDb()
On Error Resume Next 'Turn off error handling
DbAny.TableDefs.Delete "Qry_18Weeks1"
On Error GoTo 0 'Turn error handling back on
DbAny.Execute "Qry_18Weeks1"
'Followed by a line of code to open the report
DoCmd.OpenReport "Rpt_18WeeksTotal"

End Sub

What am I doing wrong? I especially do not understand the beginning of the
formula that you wrote: Dim DbAny As DAO.Database.

Many thanks for your conitnuing help!

Johanna
 
J

John Spencer

I wouldn't place this is the OPEN event of the report. I would call it from
a button event on a form; however the following modification may work

Private Sub Report_Open(Cancel As Integer)

Dim DbAny As DAO.Database

'Create a reference to the database that can be used

Set DbAny = CurrentDb()
On Error Resume Next 'Turn off error handling

'The following line should have the name of the TABLE that is created
'by the make table query.
DbAny.TableDefs.Delete "18Weeks Table"

On Error GoTo 0 'Turn error handling back on

DbAny.Execute "Qry_18Weeks1"
'You don't need the following since you have already opened the report
'Followed by a line of code to open the report
'DoCmd.OpenReport "Rpt_18WeeksTotal"

End Sub


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top