forms!Formname!control/ docmd transfer spreadsheet

[corkster]

I am trying to export to excel using docmd transfer spreadsheet. I
want to export each employee to a different workbook dynamically.
Hence my understanding is forms!Formname!contol will do this.

This is an overly simplification of what I am doing (for my
understanding). I have received help earlier from this group on this
and have used the ideas provided to me from that posting.

When the code gets to the sqltxt=Select Where empl!employee name = &
Forms![Form3]![employee name] I get the following error "Syntax error
(missing operator) in query expression '[empl]![Employee Name] =
Forms![Form3]![employee name]"

Notes: Query 3 lists all employees
Query 1 I have the following sql SELECT empl.empid,
empl.[Employee name]
FROM empl
WHERE ((([Forms]![Form3]![Employee name])<>False));
empl is a table

I hope somenone can help. I use Access 2003


Dim sFile As String


Set Mrecs = CurrentDb.OpenRecordset("Query3") 'Query 3 lists all the
employees


If Mrecs.RecordCount > 0 Then
With Mrecs
..MoveFirst
Do While Not .EOF

sFile = "C:\somefolder\" & ![employee] & " " & Format(Date,
"mm-dd-yyyy") '& " " & Date Target path and file name
Forms![Form3]![employee name] = ![employee] 'put actual names here
'sqltxt = "Delete * From empltemp"
'DoCmd.RunSQL sqltxt
sqltxt = "Select * Where [empl]![employee name] = &
Forms![Form3]![employee name]"
DoCmd.RunSQL sqltxt
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"empl", sFile, True, "Export" & ![employee]
..MoveNext
Loop
..Close
End With
End If

 

[corkster]

Not sure if I am way off on my code or if what I wrote caused
confusion. But can anyone help on this?

Thanks
C

 

[John Nurick]

On 28 Apr 2006 10:34:13 -0700, "corkster" <[email protected]>
wrote:


This line

sqltxt = "Select * Where [empl]![employee name] = &
Forms![Form3]![employee name]"

does not create a valid SQL statement: it needs a FROM clause. But it
shouldn't raise an error; that comes when the next line tries to execute
it:

 

[corkster]

I have tried it both ways and get the same error

sqltxt = "Select * from [empl] Where [empl]![employee name] = " &
Forms![Form3]![employee name]
DoCmd.RunSQL sqltxt
or

sqltxt = "Select * [empl] Where [empl]![employee name] = " &
Forms![Form3]![employee name]
DoCmd.RunSQL sqltxt

 

[John Nurick]

There are at least two problems here. First, neither version creates a
valid SQL statement. Here's what it needs to look like (assuming that
[empl] is a text field:

sqltxt = "SELECT * FROM [empl] WHERE [empl].[employee name] = """ _
& Forms![form3]![employee name] & """;"

The [brackets] round the names are only needed to prevent ambiguity. IN
this case, you could use

sqltxt = "SELECT * FROM empl WHERE [employee name] = """ _
& Forms!form3![employee name] & """;"

The second problem is that even when you have a valid SQL SELECT
statement, RunSQL won't accept it. RunSQL executes "action queries" -
ones that change the data (append or delete or whatever) but don't
actually return a recordset. A SELECT statement doesn't do anything to
the data, and the only reason to use one is to return a recordset (e.g.
as the data source for a form or another query). What do you want the
SELECT operation to achieve?

I have tried it both ways and get the same error

sqltxt = "Select * from [empl] Where [empl]![employee name] = " &
Forms![Form3]![employee name]
DoCmd.RunSQL sqltxt
or

sqltxt = "Select * [empl] Where [empl]![employee name] = " &
Forms![Form3]![employee name]
DoCmd.RunSQL sqltxt

 

[corkster]

John

Thanks for the input. What I was trying to do was to understand what
Forms![Form3][employee name] was actually doing. I am trying to run
Query1 which uses the following select statement

SELECT empl.empid, empl.[Employee name]
FROM empl
WHERE (((Forms!Form3![Employee name])<>False));

Using my original posting code. I wanted the query to automatically
run for each employee without having to manually build a query for each
employee. The using the Transferspreadsheet to excel for each employee
in a separate workbook. How would I code this to do that?

Also, Do you know where I can find a step by step explaining what the
Forms!Formname!control specifically does as well as any documentation
that would be helpful to read for the other aspects of the code?

Thanks
C

 

[John Nurick]

1) The syntax
Forms!Formname!ControlName
is short for
Forms("Formname").Controls("Controlname").Value
.. The long form makes it clear that it gives you the value of the
control called "Controlname" on the form "Formname" (provided that the
form is loaded.

In VBA code the two syntaxes are mostly interchangeable. In SQL they are
not: as far as I know you can never use the long form in a SQL
statement. Even the short form can only be used in SQL in an Access
query, to get a parameter for the query from a form.

If you're building a SQL statement in VBA you can't do it that way: you
have to get the value of the control yourself and include it in the
string. So this in a query

SELECT * FROM MyTable WHERE EmployeeName = Forms!MyForm!EmpName;

has to be done along these lines in VBA:

strSQL = "SELECT * FROM MyTable WHERE EmployeeName = """ _
Forms!MyForm!EmpName& """;"

Note the quote marks. If EmpName contains "John"), this gives you

SELECT * FROM MyTable WHERE EmployeeName = "John";


2) Do you want to export the data for whichever employee is shown in
Forms!Form3![Employee Name], or for all employees (each to a separate
Excel file?

John

Thanks for the input. What I was trying to do was to understand what
Forms![Form3][employee name] was actually doing. I am trying to run
Query1 which uses the following select statement

SELECT empl.empid, empl.[Employee name]
FROM empl
WHERE (((Forms!Form3![Employee name])<>False));

Using my original posting code. I wanted the query to automatically
run for each employee without having to manually build a query for each
employee. The using the Transferspreadsheet to excel for each employee
in a separate workbook. How would I code this to do that?

Also, Do you know where I can find a step by step explaining what the
Forms!Formname!control specifically does as well as any documentation
that would be helpful to read for the other aspects of the code?

Thanks
C

 

[corkster]

I would like to export each employee to a separate excel file.

 

[John Nurick]

The general idea is this:

1) Create a query. It doesn't much matter what it does because the VBA
code is going to modify it. Let's call it qryTemp.

2) Create another query that returns a list of the employees whose data
you want to export (with the [empid] fields from your empl table, I
guess). Let's call this qryEmployeesToExport.

3) The VBA will be like this air code.

Dim strSQL As String
Dim dbD As DAO.Database
Dim qdfExport As DAO.QueryDef
Dim rsEmployees As DAO.Recordset
Dim strFileSpec As String

'Preparations
Set dbD = CurrentDB()
Set qdfExport = dbD.QueryDefs("qryTemp")

'open recordset with list of employees
Set rsEmployees = dbD.OpenRecordset("qryEmployeesToExport", _
dbOpenSnapshot)

'iterate through recordset
Do Until rsEmployees.EOF

'set the SQL statement in the query.
' NB: if the key field (empid) is a text field you need to
' use additional quotes as in the example in my previous
' message.
qdfExport.SQL = "SELECT * FROM TheTable WHERE [empid]= " _
& rsEmployees.Fields("empid").Value & " ORDER BY somefield;"

'generate the filename
strFileSpec = "C:\somefolder\" _
& rsEmployees.Fields("employee").Value _
& " " & Format(Date, "mm-dd-yyyy")& ".xls"

'export query to file
DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9, _
"qryTemp", strFileSpec

'move to next record ready for next time through loop
rsEmployees.MoveNext

Loop

 

[corkster]

John

Thanks for taking the time on this, I am sure you are busy. I will
walk through this and let you know how it goes.

Corkster

 

[corkster]

John

I was able to get it to work. Thanks so much for all of your patience
and help on this. Your code worked just fine.

Corkster

 

[John Nurick]

I'm glad. I hope it also helps you tackle the next problem<g>