Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open a .doc fie from within access based on form (2003)

    Hi,

    I want to be able to open a word .doc file based on the information currently been viewed in a form.

    For example:

    Customer 1234 is part of group 'A'
    Customer 2212 is part of group 'A'
    Customer 4321 is part of group 'B'

    The .doc files are filed by group and then by the first 2 numbers of the customer code.

    Example
    Customer 1234 .doc file is located in c:customer lettersACustomer starting 10 to 191234.doc
    Customer 2212 .doc file is located in c:customer lettersACustomer starting 20 to 292212.doc
    Customer 4321 .doc file is located in c:customer lettersBCustomer starting 40 to 494321.doc

    The form containing the button 'View Customer Letters' displays the current 'CRN' (Customer Number) and 'Group'

    I need some kind of.

    IF 'GROUP' IS 'A' AND "CRN" IS >01 AND <09 THEN C:CUSTOMER LETTERSACUSTOMER NUMBER 01 to 09
    AND OPEN DOC "[CRN].doc"

    I have around 30 Groups and folders in each group starting 01-09, 10-19, 20-29, 30-39...... upto 90-99

    I was hoping it would be possible to create a table with the directory info incase directories need to be moved or add to etc this will save editing the code in the long term development of the database

    Many thanks in advance.
    Regards
    Gerbil (AKA Kevin)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open a .doc fie from within access based on form (2003)

    I'd put all documents in a single folder, that would make it a lot easier...

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a .doc fie from within access based on fo

    Unfortunatly I am unable to do this there are more than 500'000 .doc files, and the same CRN can be used i more than one group.

    Example: A customer can have a letter sent by dept A which would be filed as lettersdept acrn starting 01 to 09012232.doc
    and the same customer may have a letter sent by dept B which would be filed as lettersdept bcrn starting 01 to 09012232.doc

    So in theory a customer could have a file in every dept. all with the same file name. I am definatley unable to move the filing structure its out of my hands!

    Your help is always gratfully received

    Kevin
    Regards
    Gerbil (AKA Kevin)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open a .doc fie from within access based on fo

    Say that you place a command button cmdOpenDoc on your form, with caption "Open Document" (or similar).
    Create an event procedure for the On Click event of the command button:

    Sub cmdOpenDoc_Click()
    Dim strPath As String
    Dim intStart As Integer
    intStart = CInt(Left(Me.CRN, 1))
    strPath = "C:Customer Letters" & Me.Group & "" & _
    "Customer starting " & 10 * intStart & " to " & _
    (10 * intStart + 9) & "" & Me.CRN & ".doc"
    Application.FollowHyperlink strPath
    End Sub

  5. #5
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a .doc fie from within access based on fo

    Thanks Hans,

    I modified your code to suit and works great, I had issues with files begining 01 - 09 so added an IF statement see below. Is there a way I can reference the 'E:Customer Records to another table called 'TblFileLoactions' where I have a list of descriptions and there locations, then I wouldn't have to change the code if they move the server location. The table would contain more than one description and location for different parts of the database so the Customer form would have to know how to pick the right record. Thank you again!

    Private Sub Command4_Click()
    Dim strPath As String
    Dim intStart1 As Integer

    If CInt(Left(Me.CRN, 1)) < "1" Then

    intStart1 = CInt(Left(Me.CRN, 1))
    strPath = "E:Customer Letters" & Me.Spec & "" & _
    "CRN " & intStart1 & "1" & " - " & _
    (intStart1 & "9") & "" & Me.CRN & ".doc"
    Application.FollowHyperlink strPath

    Else

    intStart1 = CInt(Left(Me.CRN, 1))
    strPath = "E:Customer Letters" & Me.Spec & "" & _
    "CRN " & 10 * intStart1 & " - " & _
    (10 * intStart1 + 9) & "" & Me.CRN & ".doc"
    Application.FollowHyperlink strPath

    End If
    End Sub
    Regards
    Gerbil (AKA Kevin)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open a .doc fie from within access based on fo

    Create a table tblFileLocations like this

    <table border=1><td align=center>Description</td><td align=center>Location</td><td>CustomerDoc</td><td>E:Customer Letters</td><td>...</td><td>...</td></table>
    You can then replace "E:Customer Letters" in the code with
    <code>
    DLookup("Location", "tblFileLocations", "Description='CustomerDoc'")</code>

  7. #7
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a .doc fie from within access based on fo

    Hi again, Im affraid I still have problems with the code, after testing at work I find that 'SPEC' field in the Form is not the same as the File Location

    Example. SPEC = GSUR on the form but the file location = General Surgery

    I need to do something similar to the root location Dlookup you gave me earlier where Me.SPEC = but I cant get it right heres what I got so far

    It is giving C:LETTERSCRN 01 - 09012345.doc Notice its missing the SPEC and uses

    Many thanks

    strPath = DLookup("Location", "TblFileLocations", "Description='Patient Letters'") _
    & DLookup("Specialty", "TblSpecialtyCodes", "SpecialtyCode= " & Me.SPEC) & "" & _
    "CRN " & intStart1 & "1" & " - " & _
    (intStart1 & "9") & "" & Me.CRN & ".doc"
    Regards
    Gerbil (AKA Kevin)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open a .doc fie from within access based on fo

    Try changing
    <code>
    DLookup("Specialty", "TblSpecialtyCodes", "SpecialtyCode= " & Me.SPEC)
    </code>
    to
    <code>
    DLookup("Specialty", "TblSpecialtyCodes", "SpecialtyCode= " & Chr(34) & Me.SPEC & Chr(34))
    </code>
    Text values must be enclosed in single or double quotes. Chr(34) is the double quote character ".

  9. #9
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a .doc fie from within access based on fo

    Thanks Hans, I will try it tomorow when im back at work.

    Is it possible to have a word .doc file open inside an access form, in read-only format and still open the correct file.

    Thanks.
    Regards
    Gerbil (AKA Kevin)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open a .doc fie from within access based on fo

    You could use an OLE control to display a Word document. If you set its Enabled property to No, the user can't edit the document, but (s)he won't be able to scroll through the document either.

  11. #11
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a .doc fie from within access based on fo

    Thanks Hans, works great, havent tried the OLE idea yet though.

    I am having trouble if the file does not exsit, it was coming up with a debug prompt, I got it to display an information box say 'file not found' but now when the file is found it opens it and still displays the message box!
    Regards
    Gerbil (AKA Kevin)

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open a .doc fie from within access based on fo

    See <post:=688,881>post 688,881</post:> for a description how to create a generic error handler. You have to make sure that there is an Exit Sub above the error handling section, otherwise you'll get an error message even if there is no error.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •