Results 1 to 15 of 15
  1. #1
    myusersname is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    17

    Mail Merge only merging the Primary keys

    Greetings Access Wizards,



    I thought I'd be clever and setup an Access Database for some of my less Excel savvy co-workers to create there own Table that could be used for mail merge letters in Word. I've never fooled with Access much but I figured it couldn't be much different that Excel. I figured incorrectly. Access is a beast to understand.

    Let me be clear, I know little to nothing about VBA or Table Relationships. However I managed to come up with a "Order Form" that does complete an "Order Table" that displays everything I need for the mail merge. However, when I use that table for Word's Mail Merge, some of the data displays the Primary Key rather than the pulled data from the other tables (Names, Addresses, Phone numbers). If I export the table to Excel it works fine and all the proper data is displayed in the fields. But I'd rather be able to get the mail merge from the Orders table.

    I did use code builder to auto-populate the Form with the information from the customer table (names, addresses, phone) based off the client number from a combo box. The Client ID combo box had all the other fields as well but I shortened the width so they were un-noticable from the drop down. The other fields are also in the form as combo boxes with only the field required (First name for example). So when I type the Customer Number the rest is auto-filled and if changes are needed to those fields on the order form a button to open the customer form is used.

    Since my finding the mailmerge problem, I've read some folks say that the Mail merge doesn't work well with Word, which would be quite devastating considering the 20 hours I've lost on this thus far. With my little understanding of the table relationships, I'd hate to try to go back through this thing and try to untangle the webs, so I'm hoping that isn't the issue. I've tried to create a Query but I'm lost there as well.

    Any suggestions on a simple way to get mail merge working that won't take extensive knowledge??

    Thank you for your time in looking at this.

    This is my code if you think it's the issue.



    Code:
    Private Sub CLMTID_Change()
    
    Me.CLMTFN.Value = CLMTID.Column(0)
    Me.CLMTLN.Value = CLMTID.Column(0)
    Me.Blank.Value = CLMTID.Column(1)
    Me.CLMTADD2.Value = CLMTID.Column(0)
    Me.CLMTCITY.Value = CLMTID.Column(0)
    Me.CLMTSTATE.Value = CLMTID.Column(0)
    Me.CLMTZIP.Value = CLMTID.Column(0)
    Me.CLMTPHONE.Value = CLMTID.Column(0)
    Me.CLMTEMAIL.Value = CLMTID.Column(0)
    Me.CLMTLODGE.Value = CLMTID.Column(0)

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I expect you need to change the column index for each line.

    Why use mail merge instead of Access report?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    myusersname is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    17
    I attempted that, but it left the other fields unpopulated when I changed it to Column (1) or 2 or 3. The reports may work for some stuff, but some letters will need to be manually tweaked or edited before they are sent.
    Last edited by myusersname; 02-18-2019 at 10:29 PM. Reason: Typos

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Well, if the fields are in the combobox RowSource and all records have data, referencing combobox column index should provide data. What is ColumnCount property setting?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    myusersname is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    17
    Master Claims Empty.zip

    I think that's attached correctly.

    Don't rip on my hack job database too much. While deleting the personal information I found that I have my relationships wrong, I can't delete/edit members if they have a claim unless I'm in the form that puts the claims in.

    The ClaimListTBL is the table I want to use for mail merge.

    Thanks for looking.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Your problem has to do with the use of lookup fields in the table. The lookup field should be replaced with combo-boxes in the interface alone(=forms) as they mask the values stored in the table. If you open your table in design view and replace the lookup from combo to textbox you will see that the table actually stored the member ID (PK) because that is what you feed it in the form.

    Also if your claim table has the member ID field (CLMTID) you don't need to duplicate their information (names, phone #,emails, etc.) in there; you simply join the member table (CSXMemberTBL) to the claims table in a query and use that for mail-merge.

    You are using multi-values fields instead of regular join tables what will make extracting the data from them much harder. Same with the attachment type field.

    I would say you need to take a second look at your db and redesign it properly before rushing to implement your mail-merge. Access is definitively a much better tool than Excel for your needs, but it has to be dome correctly.

    While over the years there were some issues with automating Word mail-merge from Access I find it usually a much better and easier solution than using bookmarks.

    Cheers,
    Vlad

  7. #7
    myusersname is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    17
    Your problem has to do with the use of lookup fields in the table
    I had to reference the Name, Addresses, from the membersTBL in my ClaimListTBL otherwise my ClaimForm would not auto-populated the once the CLMTID was entered. Is that the problem you are referring to or rather the use of code to auto-populate based on the CMNTID (Column(0))??

    I know that the form is putting really placing the CLMTID in all the fields, because I created a search text box to search for any field in the Claim Form but it will only find the ClaimNumber and CLMTID.

    The lookup field should be replaced with combo-boxes in the interface alone(=forms)
    Currently I have the CMNTID CLMTFN CLMTADD1 (ID Number, First Name, Address 1) fields in the ClaimListTBL as Combo Boxes that look to the MemberTBL for the drop down list. Can you explain more of how I should have it either in the ClaimListTBL or in the Claim Form?

    Also if your claim table has the member ID field (CLMTID) you don't need to duplicate their information (names, phone #,emails, etc.) in there; you simply join the member table (CSXMemberTBL) to the claims table in a query and use that for mail-merge.
    AHHHH, see I was thinking that I needed the Claim Form to enter all of this data into one Table to use that for the mail merge. In reality I should use the form to SHOW the name/address/ect FROM the memberTBL that is associated with the CMNTID typed into the form but only store the CMNTID on the ClaimListTBL and run a query to populate a "temporary Table" to use for the mail merge. Is that right??


    I would say you need to take a second look at your db and redesign it properly before rushing to implement your mail-merge. Access is definitively a much better tool than Excel for your needs, but it has to be dome correctly.
    Yeah I didn't think it would be this complicated. I don't fully understand the relationships to see clearly how to set them up, much less the rest of the stuff. It's it nice to tab around the forms to input the data though. Excel is much easier to setup the sheet and do a quick mail merge, but long term and user interface goes to Access.


    Thanks again everyone for the help thus far.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The source of problem is creating lookups in table. Most experienced developers will not do this. Instead just build comboboxes on form. You already have comboboxes on form. So now modify table to remove the lookups - change to textbox in the field properties.

    Your mail merge will have to use a query that joins these related tables so the associated information is available. Not sure you need 'temporary table'. Can't mail merge use a query?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    myusersname is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    17
    When I change the fields in the ClaimListTBL from ComboBox to Text box in the Lookup tab, I loose the ability to auto-populate the fields once the CLMTID is entered. I'm thinking that I should change the Claim Form to show the Member Form to view/edit the member's info once the ID is put in like a search record text box. As stated above, I should not worry about placing all that info (Names, Addresses) into the ClaimListTBL but rather just place the CLMTID in that table then query the two.

    My other option would be to leave as is and put a button on there to export the ClaimListTBL into an Excel sheet and have it overwrite the old one every time, but I don't know if that is a possibility.

  10. #10
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    PMFJI, Have you looked into Albert Kallal's Super Easy Word Merge?

    http://hitechcoach.com/downloads-mai...asy-word-merge

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What do you mean you 'lose the ability to auto-populate the fields'? These fields are saving ID values from comboboxes on form, not descriptive info. If you need to view the descriptive info then use a query that joins the tables.

    Every one of those text fields with combobox in ClaimListTBL is saving the same data - EmployeeID. This is useless for your mail merge. ClaimListTBL should have only 1 field with EmployeeID value. This establishes relationship to CSXMemberTBL.

    Then there should be only 1 combobox on ClaimForm to select employee and save EmployeeID. Textboxes can display the related info but should not be saving into ClaimListTBL.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have a look at the attached file to see what we are talking about. I have updated your table and form and created a query for mail merge.

    You still have a bit of cleanup to do:

    -attachment fields should be avoided as they tend to bloat the file , you should replace the attachment field with a table where you store the AttachmentID (OK, autonumber),ClaimID (ForeignKey),AttachmentName(text, full path to file),AttachmentDescription(text)

    -you should replace the ID (autonumbers) PKs with more meaningful names (ClaimID, DistrictID, etc.).

    -multivalue fields usually are a pain to work with and should be replaced with tables (similar to the attachment one)

    -you have to rethink your district/division/subdivision/seniority structure. Looks like a lot of repetition which could lead to data inconsistencies. If you build a proper hierarchical table structure all you need to store in the claims table is the smallest element (in your case SubdivisionID); if you know that you can get the division that subdivision belongs to and the district that that division belongs to and...., don't need to store all of them in the claims table.

    Hopefully this would help you get a better picture on how it would work. Post your progress back and we would be happy to help.

    Cheers,
    Vlad
    Attached Files Attached Files

  13. #13
    myusersname is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    17
    I see now!! My ClaimForm should be Showing the fields from the MemberTBL that match the CLMTID, and I need to delete the those field that are the same in me ClaimListTBL, because all of that date can be "merged" in a query, then run the Mail merge from that.

    I know you guys have told me like 4 time but I finally understand what you're talking about.

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Glad to help!

    Cheers,
    Vlad

  15. #15
    myusersname is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    17
    Yes,

    Thank you all so much!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Use of primary keys
    By Homegrownandy in forum Access
    Replies: 6
    Last Post: 06-29-2015, 01:17 AM
  2. Primary Keys & Relationships
    By Njliven in forum Programming
    Replies: 4
    Last Post: 12-17-2012, 09:42 AM
  3. How to create two primary keys
    By Shabana123 in forum Database Design
    Replies: 1
    Last Post: 09-08-2012, 05:55 AM
  4. Replies: 1
    Last Post: 07-27-2010, 07:06 AM
  5. Primary and Secondary Keys...
    By LittleOleMeDesigns in forum Database Design
    Replies: 5
    Last Post: 07-24-2009, 11:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums