Need help automating a manual process

[The Yankees Stat Zone]

Hi,

I need help to automate a manual process. Currently the db uses a
macro that does the following:

1. Opens a query that lists names of sales people that have had sales
since the last export date (the date is stored in a table).
2. Kicks off a parameter query where the user types the sales person
name (from above query)
3. Exports that query into htm in a directory that the user chooses
(it's the intranet shared directory, user browses to the directory and
is intructed to overwrite the existing salesperson.htm file)
4. inserts current timestamp into the last export date field.
5. Closes the list.

What I'd like to do is:
Create a module that will do this:

1. Use the query referenced in 1 above as a recordset.
2. Insert the name it finds in the recordset into the parameter query
(no typing needed by user anymore)
3. Export the that query into a predefined location (location stored
in a table) without the user browsing to find the location, using the
shortname from within the recordset.
4. loop the module for every name in the recordset.
5. update the last export date.

The salespersonlist query is shown as follows:

SalesPerson ShortName
George Foreman geofor
Leonard Spinks leospi
Muhammed Ali muhali
Lennox Lewis lenlew
Mike Tyson miktys


Here's the air-code that I envision, very loosely written and
untested, help me clean up this code so it works.

Function ExportSales()
On Error GoTo Err_Command0_Click

Dim dbSalesRevenue As Database
Dim rstSalesPersonList As Recordset
Dim stSalesPerson As String
Dim stShortName As String
Dim stExportLocation As String
Dim stTemplateLocation As String

Set dbSalesRevenue = CurrentDb()
Set rstSalesPersonList =
dbSalesRevenue.OpenRecordset("qry_SalesPersonList", dbOpenDynaset)

stShort = DLookup("[ShortName]", "qry_list_of_salespersons")
stLocation = DLookup("Private", "Settings") & "private\Query
Template.htm"
stExport = DLookup("Export", "Settings")

DoCmd.OutputTo acOutputQuery, "Parameter Query name", acFormatHTML,
stExportLocation & rstSalesPersonList.stShortname & ".htm", ,
stTemplateLocation

Exit_Command0_Click:
Exit Function

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Function

To summarize, my questions:
Where does the Loop go?
where does the do until EOF go?
How do I reference the rst in the parameter query?
How do I reference the rst as the html file name?

This is my first stab at doing a loop module, couldn't find a any
concrete information on the web would be helpful if anyone has a link
with a tutorial I would appreciate it.

YF4E

 

[Steve Sanford]

I've been looking at your post and I think I understand what you are trying
to do, but I need more info.

I need help to automate a manual process. Currently the db uses a
macro that does the following:

1. Opens a query that lists names of sales people that have had sales
since the last export date (the date is stored in a table).

What is the name of this query?
Please post the SQL of the query.

2. Kicks off a parameter query where the user types the sales person
name (from above query)

What is the name of this query?
Please post the SQL of the query.

3. Exports that query into htm in a directory that the user chooses
(it's the intranet shared directory, user browses to the directory and
is intructed to overwrite the existing salesperson.htm file)

What is the structure of the table "Settings"? Would you provide an example
of the data stored in it?

4. inserts current timestamp into the last export date field.

By "timestamp", is it just the date or date and time? What table is this
stored in? What field name?


Also, if you would convert the macro into VBA and post it, it would help. To
convert the macro, select the macro, then goto "TOOLS/MACRO/Convert Macro to
Visual Basic". The VBA code will be in a module named something like
"Converted Macro- test" where "Test" is the name of your macro.


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi,

I need help to automate a manual process. Currently the db uses a
macro that does the following:

1. Opens a query that lists names of sales people that have had sales
since the last export date (the date is stored in a table).
2. Kicks off a parameter query where the user types the sales person
name (from above query)
3. Exports that query into htm in a directory that the user chooses
(it's the intranet shared directory, user browses to the directory and
is intructed to overwrite the existing salesperson.htm file)
4. inserts current timestamp into the last export date field.
5. Closes the list.

What I'd like to do is:
Create a module that will do this:

