DoCmd Output To doesnt output textbox value

[stepone]

Hello,

I have a query which outputs the value of a txtbox on my form. So in the query I have a field which looks like this ;

Approver: [forms]![frmMain]![txtApprover]

This works fine when I run the query, but when I use docmd.outputto to export it to Excel, the Approver column is blank.

I get the same issue in Access 2000 and 2002

Any suggestions ?

Thanks
StepOne

 

[rainman89]

you are using the query to populate the table then typing the persons name in the textbox?

does it show in the query? or even better, in the table?

 

[stepone]

The value does show in the query. It's not stored in the table - it's an unbound text box on my form. When the user wants to export the details to Excel, they put their name in the text box. The 'Approver' column in the query then displays their name against each record. That works fine.

But they need the data in Excel format for uploading to another system. I use DoCmd.OutputTo to export the query to Excel - but in Excel the 'Approver' column is blank. The column title is there, but against each record the value is blank. So it looks fine in actual query, but the Export to Excel does not pull through the value from the textbox.

I've tried this at work, and at home, on separate PCs and different versions of Access/Excel (2K and 2002) and get the same problem.

Thanks for any help you can give,
StepOne

 

[Brianwarnock]

I hope you don't think this is a daft question but is the Form still open when you execute the Docmd

Brian

 

[stepone]

Hi there,

Yes the form is still open. In fact, while testing, the VBA code opens the query AND outputs to Excel. The query displays fine - with the Approvers name against each record, but in the Excel file it is blank.

While searching I have since found a workaround - create an SQL string including this ;

me.txtApprover AS Approver

and change the QueryDef on the fly. Seems a clunky way to have to do it and I just wondered if this is a known problem with DoCmd.OutputTo.

Cheers,
StepOne

 

[Brianwarnock]

The reason I asked about the form was because I had passed info from a form to Excel, on checking it wasn't as simple as yours and I did a quick check and guess what would work

Approver: iif(Not IsNull([forms]![frmMain]![txtApprover]),[forms]![frmMain]![txtApprover])

I know I am.

Brian

 

[stepone]

Thanks - I am also confused, but it worked !

Cheers,