Referencing Textbox Input from Access Form in VB Module

Question

I'm currently working in access creating a database that will send all of the people I've designated an email every month with files relevant to them and am trying to reference a textbox entry where the user will enter a date from an Access form in an access module. More specifically, I'm attempting to grab the text from the textbox and put it into the file path that I'm attempting to save each file to.

Instead of babbling on, here is a sample of what's not working in my code:

    Dim timestamp as String
    Dim territory as String
    DoCmd.OpenForm "Sales Email"
    Forms![Sales Email]![FromDate].SetFocus

    territory = rs.fields(1)
    timestamp = Forms![Sales Email]![FromDate].Text

    DoCmd.OutputTo acOutputQuery, "TheNewQueryDef", acFormatXLS, "\\cletus\data\accounting\KRISTEN\Monthly Sales for " & territory & " " & timestamp & ".xls", False

Specifically it's telling me: "Run-time error '2302': Microsoft Office Access can't save the output data to the file you've selected."

Side note: Before I began working on adding in the timestamp variable (which I'm trying to get to reference the date entered in the form's textbox) to the file path, it worked perfectly how I needed it to.

Any input would be greatly appreciated! And sorry if I'm not clear - I'm still somewhat of a beginner when it comes to code. If more clarification is needed, please say so.

Check the full path of the output file which you're using in DoCmd.OutputTo. Do you know how to do that? — HansUp
– HansUp, Commented Mar 17, 2016 at 13:46
I figured it out and checked it. The file path seems to be correct. Like I said, before I began trying to add in the timestamp variable into the file name, the code worked exactly how I needed it to. — lucky123
– lucky123, Commented Mar 17, 2016 at 14:00

Andre · Accepted Answer · 2016-03-17 14:30:42Z

2

Most probably your timestamp string contains characters that are illegal in a Windows file name,
e.g. / or :

If it's a date with time, try something like this:

timestamp = Format(Forms![Sales Email]![FromDate].Value, "yyyy-mm-dd hh-nn-ss")

which also makes for better sorting.

Note: by using .Value instead of .Text you don't need this line:

Forms![Sales Email]![FromDate].SetFocus

answered Mar 17, 2016 at 14:30

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Referencing Textbox Input from Access Form in VB Module

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related