1. Use the query referenced in 1 above as a recordset.
2. Insert the name it finds in the recordset into the parameter query
(no typing needed by user anymore)
3. Export the that query into a predefined location (location stored
in a table) without the user browsing to find the location, using the
shortname from within the recordset.
4. loop the module for every name in the recordset.
5. update the last export date.

The salespersonlist query is shown as follows:

SalesPerson ShortName
George Foreman geofor
Leonard Spinks leospi
Muhammed Ali muhali
Lennox Lewis lenlew
Mike Tyson miktys


Here's the air-code that I envision, very loosely written and
untested, help me clean up this code so it works.

Function ExportSales()
On Error GoTo Err_Command0_Click

Dim dbSalesRevenue As Database
Dim rstSalesPersonList As Recordset
Dim stSalesPerson As String
Dim stShortName As String
Dim stExportLocation As String
Dim stTemplateLocation As String

Set dbSalesRevenue = CurrentDb()
Set rstSalesPersonList =
dbSalesRevenue.OpenRecordset("qry_SalesPersonList", dbOpenDynaset)

stShort = DLookup("[ShortName]", "qry_list_of_salespersons")
stLocation = DLookup("Private", "Settings") & "private\Query
Template.htm"
stExport = DLookup("Export", "Settings")

DoCmd.OutputTo acOutputQuery, "Parameter Query name", acFormatHTML,
stExportLocation & rstSalesPersonList.stShortname & ".htm", ,
stTemplateLocation

Exit_Command0_Click:
Exit Function

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Function

To summarize, my questions:
Where does the Loop go?
where does the do until EOF go?
How do I reference the rst in the parameter query?
How do I reference the rst as the html file name?

This is my first stab at doing a loop module, couldn't find a any
concrete information on the web would be helpful if anyone has a link
with a tutorial I would appreciate it.

YF4E

 

[The Yankees Stat Zone]

I've been looking at your post  and I think I understand what you are trying
to do, but I need more info.

I need help to automate a manual process. Currently the db uses a
macro that does the following:
1. Opens a query that lists names of sales people that have had sales
since the last export date (the date is stored in a table).

What is the name of this query?
Please post the SQL of the query.

2. Kicks off a parameter query where the user types the sales person
name (from above query)

What is the name of this query?
Please post the SQL of the query.

3. Exports that query into htm in a directory that the user chooses
(it's the intranet shared directory, user browses to the directory and
is intructed to overwrite the existing salesperson.htm file)

What is the structure of the table "Settings"? Would you provide an example
of the data stored in it?

4. inserts current timestamp into the last export date field.

By "timestamp", is it just the date or date and time? What table is this
stored in? What field name?

Also, if you would convert the macro into VBA and post it, it would help.To
convert the macro, select the macro, then goto "TOOLS/MACRO/Convert Macroto
Visual Basic". The VBA code will be in a module named something like
"Converted Macro- test" where "Test" is the name of your macro.

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

:

Hi,

I need help to automate a manual process. Currently the db uses a
macro that does the following:

1. Opens a query that lists names of sales people that have had sales
since the last export date (the date is stored in a table).
2. Kicks off a parameter query where the user types the sales person
name (from above query)
3. Exports that query into htm in a directory that the user chooses
(it's the intranet shared directory, user browses to the directory and
is intructed to overwrite the existing salesperson.htm file)
4. inserts current timestamp into the last export date field.
5. Closes the list.

What I'd like to do is:
Create a module that will do this:

1. Use the query referenced in 1 above as a recordset.
2. Insert the name it finds in the recordset into the parameter query
(no typing needed by user anymore)
3. Export the that query into a predefined location (location stored
in a table) without the user browsing to find the location, using the
shortname from within the recordset.
4. loop the module for every name in the recordset.
5. update the last export date.

The salespersonlist query is shown as follows:

SalesPerson        ShortName
George Foreman     geofor
Leonard Spinks     leospi
Muhammed Ali       muhali
Lennox Lewis       lenlew
Mike Tyson miktys

