1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Sending a single record from Access to Word

Discussion in 'Business Applications' started by johnhouk, Jan 15, 2002.

Thread Status:
Not open for further replies.
Advertisement
  1. johnhouk

    johnhouk Thread Starter

    Joined:
    Dec 20, 2001
    Messages:
    17
    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
     
  2. stepheno

    stepheno

    Joined:
    Jan 3, 2001
    Messages:
    282
    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
     
  3. johnhouk

    johnhouk Thread Starter

    Joined:
    Dec 20, 2001
    Messages:
    17
    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.
     
  4. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Sure, you can do that. How comfy are you with VBA?
     
  5. johnhouk

    johnhouk Thread Starter

    Joined:
    Dec 20, 2001
    Messages:
    17
    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.
     
  6. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    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...
     
  7. johnhouk

    johnhouk Thread Starter

    Joined:
    Dec 20, 2001
    Messages:
    17
    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
     
  8. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    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.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/64994

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice