[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

Word mail merge information from Access to postcards

I have customer information in an Access database.    I have created a mail merge in Word to print postcards, front and back, 4 postcards on one sheet of paper.   I can get the infomation to print on one side (which will be the back of the postcard), and want to print their address on the correct other (front) side.    I cannot get the correct address on the front to match up with the correct data on the back.   I also would ideally like this to all print on a duplex printer without having to set up two different mail merges.     I would love any help you've got!          - Thank you!
0
jmillerCTG
Asked:
jmillerCTG
  • 4
  • 3
1 Solution
 
GrahamSkanRetiredCommented:
To do it in two passes, you would need a second re-ordered copy of the data. In terms of the 'front' data, the 'back' data would have to go 2,1,4,3,6,5...

You could combine the two sets into one table that would go:

1,2,3,4,2,1,4,3,5,6,7,8,6,5,8,7.

and use this for duplex printing.
0
 
jmillerCTGAuthor Commented:
GrahamSkan,  first I am sorry for this long delay in my response.     I am back to work now and need to get back on this project.      Do you mean to put a new column or key into the Access data to have it sort in that manner?     Thanks.
0
 
GrahamSkanRetiredCommented:
You would need to have two records per card. You could create a table to do this.

The fields for the front of the of the card are represented in the table by the single field FrontData. The back example follows the same system, though there is no reason why some fields should not be shown on both sides.

I think that you would need some the VBA to create it - I can't think of a way of doing it in a query.  

The tables (original and new) are illustrated in the snippet.



CardID FrontData BackData
1	F1	B1
2	F2	B2
3	F3	B3
4	F4	B4
5	F5	B5
6	F6	B6
7	F7	B7
8	F8	B8
		
 
RecNo	CardID	FrontData BackData
1	1	F1	B1
2	2	F2	B2
3	3	F3	B3
4	4	F4	B4
5	2	F2	B2
6	1	F1	B1
7	4	F4	B4
8	3	F3	B3
9	5	F5	B5
10	6	F6	B6
11	7	F7	B7
12	8	F8	B8
13	6	F6	B6
14	5	F5	B5
			

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
GrahamSkanRetiredCommented:
The code would look like this. It is untested.
Sub DoubleCards()
    Dim rs As New ADODB.Recordset
    Dim rs2 As New ADODB.Recordset
    Dim conn As ADODB.Connection
    Dim s As Integer
    Dim i As Integer
    Dim strSteps() As String
    
    Const Steps = "1,1,1,-2-1,3,-1,2" 
    
    strSteps = Split(Steps, ",")
    Set conn = CurrentProject.Connection 'In Access
    rs.Open "Select * from CardData Order by CardData.CardID", conn, adOpenDynamic, adLockPessimistic, adCmdTable
    Do Until rs.EOF
        For s = 1 To 8
            rs2.AddNew
            If strSteps(s) < 0 Then
                For i = 1 To -strSteps(s)
                    rs.MovePrevious
                Next i
            Else
                For i = 1 To strSteps(s)
                    rs.MoveNext
                Next i
            End If
            For i = 0 To rs.Fields.Count - 1
                rs2.Fields(i + 1).Value = rs.Fields(i).Value
            Next i
        Next s
    Loop
End Sub

Open in new window

0
 
jmillerCTGAuthor Commented:
I am not familiar with anything about Visual Basic except using it to do some automating in Excel, so I am really not sure what to do next.   Thanks.
0
 
GrahamSkanRetiredCommented:
I suggest that you create a button on a form and use the event builder to open the code for the click event. Copy and paste the code into the event procedure.
Edit the table and field names to match your own. Note that you will need to create a second table with an extra, AutoIncement, field in the first position. This will be used to order by when doing the merge.
0
 
jmillerCTGAuthor Commented:
GrahamSkan, thank you so much for all of your help.  I am sure that this is the way to do it, but it definitely beyond my skill level.     I may revisit this in the future, but will close this open question for not.   Thanks again.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now