automatically create queries

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

Guest

I am currently creating quries based on collector's name. for example:
qrySam, qryJane, qryBob. These are exported out to excel spreadsheet in
different tabs. Is there a way to somehow in code create queries based on
name so that if there are new names added to the main table, I wouldn't have
to create queries for each new name? any suggestions? I maybe be making it
harder than it should.
thank you!
 
Hi,
yes, you can use CreateQueryDef Method
Look at Access help for more info and examples
 
I maybe be making it harder than it should.
Click to expand...

Yes you are! You should only have *one* query, and use a parameter in it
to select the collector's name at runtime. Here's an idea:

* Create a table to hold collectors' names (unless it already exists);
* Define a public valiable in a standard module, to temporarily hold the
collector's name;
* Create a simple function in the module, to return the collector's name
currently stored in the public variable, so the query can "read" it as a
parameter;
* Change your query so the criterion under the collector name field
reads the public variable through the function;
* Use code to loop through the collectors' names and export the query
results.

Sample code:

Option Compare Database

Public vCollector As String
Function F_Collector()
F_Collector = vCollector
End Function

Sub Export_qryCollector()
Dim db As DAO.databasee
Dim rst As DAO.Recordset
Dim MyExportFile As String
Dim qry As String
Dim tbl As String
qry = "qryCollector"
tbl = "tblCollectors"
MyExportFile = "C:\My Documents\Collector Data.xls"
Set db = CurrentDb
Set rst = db.OpenRecordset(tbl)
rst.MoveFirst
Do Until rst.EOF
vCollector = rst.Fields("Collector_Name")
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, _
qry, MyExportFile, True, vCollector
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Assumptions:
Query name: qryCollector
Table name: tblCollectors, field: Collector_Name

The criterion in the query should be:
F_Collector()

The code loads the collector names as a recordset, and loops through
each, temporarily setting the variable vCollector to each name, and
exporting the query results to a separate sheet named after the
currently selected collector name.

Note: To run this code, an appropriate DAO Object Library reference is
required. While in the VB editor window, go to menu item Tools >
References; check if a Microsoft DAO reference is present among the ones
checked at the top of the list. If not, scroll down to find the
appropriate Microsoft DAO X.X Object Library reference and check it. The
appropriate reference is DAO 3.51 for A97, DAO 3.6 for A2K or later.

The above approach assumes you will always want to export all
collectors. If you want to be able to select manually which ones to
export each time, then make a form with a multi-select listbox with the
collectors' names, and modify the code to loop through the selections
instead of opening tblCollectors as a recordset.

HTH,
Nikos
 
I am sorry, i cannot find it. Can you suggest a good website for me learn
this function?
Thank you!
 
Wow!
Thank you for easy to follow instruction!
this is my code:

Function F_collector()
F_collector = vcollector

End Function

Sub export_qrycollector()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim myexportfile As String
Dim qry As String
qry = "qrycollector"
tbl = "tblcollectors"
myexportfile = "d:\my documents\collector data.xls"
Set db = CurrentDb
Set rst = db.OpenRecordset(tbl)
rst.MoveFirst
Do Until rst.EOF
vcollector = rst.Fields("collector_name")
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, qry, myexportfile,
True, vbcollector
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub
and my query looks like this:

SELECT CCC.[Collector Name], CCC.[Invoice #], CCC.[361+]
FROM CCC
WHERE (((CCC.[Collector Name])=F_collector()));

do i simply run the query??
sorry for my ignorance...
i have the function and sub export_qrycollector() in one module.

thank you!
 
I have figured it out. However, there's a problem.
There are 5 names in the tblcollectors and when exported, it is only
exporting one name. (last one on the list). What am i doing wrong?


GEORGIA said:
Wow!
Thank you for easy to follow instruction!
this is my code:

Function F_collector()
F_collector = vcollector

End Function

Sub export_qrycollector()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim myexportfile As String
Dim qry As String
qry = "qrycollector"
tbl = "tblcollectors"
myexportfile = "d:\my documents\collector data.xls"
Set db = CurrentDb
Set rst = db.OpenRecordset(tbl)
rst.MoveFirst
Do Until rst.EOF
vcollector = rst.Fields("collector_name")
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, qry, myexportfile,
True, vbcollector
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub
and my query looks like this:

SELECT CCC.[Collector Name], CCC.[Invoice #], CCC.[361+]
FROM CCC
WHERE (((CCC.[Collector Name])=F_collector()));

do i simply run the query??
sorry for my ignorance...
i have the function and sub export_qrycollector() in one module.

thank you!



Nikos Yannacopoulos said:
Yes you are! You should only have *one* query, and use a parameter in it
to select the collector's name at runtime. Here's an idea:

* Create a table to hold collectors' names (unless it already exists);
* Define a public valiable in a standard module, to temporarily hold the
collector's name;
* Create a simple function in the module, to return the collector's name
currently stored in the public variable, so the query can "read" it as a
parameter;
* Change your query so the criterion under the collector name field
reads the public variable through the function;
* Use code to loop through the collectors' names and export the query
results.

Sample code:

Option Compare Database

Public vCollector As String
Function F_Collector()
F_Collector = vCollector
End Function

Sub Export_qryCollector()
Dim db As DAO.databasee
Dim rst As DAO.Recordset
Dim MyExportFile As String
Dim qry As String
Dim tbl As String
qry = "qryCollector"
tbl = "tblCollectors"
MyExportFile = "C:\My Documents\Collector Data.xls"
Set db = CurrentDb
Set rst = db.OpenRecordset(tbl)
rst.MoveFirst
Do Until rst.EOF
vCollector = rst.Fields("Collector_Name")
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, _
qry, MyExportFile, True, vCollector
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Assumptions:
Query name: qryCollector
Table name: tblCollectors, field: Collector_Name

The criterion in the query should be:
F_Collector()

The code loads the collector names as a recordset, and loops through
each, temporarily setting the variable vCollector to each name, and
exporting the query results to a separate sheet named after the
currently selected collector name.

Note: To run this code, an appropriate DAO Object Library reference is
required. While in the VB editor window, go to menu item Tools >
References; check if a Microsoft DAO reference is present among the ones
checked at the top of the list. If not, scroll down to find the
appropriate Microsoft DAO X.X Object Library reference and check it. The
appropriate reference is DAO 3.51 for A97, DAO 3.6 for A2K or later.

The above approach assumes you will always want to export all
collectors. If you want to be able to select manually which ones to
export each time, then make a form with a multi-select listbox with the
collectors' names, and modify the code to loop through the selections
instead of opening tblCollectors as a recordset.

HTH,
Nikos
Click to expand...
Click to expand...
 
ok..
i don't know what i did but it is working PERFECTLY!!!
THANK YOU SOOOOOOO MUCH!!!


GEORGIA said:
I have figured it out. However, there's a problem.
There are 5 names in the tblcollectors and when exported, it is only
exporting one name. (last one on the list). What am i doing wrong?


GEORGIA said:
Wow!
Thank you for easy to follow instruction!
this is my code:

Function F_collector()
F_collector = vcollector

End Function

Sub export_qrycollector()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim myexportfile As String
Dim qry As String
qry = "qrycollector"
tbl = "tblcollectors"
myexportfile = "d:\my documents\collector data.xls"
Set db = CurrentDb
Set rst = db.OpenRecordset(tbl)
rst.MoveFirst
Do Until rst.EOF
vcollector = rst.Fields("collector_name")
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, qry, myexportfile,
True, vbcollector
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub
and my query looks like this:

SELECT CCC.[Collector Name], CCC.[Invoice #], CCC.[361+]
FROM CCC
WHERE (((CCC.[Collector Name])=F_collector()));

do i simply run the query??
sorry for my ignorance...
i have the function and sub export_qrycollector() in one module.

thank you!



Nikos Yannacopoulos said:
I maybe be making it harder than it should.

Yes you are! You should only have *one* query, and use a parameter in it
to select the collector's name at runtime. Here's an idea:

* Create a table to hold collectors' names (unless it already exists);
* Define a public valiable in a standard module, to temporarily hold the
collector's name;
* Create a simple function in the module, to return the collector's name
currently stored in the public variable, so the query can "read" it as a
parameter;
* Change your query so the criterion under the collector name field
reads the public variable through the function;
* Use code to loop through the collectors' names and export the query
results.

Sample code:

Option Compare Database

Public vCollector As String
Function F_Collector()
F_Collector = vCollector
End Function

Sub Export_qryCollector()
Dim db As DAO.databasee
Dim rst As DAO.Recordset
Dim MyExportFile As String
Dim qry As String
Dim tbl As String
qry = "qryCollector"
tbl = "tblCollectors"
MyExportFile = "C:\My Documents\Collector Data.xls"
Set db = CurrentDb
Set rst = db.OpenRecordset(tbl)
rst.MoveFirst
Do Until rst.EOF
vCollector = rst.Fields("Collector_Name")
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, _
qry, MyExportFile, True, vCollector
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Assumptions:
Query name: qryCollector
Table name: tblCollectors, field: Collector_Name

The criterion in the query should be:
F_Collector()

The code loads the collector names as a recordset, and loops through
each, temporarily setting the variable vCollector to each name, and
exporting the query results to a separate sheet named after the
currently selected collector name.

Note: To run this code, an appropriate DAO Object Library reference is
required. While in the VB editor window, go to menu item Tools >
References; check if a Microsoft DAO reference is present among the ones
checked at the top of the list. If not, scroll down to find the
appropriate Microsoft DAO X.X Object Library reference and check it. The
appropriate reference is DAO 3.51 for A97, DAO 3.6 for A2K or later.

The above approach assumes you will always want to export all
collectors. If you want to be able to select manually which ones to
export each time, then make a form with a multi-select listbox with the
collectors' names, and modify the code to loop through the selections
instead of opening tblCollectors as a recordset.

HTH,
Nikos
Click to expand...
Click to expand...
Click to expand...
 
You are welcome! Glad that you could troubleshoot on your own, that's
where the real value lies.

Nikos
 
Back
Top