Here's the air-code that I envision, very loosely written and
untested, help me clean up this code so it works.

Function ExportSales()
On Error GoTo Err_Command0_Click

   Dim dbSalesRevenue As Database
   Dim rstSalesPersonList As Recordset
   Dim stSalesPerson As String
   Dim stShortName As String
   Dim stExportLocation As String
   Dim stTemplateLocation As String

           Set dbSalesRevenue = CurrentDb()
   Set rstSalesPersonList =
dbSalesRevenue.OpenRecordset("qry_SalesPersonList", dbOpenDynaset)

   stShort = DLookup("[ShortName]", "qry_list_of_salespersons")
   stLocation = DLookup("Private", "Settings") & "private\Query
Template.htm"
   stExport = DLookup("Export", "Settings")

   DoCmd.OutputTo acOutputQuery, "Parameter Query name", acFormatHTML,
stExportLocation & rstSalesPersonList.stShortname & ".htm", ,
stTemplateLocation

Exit_Command0_Click:
    Exit Function

Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click

End Function

To summarize, my questions:
Where does the Loop go?
where does the do until EOF go?
How do I reference the rst in the parameter query?
How do I reference the rst as the html file name?

This is my first stab at doing a loop module, couldn't find a any
concrete information on the web would be helpful if anyone has a link
with a tutorial I would appreciate it.

YF4E- Hide quoted text -

- Show quoted text -

Hi Steve,
Thanks for taking the time to read/understand & reply to my post, I
think I may have over explained the situation.
For questions 1-4 this is part of process that is working but needs to
be retired in favor of an automated solution, it's automated portion
that I need help with.

To reword the task at, export a single parameter query using a
recordset to define the criteria, HTML file name, and number of times
to loop.
The recordset below (repeated here for ease and convenience) would
generate 5 HTML files with sales info for each sales person. And the
file names would be geofor.html, leospi.html, muhali.htm, lenlew.html,
miktys.html

SalesPerson ShortName
George Foreman geofor
Leonard Spinks leospi
Muhammed Ali muhali
Lennox Lewis lenlew
Mike Tyson miktys

Hope this was clearer than my last post.
YF4e

 

[Steve Sanford]

Hi Steve,
Thanks for taking the time to read/understand & reply to my post, I
think I may have over explained the situation.
For questions 1-4 this is part of process that is working but needs to
be retired in favor of an automated solution, it's automated portion
that I need help with.

Since the macro works, it is a great place to start from, if it is converted
to VBA.

To reword the task at, export a single parameter query using a
recordset to define the criteria, HTML file name, and number of times
to loop.

Not a problem, if I can see the SQL of the query.

The recordset below (repeated here for ease and convenience) would
generate 5 HTML files with sales info for each sales person. And the
file names would be geofor.html, leospi.html, muhali.htm, lenlew.html,
miktys.html

SalesPerson ShortName
George Foreman geofor
Leonard Spinks leospi
Muhammed Ali muhali
Lennox Lewis lenlew
Mike Tyson miktys

Got it. But you have two queries (items 1 & 2 in the "What the macro does).
If I see the SQL of the queries, I can convert them to recordsets and provide
a VBA procedure that is very close to what you ultimately want. Otherwise, I
can give you an outline and let you troubleshoot it until it works (a great
learning experience).

Hope this was clearer than my last post.
YF4e

HTH

 

[Steve Sanford]

YF4e,

I got busy and lost track of you. Did you get this straightened out?

I would add the query "SQL for Sales Activity by Sales Person" to the VBA
code.

I haven't tested this, I don't know what "@" refers to and I don't
understand why you have a table "Games" refered to in the FROM clause when
the table has no references to any fields from table "Games".


This is what I came up with...Warning: Air Code - untested!!

Option Compare Database
Option Explicit

Function ExportSalesActivity()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim stExportLocation As String
Dim StTemplateLocation As String
Dim strSQL As String

StTemplateLocation = DLookup("Private", "Settings")
stExportLocation = DLookup("Export", "Settings")

Set db = CurrentDb

Set rs = db.OpenRecordset("SalesPerson_Upload2", dbOpenSnapshot)
'check for records before trying to do a MoveFirst
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF

'SQL for Sales Activity by Sales Person: (select query that uses a
'parameter as criteria to prompt the user for input)
'
strSQL = "SELECT ClientVisits.Date, IIf(IsNull([@]),'Office','@ ' &
[ClientCity]) AS Location"
strSQL = strSQL & " FROM SalesPersonNames INNER JOIN Games "
strSQL = strSQL & " ON SalesPersonNames.NamesID =
ClientVisits.NameID"
strSQL = strSQL & " GROUP BY ClientVisits.Date,"
strSQL = strSQL & " IIf(IsNull([@]),'Office','@ ' & [ClientCity]),
SalesPersonNames.Short"
strSQL = strSQL & " HAVING (((SalesPersonNames.Short)= '" &
rs.ShortName & "';"

' Debug.Print strSQL

DoCmd.OutputTo acOutputQuery, "Sales Activity by Sales Person",
acFormatHTML, stExportLocation & "\query\" & rs!ShortName & ".htm", no,
StTemplateLocation & "private\Query Template.htm"

rs.MoveNext
Loop
End If

rs.Close
Set rs = Nothing
Set db = Nothing

Forms![Queries]![Text26].Requery

End Function

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Since the macro works, it is a great place to start from, if it is converted
to VBA.




Not a problem, if I can see the SQL of the query.



Got it. But you have two queries (items 1 & 2 in the "What the macro does).
If I see the SQL of the queries, I can convert them to recordsets and provide
a VBA procedure that is very close to what you ultimately want. Otherwise, I
can give you an outline and let you troubleshoot it until it works (a great
learning experience).




HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)- Hide quoted text -

- Show quoted text -

Steve,
Thanks again for your help, snce my last post I've made progress, I've
also ascertained some of the SQL and Code that you need to help me.
Now, I am able to loop the module and export the html x amount of time
using the shortname as the filename. I still cannot specify the
shortname as criteria for the parameter query.



Here's the new revised module, I've had to change the table names and
some fieldnames because they contain proprietary into


Option Compare Database

Function ExportSalesActivity()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim stExportLocation As String
Dim StTemplateLocation As String

StTemplateLocation = DLookup("Private", "Settings")
stExportLocation = DLookup("Export", "Settings")


Set db = CurrentDb

Set rs = db.OpenRecordset("SalesPerson_Upload2", dbOpenSnapshot) 'See
SQL for this query below
rs.MoveFirst
Do Until rs.EOF

DoCmd.OutputTo acOutputQuery, "Sales Activity by Sales Person",
acFormatHTML, stExportLocation & "\query\" & rs!ShortName & ".htm",
no, StTemplateLocation & "private\Query Template.htm" 'See SQL for
this query below
rs.MoveNext
Loop

Forms![Queries]![Text26].Requery

End Function


SQL for Sales Activity by Sales Person: (select query that uses a
parameter as criteria to prompt the user for input)

SELECT ClientVisits.Date, IIf(IsNull([@]),"Office","@ " &
[ClientCity]) AS Location
FROM SalesPersonNames INNER JOIN Games ON SalesPersonNames.NamesID =
ClientVisits.NameID
GROUP BY ClientVisits.Date, IIf(IsNull([@]),"Office","@ " &
[ClientCity]), SalesPersonNames.Short
HAVING (((SalesPersonNames.Short)=[Type the 6 Letter Short Name]));

SQL for SalesPerson_Upload2 (select query that displays list of sales
people that have activity since last upload date)
SELECT StrConv([Short],2) AS ShortName
FROM Upload, SalesPersonNames INNER JOIN SalesPersonNames.NamesID =
ClientVisits.NameID
WHERE (((ClientVisits.TypeID)=1 Or (ClientVisits.TypeID)=6) AND
((ClientVisits.Date)>([Upload].[Date])) AND
((SalesPersonNames.Exclude)=0))
GROUP BY StrConv([Short],2);