OutputTo Excel File Name

[Eric]

I have a button on form that exports out a select query to an Excel
spreadsheet using the OutputTo function. The button works great. It
does everything I want it to (thanks to the this group). However,
there is one thing that I cannot get it to do, and that is get the
correct file name. I am managing several projects and I am keeping a
database of QC tracking for various projects. I built a form called
Project Selector that allows me to select a specific project from an
unbound combo box which creates a filter to show me only the data from
that Project. The combo box is populated by the project names pulled
from a table with the project names and an ID field, using the
following code:

SELECT domProject.ProjectID, domProject.ProjectName FROM domProject
ORDER BY domProject.ProjectName; .

The query, report, and the other forms are linked back to the project
ID. My OutputTo code is as follows:

Dim stDocName As String

stDocName = "C:\PFData\" & Me.ProjectName.Value & "QC" & ".xls"
DoCmd.OutputTo acOutputQuery, "qryProjectQC", acFormatXLS,
stDocName, False

The filename that it creates is 1QC.xls or 5QC.xls. 1 and 5 being the
Project ID. How can I get to name the file using the project name
instead of the project ID?

Thanks,
Eric

 

[Jeanette Cunningham]

Eric,
Me.ProjectName.Value gives you the ProjectID from the combo? is this
correct?
Assuming ProjectName is the name of the combo on the form?
If Yes, then use Me.ProjectName.Column(1) instead of Me.ProjectName.Value.
Note: you don't need to use .Value at all.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Eric]

Eric,
Me.ProjectName.Value gives you the ProjectID from the combo? is this
correct?
Assuming ProjectName is the name of the combo on the form?
If Yes, then use Me.ProjectName.Column(1) instead of Me.ProjectName.Value.
Note: you don't need to use .Value at all.

Jeanette Cunningham -- Melbourne Victoria Australia

That worked perfectly. Thank you very much!!