Running a query

[qureshi]

Hi,
Let me try to explain
I have an access 97 database. In one of the form the user enters the requested information. This information is used to create a parametric query.
Now to get the output of the data generated by this query, I am doing the following:

sqlstr= Select * into tpTable1 where ....

So a temp table is created and a report designed on this table is produced for the user to view or print. The next time the user does the same operation, the code first deletes the temp table and then recreates it with the new data.

Problem: 1.If the user halts the execution of the program. then the next time the code does not find the temp table to delete and causes problems.

2. If I want to create a number of queries, I have to create the same number of temp tables.

I admit, this is the only way I know to run these queries. There must be another way to generate the data for the report without having any temp tables.

Any help, support will be appreciated.

Qureshi

 

[55plus]

You can use a query, as well as a table, for the recordsource of a report.

My husband (a retired COBOL programmer) and I just finished a big Access VBA project and he had the same idea about queries. A query does not have to be "executed" to produce results. It is a view of data in the underlying table and so whenever it is used (as in for the report's recordsource), the data is available.

HTH.

 

[qureshi]

55plus:
Below is the code I am using to get the data for a particular supplier. The user selects the supplier through the List Box, which is then passed on to the query which in turn produces a temp table tpSup.
Can you modify this and show be a proper way of doing something similiar.

Thanks in advance for your time.

Qureshi


Private Sub Command12_Click()
Dim Supplier As String
Dim iList As Integer
Dim sqlstr, Supy As String
Set db = CurrentDb

'deleting the query and the temp table

db.Execute "delete * from tpSup"
db.QueryDefs.Delete "BySup"
db.TableDefs.Delete "tpSup"

For iList = 0 To List26.ListCount - 1
If List26.Selected(iList) = True Then
Supp = List26.ItemData(iList)
End If
Next
Set qdf = db.CreateQueryDef("BySup&quot
sqlstr = "PARAMETERS Supy text;"
sqlstr = sqlstr & " Select * into tpSup from Product where Supplier = Supy order by date"
qdf.SQL = sqlstr
qdf.Close
qdf!Supy = Supp
qdf.Execute
Set rs = db.OpenRecordset("tpSup", dbOpenDynaset)
DoCmd.OpenReport "tpSup", acViewPreview
rs.Close
qdf.Close
db.Close
End Sub

 

[TLady]

My understanding you create make table Query, right ?
This is why it is gone every time...
How about creating a Table and Insert Query ?
This way you can use tha same table and every time new user entering criteria before execution of the code - simply "delete from table1"

TIA

 

[55plus]

I have modified your code (see below) to eliminate the make table from the select. With these modifications, the sql is just a simple select query. I have not tried this so I don't guarantee that it will work precisely as I've written it.

Once that works, change the report's record source to bysup. I am assuming that this report's recordsource is always the same -- correct? If it is,then the recordsource can be set in the report properties and does not have to be set via coding.

HTH.
=====================================================

Private Sub Command12_Click()
Dim Supplier As String
Dim iList As Integer
Dim sqlstr, Supy As String
Set db = CurrentDb

'deleting the query

'db.Execute "delete * from tpSup"
db.QueryDefs.Delete "BySup"
'db.TableDefs.Delete "tpSup"

For iList = 0 To List26.ListCount - 1
If List26.Selected(iList) = True Then
Supp = List26.ItemData(iList)
End If
Next
Set qdf = db.CreateQueryDef("BySup&quot
sqlstr = "PARAMETERS Supy text;"
sqlstr = sqlstr & " Select * from Product where Supplier = Supy order by date"
qdf.SQL = sqlstr
qdf.Close
qdf!Supy = Supp
qdf.Execute
'Set rs = db.OpenRecordset("tpSup", dbOpenDynaset)
DoCmd.OpenReport "tpSup", acViewPreview
rs.Close
qdf.Close
db.Close
End Sub

 

[qureshi]

55plus:
Thanks for your time.
I tried the modified code and get the following error:
"Cant execute the select query"
I know it has to do something with the type of query being executed i.e Pass through.

Tlady:
I modified the code above and did a query as follows:
Insert into tpSup select * from product where supplier = Supy order by date.

It works fine, but the basic question stays. I mean still I am creating a query which has to be deleted every time the pcode is run. If the user halts execution in the middle, the next time when the code reaches the line:
db.querydef.delete ("BySup&quot
the error will be:
Cannot find the query BySup.

How would you go about in a situation like this.

Thanks

 

[55plus]

The only other thing I can think of is to insert some error handling (On error ....) in your code before the

db.querydef.delete ("BySup&quot

statement. Check err.number for the appropriate value for a "not found" condition (I'm not sure what that value is) and take appropriate action yourself.

HTH.