MS Access and output to file

Here’s a quick question for any MS Access gurus out there.

I’m working on a very simple one-table, one-form Access database. Each record holds several blocks of text in separate fields. I’d like to add a button that will copy all of the blocks of text either to the clipboard or to a .TXT file - but only for that record.

I’m sure there must be a way to do this, but my Access skills are on the embarrassing side of rusty.

Similarly, is there an easy way to output the results of a filtered query to a text file formatted in a way I choose rather than to printer?

Can I recommend this BB: Access World Forums - the folks there are really helpful, you’re bound to find what you need.

In response to your actual question, yes, the sorts of things you’re wanting can be done fairly easily in Access, but will involve a little VBA programming, for example, to output the details of the current record to a file, you need something like:


Private Sub YourButton_Click()

Open "c:\output\OutFile.txt" For Output As #1
Print#1, Me.Myfieldname1 & Me.Myfieldname2
Close #1

End Sub

To export a query as text, you need to use DoCmd.Transfertext (this can be done from a macro or VBA)

HTH

I fixed the code (I think) --manhattan

[Edited by manhattan on 07-06-2001 at 08:30 AM]

Gaahh, that was meant to be the close tag for the code block; I wonder if a mod would be kind enough to edit it for me… pleeeease?

Mangetout, you are a gentleman and a scholar.

How do I change that code to get Access to prompt for a savename, and to open that file in Notepad?

There are more complex methods (like opening the common dialogs control), but this works:


Private Sub YourButton_Click()
Dim YourFileName as string
YourFileName = InputBox("Please Enter Destination filename")
Open ("C:\output\" & YourFileName & ".txt") For Output As #1
Print#1, Me.Myfieldname1 & Me.Myfieldname2
Close #1
DoEvents '(makes sure Access is finished closing the file before we do the next bit)

Shell ("c:\windows
otepad.exe" & " " & "C:\Output\" & YourFileName)

End Sub

Alternatively, if you want the user to enter a filename into a field on your form, you could replace the line above with:


YourFileName = Me.SomeFieldName

NB: the above code carries out no validation on the entered filenames etc, so it’s not all that safe in it’s present state.

Thanks again, that should do the trick.