VBA script - export selected record to a text file 1

[Excelerate2004]

Hello,

I've taken a look at previous posts on this issue, but all solutions seem much more complicated than what I'm trying to do.

I have a simple 1-table access database, whereby I want to export 1 field of a selected record to a text file. This field that I require to be exported is text and will probably contain between 100-300 characters.

I will create a form whereby when the user is moving thru the recordset they can click a button on this form to export the selected field to a text file.

How can I go about doing this?

Here are the names of the fields that I have in my database:

pubid
author
citation
subject
organization
date
pageref
Notes

The table is called tblpubs.

Thanks for any help I can get!

 

[Remou]

Hi
You could try:

Private Sub cmdOutput_Click()
    Open "C:\AFile.txt" For Output As #1  'Open file for output.
    Print #1, Me!Subject
    Close #1
End Sub

There is also Write, if you want quotes, and FileSystemObject, if you want fancy.

 

[Excelerate2004]

Remou Ive tried your suggestion however I get a null value in the output text file? What am I missing?

 

[Remou]

Hi Excelerate. A few thoughts (please forgive over-simplification):
- This code is intended to run from a command button on a form
- Me!Subject is a valid field on the form
To test, you could try text: Print #1, "Hello"
If the file fails or does not close for some reason, you will get a null. You will also get a File is Open error, if you try to run again. You can close the file by running Close #1 by itself in a module.
I tested before I posted, so that's all I can think of for now.

 

[Excelerate2004]

I printed the Hello and that worked, I also tried setting up the Close#1 in a seperate module as well, but as for the actual output of anything else its a no go. I still get the NULL in the text file.

Thanks for helping.

 

[Remou]

If you can print hello, then the file is closing properly, so the problem would appear to be the field. Have you attached the code to a command button on a form? Does the form have a field with data in it? That is, make sure that:
[tt]Print #1, Me!Subject[/tt]
Is a valid field. One way to check if you have a valid field is to put:
[tt]MsgBox Me!Subject[/tt]
Into the code at the beginning and see what that gives you.

 

[Excelerate2004]

After trying your suggestion above with outputting via the msgbox, I get an error that says:

Runtime error '94'
Invalid use of Null

and of course I still get a NULL value when I use the debugger to see what the msgbox actually contains.

So it must be an issue with that field? When I created the form I used the wizard. I'll try and create a new form without using the wizard, but first I'll check the properties on that problem field.

Thanks

 

[Excelerate2004]

I recreated the form without using the wizard and I still get the same NULL value error when I click the button that runs the export procedure...is there something I could check in the properties for that particular field??

 

[Remou]

Hi Excelerate2004
You will get these errors if there is nothing in the field. It is something you need to trap. Going back a bit:

Private Sub cmdOutput_Click()
If IsNull(Me!Subject) Then
    MsgBox "No data available"
Else
    MsgBox Left(Me!Subject,20) & " ... will be written to a file"
    Open "C:\AFile.txt" For Output As #1  'Open file for output.
    Print #1, Me!Subject
    Close #1
End If
End Sub

The above should show you when your field is null. You could try this on a field with data in it and a field with no data in it.
Just as a PS, a post like this might be better in the Access Forms forum.

 

[Excelerate2004]

After trying your last suggestion it displays a field, however its the wrong one.

I looked at its tab index and its 3 however the field I want copied to the text file has a tab index of 2. I switched them but havent had any luck.

How do I change what field is displayed?

Hmm?? Almost there...

 

[Remou]

Hi
What is the name of your field and what type (textbox, combobox) of field is it? The tab index has very little to do with this particular problem.

 

[Excelerate2004]

I figured out my mistake, its very bad lol Embarassing!!

I misunderstood this line:

Me!Subject

I know that ME! means the current form, but I thought that Subject was a part of the VBA code that specified that this was the current field that was given focus and not the actual field that I had named subject Ugghh!! Rookie mistake, very sorry, maybe someone can learn from it!!

Thanks again!

 

[Remou]

I should have explained a bit more.

 

[Excelerate2004]

A few final things Remou, is it possible to have the text file pop up on screen/on top of the already running access database program once my export to text file button is clicked? Rather than have to go looking for the file.

As well is it possible to do absolute & relative paths for a file so that if I wanted to deploy this to other systems I could use the relative path?

i.e.

ABSOLUTE path: Open "C:\Documents and Settings\bam\Desktop\AFile.txt" For Output As #1

Attempted RELATIVE path: Open "\..\..\..\..\AFile.txt" For Output As #1

Thanks

 

[Remou]

Hi
I have tried the above with relative paths and it works, but it sounds quite unsafe, especially if you are deploying to other systems. You can easily open any file with Shell.

 

[pbrodsky]

Just a thought, what happens after this value is saved as a text file -- it sounds as thought there may be some additional steps that can be automated.

 

[Excelerate2004]

pbrodsky - Well once the value (citation or reference for a scientific publication) is saved to the text file the user will copy and paste that into a word doc.

What it is in fact, is a publication database with various fields that capture information about the publication that the users will be able to search and find indexed information.

Its main purpose as stated above is that once the citation data is exported to the text file the user can then copy & paste that info to a word doc in their own reference section to cite the publication if required.

I'm wondering how can I overwrite the value that I export to the text file each time. Right now it just exports once and holds on to that value until I delete it.

Thanks

 

[pbrodsky]

Why not just let the user copy and paste from your Access form? Then you could do without the text file altogether.

 

[Excelerate2004]

They could do that if they wanted, they requested this feature to export to a text file however and so I'm just accomodating