running parameter report via command line

No ... here's a list of the command line switches: http://www.vb123.com/workbench/help/commandline.htm

what are you trying to do? Perhaps there's another way.

I'm thinking you can actually have your report prompt for the parameter...haven't ever done it quite this way though.

This database will have each users tasks in it. A monthly report willl generate and
show the user what is on their list. Or incase of someone being out for an extended time
we can bring up their list to cover for them.
Im trying to have a task list, or todo list, auto emailed out to each user without setting up a
seperate query and report for each user.
I got that macro working and the email part working.
This was the last tweak.

So what Parameter would you need to enter? The UserName?

How are yout tasks tied to each user? For example, when a user entered a new task, how did you determine that Task was supposed to be assigned to Scott instead of Sam?

yes the user name.
The user name would determine who the task would be assigned to.
Everyone who uses this would be entering their own tasks.
Its more of a organizationa tool.

so how about a table of users that the program cycles through and creates/mails the report for, then closes

so the intention of the shell is a once a month thing...but you need to also be able to go in and occasionally manually do this?

yes you are correct

in order to introduce the parameter, it will really need to reside inside the recordsource for the report. I assume this is a query. For the once a month thing, if you have it on some kind of scheduler as a batch routine, then you'll definately want to have a list of users in a table that we can feed to your query one at a time and have a report generated and sent vie e-mail automatically.

As far as ad hoc requests, you'll probably find it easier to just create 1 separate query using a field from a form to pass the parameter. You could provide a dropdown box of user names, so the user can select by name and preview the report before sending it.

Waiting on you though...how you want to go with it.

As Scott already stated...you won't be able to do it using just the shell command.

The manual requests will be done using a form with drop downs to select
the user and month. The manual req wont be emailed. They will simply
print them out.

I was going to use the windows scheduler to run the report macro.
The date has the parameter of Month(Now()) so when run on the 1st of each month
it will only show the tasks for the current month.
I am a novice at programming but would like to accomplish this if it isnt
too diffacult.

ok, do this then...go into your macro and do a save-as and save it as code. We'll transform it into an e-mail sending machine! then we can call the code through a macro.

You'll need a table with your users and their e-mail addresses I suppose. You have one?

Also paste in any code your'e using outside of the macro.

J

Heres the code:
The table with the users and emails is called tblusers
Also, You will notice I have the report exporting to a rtf file called task. I am thinking each users report would need to be
unique names, there user name?
is there any way this could be exported as a pdf?
Thanks for your help with this.

Function MacroOutput()
On Error GoTo MacroOutput_Err

    DoCmd.OutputTo acReport, "RptTaskList_auto", "RichTextFormat(*.rtf)", "H:\Data\task\task.rtf", False, "", 0
    DoCmd.Quit acSave


MacroOutput_Exit:
    Exit Function

MacroOutput_Err:
    MsgBox Error$
    Resume MacroOutput_Exit

End Function

paul,
What you're asking for is usually something contractors get paid a lot to do. But if you want to take it on, you're going to need some things first.

In order to save your report to PDF like you want, you're going to need a full blown version of Adobe Acrobat. There is a well document method developed by Lebans that allows you to easily use the Application within access.

If you're happy with just exporting using .rtf, then this is much easier, and cheaper. But that decision is yours to make.

You said you're e-mailing this .rtf file to your user....do you have any code developed yet to do this?

so far, the only thing your macro appears to do is write a file out to a drive.

So in this question, you now want to:
1) create a report by user
  a) open your user table
  b) get first user and loop through consecutive users
  c) apply the user id against your report's query to select only his records
  d) apply the recordsource to the report
2) save report as a .pdf
  a) very involved process with a cost associated
3) create an e-mail and attach the .pdf
  a) what e-mail do you use?
  b) if outlook then you'll want to explore CDO methods or Outlook security bypass software
  c) if other....need to know specifics
4) send the e-mail

there's a lot here now. If you want to break this up into separate questions, I think that's probably best.

Here's a sample of what you're up against


Function ExportAllPDF() As Boolean
' This function takes all the open claims and exports them to excel by Adjuster and mails them as individual e-mails
    Dim rs As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim sTmpQuery As String
    Dim strTo As String
    Dim strSubj As String
    Dim strText As String
    Dim strReport As String
    Dim strResetCat As String
    Dim strResetDaily As String
    Dim strAdj As String
    On Error Resume Next
    'Name the temporary output query
    sTmpQuery = "PDFQuery"
    'Create a Query Definition for the Temporary Query
    CurrentDb.QueryDefs.Delete sTmpQuery
    'Set the Query Definition to use the recordset
    Set qdf = CurrentDb.CreateQueryDef(sTmpQuery, "SELECT * FROM qry_PDFAll")
        Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT [Rep ID] FROM qry_PDFAll")
        Do While Not rs.EOF
            strAdj = rs.Fields("[Rep ID]").Value
            'Grab all records for the Rep
            qdf.SQL = "SELECT * FROM qry_PDFAll WHERE [Rep ID] = '" & strAdj & "'"
            'Grab the Adjuster's e-mail address
            strTo = DLookup("Email", "mytable", "[Rep ID] = '" & strAdj & "'")
            strSubj = "mysubjectline"
            strText = "These are your newly assigned or Re-assigned claims as of " & Date
            'Send the e-mail with the PDF attached. Change TRUE to FALSE if you don't want to review the e-mail
            strReport = "C:\Temp\myfilename.pdf"
            blRet = ConvertReportToPDF("rpt_Claims", vbNullString, strReport, False, False)
            Call SendMessage(strTo, strSubj, strText, strReport)
            Kill strReport
            rs.MoveNext
        Loop
        rs.Close
        Set rs = Nothing
    CurrentDb.QueryDefs.Delete sTmpQuery
    strResetCat = "UPDATE [table] SET [table].field = 0;"
    strResetDaily = "UPDATE [Log] SET [Log].Log = 0;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strReset1
    DoCmd.RunSQL strReset2
    DoCmd.SetWarnings True
End Function

Function SendMessage(strTo As String, strSubj As String, strText As String, strAttach As String)
On Error GoTo Error_Routine
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Set OutApp = New Outlook.Application
    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
        .To = strTo
        .Subject = strSubj
        .Body = strText
            If Not IsMissing(strAttach) Then
        .Attachments.Add (strAttach)
            End If
        .Send
   End With
   Set OutMail = Nothing
   Set OutApp = Nothing
Exit_Continue:
        Exit Function
Error_Routine:
        MsgBox "Error# " & Err.Number & " " & Err.Description
        Resume Exit_Continue
End Function

 

Ok,
Maybe that is a little to much to do.
As far as the email goes we use Groupwise and I have a batchfile
which runs gwsend which allows me to send an email with an attachment
via the command line. thjan batch file is set to run after the reports are generated.
The Pdf transfer isnt needed. I can stick with rtf.
All I really need to do is step 1. I think I can figure out the rest.
How involved is step 1?

1) create a report by user
  a) open your user table
  b) get first user and loop through consecutive users
  c) apply the user id against your report's query to select only his records
  d) apply the recordsource to the report

If we want to do all users at the same time...where each report is unique to that user, we need to modify the report's recordsource accordingly. what are you using for the report now? A query of some kind?


Also, what is the name of the field in the tblUser table?

 Here's a start. What this does is create a query that you'll use as the recordset for your report. You can call the query anything you like, but basically it's going to take the query you have now and filter it by User, then save it using the user name

Function SaveFileAs() As String
Dim rs As DAO.Recordset
Dim strSQL As String
Dim UserName As String
Dim sTempQuery As String
Dim strReport As String
Dim qdf As DAO.QueryDef
Dim sTmpQuery As String
 'Name the temporary output query. This will be the recordsource for the report
    sTmpQuery = "PDFQuery"
    'Create a Query Definition for the Temporary Query
    If QueryExists(sTempQuery) Then
        CurrentDb.QueryDefs.Delete sTmpQuery
    End If
    'Set the Query Definition to use the recordset
    Set qdf = CurrentDb.CreateQueryDef(sTmpQuery, "SELECT * FROM (yourreportrecordsource)")
        Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT username FROM tblUsers")
        Do While Not rs.EOF
            UserName = rs.Fields("[username]").Value
            qdf.sql = "SELECT * FROM (yourreportrecordset) WHERE [username] = '" & UserName & "'"
            strReport = "H:\Data\task\" & UserName & "_task.rtf"
            DoCmd.OutputTo acReport, "RptTaskList_auto", "RichTextFormat(*.rtf)", strReport, False, ""
        rs.MoveNext
        Loop
        rs.Close
        Set rs = Nothing
    CurrentDb.QueryDefs.Delete sTmpQuery
End Function

I am goin to try this today.
The name of the field is email

ok, let us know if you run into a snag

I have created anew module with the above code.
I think I plugged in my tbl and query names where needed but not positive.
When it runs I get a compile error, sub or function not defined
and QueryExists is highlighted.
Please let me know what Im doing wrong.

Function SaveFileAs() As String
Dim rs As DAO.Recordset
Dim strSQL As String
Dim UserName As String
Dim sTempQuery As String
Dim strReport As String
Dim qdf As DAO.QueryDef
Dim sTmpQuery As String
 'Name the temporary output query. This will be the recordsource for the report
    sTmpQuery = "PDFQuery"
    'Create a Query Definition for the Temporary Query
    If QueryExists(sTempQuery) Then
        CurrentDb.QueryDefs.Delete sTmpQuery
    End If
    'Set the Query Definition to use the recordset
    Set qdf = CurrentDb.CreateQueryDef(sTmpQuery, "SELECT * FROM QryTaskbyMonth")
        Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT username FROM tblUsers")
        Do While Not rs.EOF
            UserName = rs.Fields("[user]").Value
            qdf.SQL = "SELECT * FROM QryTaskbyMonth WHERE [responsibility] = '" & UserName & "'"
            strReport = "H:\Data\task\" & UserName & "_task.rtf"
            DoCmd.OutputTo acReport, "RptTaskList_auto", "RichTextFormat(*.rtf)", strReport, False, ""
        rs.MoveNext
        Loop
        rs.Close
        Set rs = Nothing
    CurrentDb.QueryDefs.Delete sTmpQuery
End Function

Paste this into the same module...
Function QueryExists(qname$) As Boolean

    On Error Resume Next
    Dim db As DataBase, rs As Recordset, I%
    Set db = CurrentDb
    For I = 0 To db.QueryDefs.count - 1
        If db.QueryDefs(I).Name = qname Then
            QueryExists = True
            Exit For
        End If
    Next

End Function

also...

       Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT username FROM tblUsers") <---you're selecting username here
        Do While Not rs.EOF
            UserName = rs.Fields("[user]").Value   <----and you're trying to use a field called user here. Shouldn't they be the same?

Great  
I got this working!
Im increasing the points cause this was more involved than I thought
There is one last thing.
Can I run the code from a command line?
I need to run the code automatically.

Great!
Thanks for all the help!