Can Data from a Table Field be Used to Create Queries Automaticall

  • Thread starter Thread starter jgraves
  • Start date Start date
J

jgraves

I am new to VBA programming in Access, though very comfortable using queries
and macros. I want to know if the following can be done without VBA, and if
so, where should I start? If not possible, where should I start?

*Note - I realize more advanced folks refer to VBA as "macros", but I am
using "macros" to refer to the GUI interface object type that you can enter
commands, arguments and comments.

I have a large table of company product data (about 2 million records) that
I want to export in subsets based on "Company Name".

Ex:
Company Name Product Color Size ...
Acme ProdA-1 Red 4
Acme ProdA-2 Blue 4
Acme ProdA-3 Red 1
JaneDoe ProdABC Orange 2
JaneDoe ProdABC Orange 2

I had been using a select query, then exporting it via a macro command to an
Excel spreadsheet that I already formatted. (I designated named ranges to
specify where I wanted the information to export to, since each report is
then delivered to a specific customer)
Trouble is, I have 250 companies to export for. In the past I created 250
queries and 250 Excel templates to export them to, then just used a macro to
run through each one individually. I am thinking there has to be a better way.

I want to use a table with a summary of all the unique companies in the file
and use each company name as a criteria in the select query for that
company's product records. Then I also want to automate the export of those
select queries. (I don't want to have to create a macro with 250 line entries)

Does anyone have any suggestions as to how to accomplish this in a more
automated fashion? I would be so appreciative, as I am swimming over my head.
 
This would best be done using VBA. But no, most professionals do not refer
to VBA code as macros. That is the case in Excel, but not in Access.

You need only one parameter query to do them all. It is also not necessary
to pre format your Excel workbook. You can use the Range argument of the
TransferSpreadsheet method. Help says the Range argument doesn't work on an
export, but it actuall does. The Range can be just a worksheet name or it
can be a worksheet and cell range reference.

So in your code, you can filter the query for a company name and you can
pass the TransferSpreadsheet the range argument. One point here. If the
worksheet name can be just the company name, no problem. However, if you want
to give it a different name or include cell references, you may need to
create a table for that data. Then in your code you will need to pull that
information from the table.

I would use a form with a rowsource that is a query based on the new table I
described or if you don't need that table, then a Totals query on the company
table that would give you one record per customer. All you probably need is
the company name. The query could look like this:

SELECT tblClient.MainName FROM tblClient GROUP BY tblClient.MainName HAVING
tblClient.MainName=Forms!MyForm!txtClientName;

Put a text box on the form for the company name.

The flow would go something like this:

Using the form's Load Event

With Me.RecordsetClone
Do While Not .EOF
Docmd.TransferSpreadsheet.....
.MoveNext
Me.Bookmark = .Bookmark
Loop
End With
MsgBox "Report Complete"
Docmd.Close
 
Back
Top