Passing parameters from a Form to a Macro?

[artemetis]

Ok...let's see if I can explain this properly.

I have a form.
On the form there is a dropdown box to select employee last name.

I then have a button to show a qry, passing the employee name as the criteria:

[Forms]![frmEmployee]![txtEmployeeLastName]

And then I run a macro to output the query to an XLS file.
The Output To file is:

="C:\Documents and Settings\arthur\Desktop\Survey_1011\" & InputBox("Enter the employee name that's in the file name:") & "_Survey.xls"

I'm trying to pass the Employee Name to the macro, so as to avoid the prompt. I have to have an employeeName_Survey.xls for each employee.

Now I know this is probably a God-Awful way of going about this and that I am making some seasoned programmers cringe, but it's all I came up with.

I initially did it manually...opened the query, sorted by employee name, then ran the outPut macro....I'm trying to streamline this a bit as I have a boatload of employees to dump this survey out for.

Thanks in advance!

[ADezii]

Post the SQL for the Query as well as its Name.

[artemetis]

qryEmployeeSurvey

SELECT tblEmployee.uid, [empFirstName] & ", " & [empLastName] AS Employee, tblEmployee.empUU1, tblEmployee.empUU2, tblEmployee.empUU3, tblEmployee.empUU4, tblEmployee.empUU5, tblEmployee.empGender
FROM tblEmployee
WHERE (((tblEmployee.empLastName)=[Forms]![frmEmployee]![txtLastName]));

[ADezii]

The General Idea is to:

1. Create a Recordset based on all Employee Last Names from the tblEmployee Table.
2. Loop through these Last Names, and for each Last Name, modify the SQL Property of the qryEmployeeSurvey Query.
3. Output each Query in turn using the Current Last Name as the File Name.
4. Code Definition:
   

   Dim MyDB As DAO.Database
   Dim rstEmployees As DAO.Recordset
   Dim qdf As DAO.QueryDef
    
   Set MyDB = CurrentDb
   Set rstEmployees = MyDB.OpenRecordset("SELECT tblEmployee.empLastName FROM tblEmployee", _
                           dbOpenForwardOnly)
    
   Set qdf = CurrentDb.QueryDefs("qryEmployeeSurvey")
    
   With rstEmployees
     Do While Not .EOF         'Loop through each Employee
       qdf.SQL = "SELECT tblEmployee.uid, [empFirstName] & ', ' & [empLastName] AS Employee, " & _
                 "tblEmployee.empUU1, tblEmployee.empUU2, tblEmployee.empUU3, tblEmployee.empUU4, " & _
                 "tblEmployee.empUU5, tblEmployee.empGender FROM tblEmployee " & _
                 "WHERE tblEmployee.empLastName = '" & ![empLastName] & "';"
       'Pseudo Code ==> Output qryEmployeeSurvey to "C:\Documents and Settings\arthur\Desktop\Survey_1011\" & _
                        '![empLastName] & "_Survey.xls"
         .MoveNext
     Loop
   End With
    
   rstEmployees.Close
   Set rstEmployees = Nothing

[artemetis]

Thank you Adezii, I do believe this did work, but I am curious on how to make the "'Pseudo Code ==>" work though?

I am trying this, unsuccessfully:

DoCmd.OutputTo ("C:\Documents and Settings\arthur\Desktop\Survey_1011\[empLastName]& '_survey.xls' & ")

[ADezii]

Pay particular attention to Code Lines 4, 18, and 19.

Dim MyDB As DAO.Database
Dim rstEmployees As DAO.Recordset
Dim qdf As DAO.QueryDef
Const conPATH As String = "C:\Documents and Settings\arthur\Desktop\Survey_1011\"
 
Set MyDB = CurrentDb
Set rstEmployees = MyDB.OpenRecordset("SELECT tblEmployee.empLastName FROM tblEmployee", _
                        dbOpenForwardOnly)
 
Set qdf = CurrentDb.QueryDefs("qryEmployeeSurvey")
 
With rstEmployees
  Do While Not .EOF         'Loop through each Employee
    qdf.SQL = "SELECT tblEmployee.uid, [empFirstName] & ', ' & [empLastName] AS Employee, " & _
              "tblEmployee.empUU1, tblEmployee.empUU2, tblEmployee.empUU3, tblEmployee.empUU4, " & _
              "tblEmployee.empUU5, tblEmployee.empGender FROM tblEmployee " & _
              "WHERE tblEmployee.empLastName = '" & ![empLastName] & "';"
      DoCmd.OutputTo acOutputQuery, "qryEmployeeSurvey", acFormatXLS, conPATH & _
                                     ![empLastName] & "_Survey.xls", False
      .MoveNext
  Loop
End With
 
rstEmployees.Close
Set rstEmployees = Nothing

[artemetis]

Beautiful!
This did work perfectly...last problem and I will be out of your hair!

I did join two tables for the query and replaced with this code and getting error?

SELECT [empLastName] & ',' & [empFirstName] AS EmployeeName, tblEmployee.empLastName, tblDept.empUid, tblDept.deptCampus AS [Campus (Building Location)], tblDept.deptBin AS Bin
FROM tblDept INNER JOIN tblEmployee ON tblDept.uid = tblEmployee.uid
ORDER BY tblEmployee.empLastName;

[ADezii]

1. What is the Error you are getting?
2. Try removing Parenthesis from the Expression:
3. Instead of
   

   tblDept.deptCampus AS [Campus (Building Location)], 

4. try:
   

   tblDept.deptCampus AS [Campus Building Location], 

[artemetis]

That did it!
Now instead of only have the record for each person in the xls file, it's putting the records for all people in the file.

so, employee1_survey.xls contains all the data across for employee1, employee2, employee3...

employee1_survey.xls should only show data for this employee.

See screenshot.

[ADezii]

The Code appears to be sound, can you Upload the DB?

[artemetis]

sure thing.
thanks!

[ADezii]

You forgot one little item, the WHERE Clause in the SQL Statement, as indicated in Code Line #18:

Dim MyDB As DAO.Database
Dim rstEmployees As DAO.Recordset
Dim qdf As DAO.QueryDef
Const conPATH As String = "C:\HOLD\"
 
Set MyDB = CurrentDb
Set rstEmployees = MyDB.OpenRecordset("SELECT tblEmployee.empLastName FROM tblEmployee", _
                        dbOpenForwardOnly)
 
Set qdf = CurrentDb.QueryDefs("qryEmployeeSurvey")
 
With rstEmployees
  Do While Not .EOF         'Loop through each Employee
    qdf.SQL = "Select[empLastName] & ',' & [empFirstName] AS EmployeeName,  " & _
              "tblEmployee.empLastName, tblDept.empUid, tblDept.deptCampus AS [Campus (Building Location)],  " & _
              "tblDept.deptBin AS Bin " & _
              "FROM tblDept INNER JOIN tblEmployee ON tblDept.uid = tblEmployee.uid " & _
              "WHERE tblEmployee.empLastName = '" & ![empLastName] & "' " & _
              "ORDER BY tblEmployee.empLastName;"
 
    DoCmd.OutputTo acOutputQuery, "qryEmployeeSurvey", acFormatXLS, conPATH & _
                                     ![empLastName] & "_Survey.xls", False
      .MoveNext
  Loop
End With
 
rstEmployees.Close
Set rstEmployees = Nothing

[artemetis]

Bam!

That was it.
And you did say:
"Pay particular attention to Code Lines 4, 18, and 19"

Thanks for all of you help!

[ADezii]

You are quite welcome.