I hope I can explain what I need so you can understand and assist me. I am not sure if this should be in mailmerge or vba since I am using a combination of vba, mailmerge and userforms, I posted here. So please forgive me if this is posted in the incorrect thread.



I have a mailmerge document with a userform, and I am using vba script to connect this document to my database so I can pull data from the database and place it into my document using bookmark. This information would be different for each customer that I run the script on. The script inserts the data just like I want it but big problem is it keeps the same information and not change like it should for each customer. I am not sure how to use the mergefield in the script that changes for each customer. Below is the script I am using.

I believe this may be the line in my script that has the issue:
Cmd1.Parameters(1).Value = "0947000"
"000000" identifies the customer number but I think it should be the mergefield name here??
not sure how to place mergefield here.

For starters it looks like the connection string is not right as the DBName appears to run into the UID without a separator.
Let Connect = "driver={sql server};server=SERVERNAME;Database=DBNAME;UID=USRI D;PWD=PASSWOOD;"

Have you actually stepped through the code to see what values are being returned as you go. We don't know anything about the Stored Procedure so have no idea on what the Cmd1 command is doing.

I would note that it appears you are creating a loop to replace the text in a bookmark. The way it is written I would expect the bookmark to disappear on the first write but if it didn't then your code would continue writing there without pause so the resulting code if run to completion would just show the last entry.

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

Let Connect = "driver={sql server};server=SERVERNAME;Database=DBNAME;UID=USRI D;PWD=PASSWOOD;"

This is how the above reads.
This is a script I met in place and wanted to tweak it.

I am trying to connect to a database and pull data from the field (usp_WordMerge_SelAddlinesByFileNumber) in the database.

Cmd1.Parameters(1).Value = "0947000" is suppose to represent the customer number but I am not sure how to make that happen. The field name is actually {CustomerID}

I am creating a loop to search all customerIDs and insert text in a bookmark until the customerID has no value or data.

I hope that make sense

We are not able to do much to help you as we don't have access to your database, nor to the procedure named "usp_WordMerge_SelAddlinesByFileNumber" which I assume is in the database.

I've used a SQL string to create the filtered recordset and then traversed that subset of data with the processing. This page shows the basic method
Using Excel VBA to Query a SQL Server Database | PTR

You will need to know the table (or query) name where the CustomerID field exists
Dim lngID as Long
lngID = 0947000 'assuming it is stored as a number
Set rs = conn.Execute("SELECT * FROM TableName WHERE CustomerID = " & lngID)

If your recordset is already filtered to a unique CustomerID then there won't be a need to test the values of that field since you already know what it is.

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

Based on your description, you're not performing a mailmerge - at least not one that would be recognised as such by MS Word, for example. Mailmerges populate mergefields, not bookmarks, even though they can be automated with VBA.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]