By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,550 Members | 2,725 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,550 IT Pros & Developers. It's quick & easy.

Using an array to create a merge document

P: 4
I am making a merged set of documents using bookmarks so that only the current record is sent to Word. Right now I am checking if each field is NULL individually using;
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me.TakenBy) Then
  2.     TakenBy = " "
  3. Else
  4.     TakenBy = Me.TakenBy
  5. End if
  6.  
Where the first label in this case TakenBy is the name of bookmark in Word and Me.TakenBy is the name of the control in the Access form. I have 24 different labels so this gets long and ugly.
I would like to do this using a loop and an array. In my table of the array fields the fist column is just the counter, the second column is the list of Word bookmark names and the third column is the list of the Access control names. So far I have been having no luck with this at all. The table is called tblMergeFields, with fields ID, docMerge, and dbMerge. I am pretty new to vba programming and have gone through some intro books but arrays don't seem to be well detailed.
Any help that anyone can provide would be greatly appreciated.
Jan 31 '12 #1

✓ answered by ADezii

Like NeoPa, I too am a little confused by your Request. I'll Post what I feel is an appropriate Response, which may/may not be the case.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstMerge As DAO.Recordset
  3.  
  4. Set MyDB = CurrentDb
  5. Set rstMerge = MyDB.OpenRecordset("tblMergeFields", dbOpenDynaset)
  6.  
  7. With rstMerge
  8.   Do While Not .EOF
  9.     'Field Names correspond exactly to Values in the [Field] Column of tblMergeFields
  10.     'Value of Bookmark will equal either "" or Control Value. The actual Bookmark Name
  11.     'is the Value in the [Bookmark] Field of each Record, which is Fields(0)
  12.        With Wrd.ActiveDocument.Bookmarks
  13.          .Item(.Fields(0)).Range.Text = IIf(IsNull(Me.Controls(![Field])), "", Me.Controls(![Field]))
  14.        End With
  15.         .MoveNext
  16.   Loop
  17. End With
  18.  
  19. rstMerge.Close
  20. Set rstMerge = Nothing

Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,494
What are you actually trying to do here Mark? You say you want to populate an array, yet you also seem to indicate your array is in a table (which doesn't make much sense and shows a lack of understanding).

Typically, processing through controls on a form would be done by looping through the Controls collection, but I have little idea what you want done around that.
Jan 31 '12 #2

P: 4
I am making a word document using bookmarks. The bookmark in the word document relates to a field or sometimes a couple of fields in a table. The tblMergeFields contains the names of the Word Bookmarks and the names of the fields being merged.
The loop performs two actions;
1. - test to see is the value of the control is null and if it is then set the value of the bookmark to " "
2. - if the value of the control is not null then set the value of the bookmark to whatever is stored in the control.
eg: the form is based on a table - tblClient, in the tblClient there are fields [LastName], and [FirstName]
if [LastName] is blank then I want the merge bookmark LastName to be set to a space, else I want the merge bookmark LastName = Me![LastName]
Right now I am doing this with multiple if statements. What I am trying to figure out how to do is to use the table "tblMergeFields" with 2 columns, column 1 is the name of the Word BookMarks and column 2 is the name of the controls from the form corresponding to each entry in column 1.
I was trying to use a statement something like this;
Expand|Select|Wrap|Line Numbers
  1. For i = 1 to 24
  2.    if isNull(Me![ & "tblMergeFields(2,i)" & ]) then
  3.       str(tblMergeFields(1,i)) = " "
  4.    else
  5.       str(tblMergeFields(1,i))= Me![ & str(tblMergeFields(2,i) & ]
  6.    End if
  7. Next i
  8.  
Later on in the function I use the bookmark names to set up the fields for the word document as follows;
Expand|Select|Wrap|Line Numbers
  1. With Wrd.ActiveDocument.Bookmarks
  2.    .Item("LastName").Range.Text = LastName
  3.    .Item("FirstName").Range.text = FirstName
  4. etc...
  5. End With
  6.  
I am not doing this in the loop as well because the fields are used to create several different documents.
Jan 31 '12 #3

NeoPa
Expert Mod 15k+
P: 31,494
  1. What you want can probably be done.
  2. The way you're approaching it is almost completely wrong.
  3. To know how to direct you I need a better understanding of what you need.
  4. Your explanation seems to be repeating everything you've already said, but in more detail.

I need you to look at my questions again and see if you can explain your requirements more clearly. Not your ideas of potential solutions, just your requirements.

If your second attempt goes nowhere near what we need then I'll consider trying to work out more specific questions to lead you to share what is required, but have another go first.
Feb 1 '12 #4

P: 4
I need to make a series of documents based on a single record. The documents in Word are using the Access data to populate a number of different fields in Word. However some of the Access fields may be blank so I need to be able to test if the field in Access is blank before setting the Bookmark equal to the field value. If the Access field isn't blank then I need to set the Bookmark equal to the field value.
I am currently doing this with 24 individual if...then...else statements, which is working fine but is ugly and not efficient. I am trying to replace the 24 individual if...then...else with one that uses an array that contains the names of the Bookmarks and the equivalent Access field.
Feb 1 '12 #5

NeoPa
Expert Mod 15k+
P: 31,494
  1. And is there a correlation between the fields in the record and the controls on the form?
  2. If so what is that exactly?
  3. Are there any fields in the record which are not included in this process?
  4. If so which are they?

This is basic information, without which we cannot even begin to consider how to go about helping you. I applaud your instinct for tidier code, but you must appreciate that questions here should be posted only after due consideration of what's required - not simply after some vague thought occurs to you as a good idea.

If that sounds harsh, don't worry. You'll look back later and realise how helpful it proved. If you aren't thinking critically and logically when asking a question, how will you ever manage it to design code (and other than the most basic code such thinking is absolutely necessary).
Feb 1 '12 #6

ADezii
Expert 5K+
P: 8,638
Like NeoPa, I too am a little confused by your Request. I'll Post what I feel is an appropriate Response, which may/may not be the case.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstMerge As DAO.Recordset
  3.  
  4. Set MyDB = CurrentDb
  5. Set rstMerge = MyDB.OpenRecordset("tblMergeFields", dbOpenDynaset)
  6.  
  7. With rstMerge
  8.   Do While Not .EOF
  9.     'Field Names correspond exactly to Values in the [Field] Column of tblMergeFields
  10.     'Value of Bookmark will equal either "" or Control Value. The actual Bookmark Name
  11.     'is the Value in the [Bookmark] Field of each Record, which is Fields(0)
  12.        With Wrd.ActiveDocument.Bookmarks
  13.          .Item(.Fields(0)).Range.Text = IIf(IsNull(Me.Controls(![Field])), "", Me.Controls(![Field]))
  14.        End With
  15.         .MoveNext
  16.   Loop
  17. End With
  18.  
  19. rstMerge.Close
  20. Set rstMerge = Nothing
Feb 1 '12 #7

P: 4
Thank-you very much for your answer. The code you gave won't work exactly as is because several different documents are being created at the same time and not all of them use all of the bookmarks. However the problem I was having was with the syntax and what you have posted was enough to allow me to work it out. I also can't use a While not EOF since this table contains the names of all of the BookMark Labels and corresponding Control names for all of the documents so I am using a for loop that uses allows me to choose the fields I need, eg one set of documents will use records 1-10 while another set will use 11-25.
Thanks again.
Feb 15 '12 #8

ADezii
Expert 5K+
P: 8,638
I am using a for loop that uses allows me to choose the fields I need, eg one set of documents will use records 1-10 while another set will use 11-25.
This can actually be incorporated into the overall Logic also, as in:
Expand|Select|Wrap|Line Numbers
  1. Dim strDocumentName As String
  2. Dim intCtr As Integer
  3. Dim intLowRec As Integer
  4. Dim intHighRec As Integer
  5.  
  6. strDocumentName = "Document 3"
  7.  
  8. Select Case strDocumentName
  9.   Case "Document 1"
  10.     intLowRec = 1
  11.     intHighRec = 10
  12.   Case "Document 2"
  13.     intLowRec = 11
  14.     intHighRec = 25
  15.   Case "Document 3"
  16.     intLowRec = 26
  17.     intHighRec = 45
  18. End Select
  19.  
  20. 'For 'Document 3'
  21. For intCtr = intLowRec To intHighRec
  22.   Debug.Print "Record#: " & CStr(intCtr)
  23. Next
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Record#: 26
  2. Record#: 27
  3. Record#: 28
  4. Record#: 29
  5. Record#: 30
  6. Record#: 31
  7. Record#: 32
  8. Record#: 33
  9. Record#: 34
  10. Record#: 35
  11. Record#: 36
  12. Record#: 37
  13. Record#: 38
  14. Record#: 39
  15. Record#: 40
  16. Record#: 41
  17. Record#: 42
  18. Record#: 43
  19. Record#: 44
  20. Record#: 45
Feb 15 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.