Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autofill Word Document from Access (2000)

    Hi,
    I'm just interested whether or not it is possible to self-fill a formatted document in Word 2000, with the information that is inputted into a form in Access 2000.
    I currently have a FrmMain with FrmPayments (sub) that data is entered onto. The relationship is a One(FrmMain) To Many (FrmPayments) , I would like to have a control that when clicked extracts data from FrmMain & FrmPayments and places them in a word document.
    Would the way to do this be a query based form, with the populated letter being a report? Or can this literally be done in Word?
    The following information would need to be carried from these fields:
    (FrmMain/TblMain)
    Defendant
    URN
    Date of finalisation
    (FrmPayments/TblPayments)
    Advocate (This would relate to all entries in FrmPayments/TblPayments to the singular entry in FrmMain/TblMain)

    The address would be carried from FrmPayments/TblPayments, field = Chambers.

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

    Re: Autofill Word Document from Access (2000)

    Nigel,

    There are three ways you can do this:

    1. Create a report displaying the desired information, and export the report to Word (or to be more precise, to RTF.) The export can be done from a command button on the form. This is the easiest solution, but the export is not ideal - only text is exported, graphics such as lines are lost, and in general, you'll have to touch up the result to make it presentable. Still, it recommend exporting a report to RTF (Tools | Office Links | Publish it with Microsoft Word) as a test to see how you like the result.

    2. Use mail merge to populate fields in the Word document with data from Access. This is often an attractive solution, because you can use the power of Word to format the document the way you want. You would have to create a query that contains the data you need in Word, and use that as data source for the mail merge. This query can be created on the fly, if necessary. See Managing Merges with Access on WendellB's website.
    Note: the fact that you want to use data from the form and from the subform is a complicating factor.

    3. Use Automation to control Word in VBA code from Access. This is very powerful, since you can completely control the way the information is places in the Word document, but requires you to be confident with both Access VBA and Word VBA. See Automation 101 on the same website.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill Word Document from Access (2000)

    Thanks for the advice.
    The easiest way is probably the root that I will go. I've had a fiddle with the report side to see what it is like, and I think that it will be fine. The only problem that I am having is how to display the data relevant to the record in FrmMain, as opposed to all data in TblMain. I know its more than likely to be a really simple thing to do but.............

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

    Re: Autofill Word Document from Access (2000)

    Use a query as Record Source for the report. In the Criteria line for URN, enter [Forms]![frmMain]![URN]. This will restrict the report to the record in frmMain.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill Word Document from Access (2000)

    Hi Hans,
    Attached is the database.
    I think that I have overly complicate the queries. If you can spare 5 minutes, would it be possible to have a quick look for me? I won't explain what I'm tryinig to do as the queries will display that, but I am trying to add a txtbox on each report totalling all the records relevant to that Query.
    The only report that I am more or less happy with is RptLetter. My only problem with this is that I would have liked to produce letters with mutiple Advocates (Name) if the Chambers were equal, but I fear that this would overly complicate things from my perspective. There is also the issue of multiple Chambers, therefore producing multiple letters. I fear that this would get me in trouble.
    Thanks for your time and infinite patience.

    NB. Just an edit, I've managed to sort out the queries, its the multiple names for the Advocate on the report that I'm still stuck on.
    Attached Files Attached Files

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

    Re: Autofill Word Document from Access (2000)

    You can display multiple advocates for one URN on frmMain. How was this done? By using a subform.
    You want to display multiple advocates for one URN on rptLetter. How to do this? By using a subreport. This means that you must change the record source of the main report to include only the data needed there.

    It's not clear to me what you want to do with advocates from different chambers. The attached version contains a possible solution for rptLetter, but you will have to judge for yourself. I haven't touched the other reports, so I have deleted them, as well as other objects not used here. Don't replace your database with this version!
    Attached Files Attached Files

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill Word Document from Access (2000)

    Hi Hans,
    That's the sort of thing I was thing about.
    With regards to the advocates, a case may have several advocates deal with it throughout its life as a live file. 90% of the time the file is given to the same chambers and therefore Advocates from the same chambers deal with the file throughout the various Chambers. On occassion a file gets dealt with by a different Chambers and therefore a different Advocate. So I would need to produce a letter per Chambers.
    An example would be:
    Chambers = Octagon House. Guy Ayers & Stephen Ridley do hearings on the file. Case goes to trial for murder as victim has since died, case is dealt with from this point by Roy Almott QC of King's Bench Walk Chambers.
    Therefore a this case would have 2 Chambers on it, needing 2 letters.
    Thanks for the work you have done, I didn't actually realise you could do subreports!

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

    Re: Autofill Word Document from Access (2000)

    Try the attached version; it links the main and subreport not only on URN but also on Chambers, so that a separate letter for each Chambers is produced.
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill Word Document from Access (2000)

    Thanks Hans, just what I had in mind. Quite simple when you're sure off how too do things, I'll get there at some point!
    I do have one final question (honestly).
    I would like to have a Frm based on TblMain & TblPayments that allows the user to search for Archived data. This in itself isn't a problem as it would be done in a relatively simple query, picking up on the values in Archive on both forms. What I would like to be able to do is allow the user to effectively uncheck the archive box thus making it false and effectively live. I'm not too sure the best of doing this. I presume that a form could be based around, say, QryReactivateArchive that will find what cases are archived, but I'm not too sure how the user would go from this query to reactivating a case.
    Example:
    All payments have been made on SMITH file.
    SMITH file is archived.
    Hearing 3 months later on confiscation of property due to proceeds of crime.
    New payments need to be made on this file, but with the same CFIN number.
    Reactivate case.
    Hope this makes sense.

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

    Re: Autofill Word Document from Access (2000)

    That shouldn't be too hard
    - As you say, create queries that return archived records in tblMain and tblPayments.
    - Create a form and subform based on those queries.
    - Include the Archive fields on both forms. By default, they will be check boxes.
    The user can clear a check box to reactivate a record. Next time, it will magically reappear in the standard forms.

    Note: you may want code in the After Update event of the check box on the subform. If the check box on the subform was cleared, and the check box on the main form is ticked, it should probably be cleared too.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill Word Document from Access (2000)

    That makes sense. On reflection, it will only be the archived entry in TblMain that I will need to reactivate. All records in TblPayments will stay archived regardless.
    Based on this, I have created the relevant query which when activated by itself, runs fine. This data is displayed in FrmArchiveReactivate.
    When I try to run this process by opening FrmArchiveReactivate from FrmMain via CmdReactivate (on FrmMain) the data entered in the Parameter field displays nothing in FrmArchiveReactivate.
    Can't quite understand why as by opening this form up without FrmMain, all hunky dorey.
    Attached Files Attached Files

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

    Re: Autofill Word Document from Access (2000)

    When opening the FrmArchiveReactivate form from FrmMain, you specify a WhereCondition for DoCmd.OpenForm that restricts the form to the URN of the current record on FrmMain. You are then prompted for an URN, and most probably, you will enter another URN than the current one. Since a record cannot have two different URN's at once, nothing is displayed. You should remove the WhereCondition argument (stLinkCriteria) from DoCmd.OpenForm, so that the parameter prompt can do its work. Replace

    stDocName = "FrmArchiveReactivate"
    stLinkCriteria = "[URN]=" & "'" & Me![URN] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    by

    stDocName = "FrmArchiveReactivate"
    DoCmd.OpenForm stDocName

  13. #13
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill Word Document from Access (2000)

    Yes, that's solved it! Now to transfer the odd 600 records from the old excel workbook.
    Thats everything now, thanks for all your work and assistance yet again.

  14. #14
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill Word Document from Access (2000)

    Hi Hans,
    Just a quick bit off advice really, I've attached excel data that I need to transfer to this database. My concerns with it are that the coding to do with the CFIN number will not be compatible with the CFIN numbers that already exist on the excel workbook.
    I have split the excel workbook into 2 worksheets to coreespond with the data relating to TblMain & TblPayments. What will be the best way to transfer this data? I've tried exporting but it didn't seem to like that! I have copied and pasted the data into TblMain but now the CFIN number doesn't work automatically.
    With regards to TblPayments, will I be able to do the same as the URN in this table is taken from TblMain. I have a copy of the URN in the excel worksheet.
    Your advice would be duly appreciated.
    Attached Files Attached Files

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

    Re: Autofill Word Document from Access (2000)

    1. I don't understand what the problem with the CFIN's is.

    2. I noticed a problem with the URN. Some rows in the TblMain worksheet contain multiple URN's, for example 36KC1147302 / 1377503. The format of these multiple URN's seems to be inconsistent, making it very hard to process them automatically.

    3. The TblPayment worksheet contains the name and chambers of the advocates in one column, in a format that is not consistent with the way these data are stored in the database. For example, the worksheet contains "C MORGAN - 1 Paper" and the database contains "Morgan ,Christopher" and "1 Paper Buildings, Temple, DX 332, London". It may be possible to link these, but it will not be easy.

Page 1 of 2 12 LastLast

Posting Permissions

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