Sending a single record from Access to Word

I need to add a command button to a form which sends the current record to a word doc containing corresponding fields.

(I will be using this to create corospondance with customers and vendors conserning complaints and material certification letters.)

This seems like a very common place function, however I was unable to find it mentioned in any other thread.

Thanks in advance for any help,
John

 

IMHO you can't export a record from a form. Why not just create a query with criteria in the ID field and design a report to look like a letter or whatever. Then create a macro and attach it to a button on your form. When you run the command it'll ask you for the record ID and then print it out.

Hope that helps.

stepheno

 

I was hoping for something more along the lines of how to export the current record to a text file to be used as a single record data source.

 

Sure, you can do that. How comfy are you with VBA?

 

Wellll. I had taken a corce in vb6 in college a few years back. I found that to be a brease. As for vba, im unfamiliar with what "librarys" I have at my disposal. Since its been a while and I dont know these methods, the syntax is best spoon fed to me, I'm asshamed to say. But once I read somthing I can usually pick apart its meaning, using the define & help commands in the right click context menus. So Im not totally clueless, but im not at the level I should be either.

I was thinking of maybe having the code create a report using an sql query which contains the contents of the pk field for the current record as the where clause.
something like: (remember im guessing at the syntax)
....
where record_number = vbcurrentrecord.record_number.value

Then usinging cmd.outputto (or similar) to output that single record report to a datasource.txt to be used by word.

 

Okay. We'll give it a shot, anyway. Here's how to do it:

1. Read up in Access help, or a good Access book, to get (a little) comfortable with the VBA interface and structure, a little bit about class modules behind forms, and about the syntax for referring to form controls, etc. I just can't take the time it would take to explain all that to you. (Tho obviously if as you work through this you have specific questions, I and others can help you answer them.)

2. Create a new module (not behind a forum, just a regular old module, off the db window). Open it in the VB Editor (double-click the module, or whatever). You'll want to paste the following code into that module (sorry if you have to scroll over in your browser to read a line or two, I didn't want it to break lines):

Code:

Public Sub Write_Log_File(ByVal pstrFilNm As String, _
                          ByVal pstrLog As String, _
                 Optional ByVal pblePathInFilNm As Boolean = False)
    
    Dim fsoSysObj   As New FileSystemObject
    Dim filTxtFile  As File
    Dim txsStream   As TextStream
    Dim strPath         As String
    Dim strFilAndPath   As String
    
    If Not pblePathInFilNm Then
        strPath = CurDir & "\"
        strFilAndPath = strPath & pstrFilNm
    Else
        strFilAndPath = pstrFilNm
    End If
    
    On Error Resume Next
    Set filTxtFile = fsoSysObj.GetFile(strFilAndPath)
    If Err <> 0 Then
        Set filTxtFile = fsoSysObj.CreateTextFile(strFilAndPath)
        Set filTxtFile = fsoSysObj.GetFile(strFilAndPath)
    End If
    On Error GoTo 0
    
    Set txsStream = filTxtFile.OpenAsTextStream(ForAppending)
    txsStream.WriteBlankLines 3
    txsStream.WriteLine Now
    txsStream.WriteLine "---------------------------------------------------"
    txsStream.Write pstrLog
    txsStream.WriteBlankLines 3
    txsStream.WriteLine "---------------------------------------------------"
    txsStream.Close
    
    MsgBox "Text file written to " & filTxtFile.Name
    
    Set filTxtFile = Nothing
    Set fsoSysObj = Nothing
    
End Sub

This is a generic procedure which writes any text pstrLog to a file named pstrFilNm; if the variable pstrFilNm contains a path, set pblePathInFileNm to True, otherwise it will write to your current directory (more on this later).

Very important: in order for this code to work, you must set a reference to MS Scripting Runtime. In the VB Editor, choose tools > references. Scroll down until you see it in the list; check it. Choose Debug > Compile afterwards to make sure your code is okay.

3. Now you'll have to write the code in which you send the form contents to the file--the procedure in the form's module that calls the code above. I'm pretending you have three controls on your form called FirstNm, LastNm, and PhoneNum, just for the example--you can use whatever control(s) you want once you get the idea. I'm further pretending that your command button is called cmdWriteRecToTxtFile. You'll need to use your own names, of course. But using mine, the procedure would look about like this:

Code:

Private Sub cmdWriteRecToTxtFile_Click()

    Dim strTxt as string

    strTxt = Me!FirstNm & vbNewLine _
     & Me!LastNm & vbNewLine _
     & Me!PhoneNum

   Write_Log_File "C:\MyRec.Txt", strTxt, True

End Sub

That's it. You click the button; it will write the contents, as well as some blank lines and stuff around it (you can play with those lines once you understand the code) to a file called MyRec.Txt, on the C root directory. (The same line without directory would read Write_Log_File "MyRec.Txt", strTxt). If the file exists, it adds the record on; if it doesn't exist, the procedure creates it.

Let us know how this works for you, any questions you might have...

 

So far works like a charm,
It took me about 2 miniutes from reading your post to generate the file.
Now, I need to ajust the Write_Log_File routine so it does not append seperate records.

I think I need to change the
Set txsStream = filTxtFile.OpenAsTextStream(ForAppending)
Line. Or maybe Its to be removed? I'm not sure, just guessing.
Also I needed to construct the output to resemble a csv or similar type file. You can make a suggestions/corrections on this, This is what i used:
strTxt = "complaint_number|vendor_name|vendor_contact~" & Me!complaint_number & "|" & Me!vendor_name & "|" & Me!vendor_contact & "~"
(I chose to use the | and ~ for delimiters because I dont expect users to enter them, so far it works well as far as using it as the data source for words mail merge.)

You know, based on all that I have read in these forums, I knew you would be the one with the answer.
Thanks again

 

Good, glad it works (more or less). You know that you could also directly mail-merge from Access? Well, anyway.

If you never want to use a file, always overwrite, you can just delete it, which as I recall is done with the Kill command, can't be sure, check VBA help on that one...

As far as CSV, you can also use the TransferSpreadsheet command (with a query, not a form) to write a real CSV file. If you'd rather. Otherwise, concatenating with | and ~ seems fine to me, for what (as I understand it) you're trying to do.