Merging from Access with criteria inputting

[James]

Hi all
I have an Access 2003 database with names and addresses plus employee
numbers in it and want to merge this with a word document. I do not want to
merge all the records, I want to chose which employees to merge each time I
open the document by entering their employee numbers. The Access query works
fine, asking for the criteria when opened in Access, but Word wont merge with
it - it cannot open the query.
I appreciate that I may have to do this the other way around and start the
process from Access, but would prefer to open the word document and be asked
which employee numbers I want to input to merge.
Any ideas?

 

[Peter Jamieson]

One approach is to change the way Word connects to the database - check
Word Tools->Options->general->"Confirm conversions at open", then go
through the connection process again. You should see an additional
dialog box which presents a DDE option. Choose that, and you should be
able to see the Parameter Query you are using.

The main drawbacks of DDE (other than the fact that it's supposed to be
obsolescent) are that
a. it has to open Access to do the work
b. if you happen to have non-ANSI Unicode characters in your data they
will be lost and replaced by "?"
c. it can be slower

Another approach is to use Word VBA to prompt the user, construct the
appropriate Access query, open the connection and (optionally) perform
the merge. But let's not go there now...

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Peter Jamieson]

FWIW that will only do one employee at a time. I'm not sure you can
easily do an abritrary number of employees at one time using a parameter
query, but the following piece of code shows how you can get a list of
IDs (in this case numeric) and make the appropriate database connection.
It's based on the Northwind sample database.

This still assumes that you know the IDs of the employees you want - to
do more, you would need to create a userform that lets you select from a
list.

Sub connecttospecificrecords()
' prompts for a list of employee ids
' creates a suitable query
' connects to the data
Dim bAskAgain As Boolean
Dim strList As String
Dim strSQL
Do
strList = InputBox("Type the Employee IDs, separated by commas",
"Employee IDs")
bAskAgain = False
If strList = "" Then
MsgBox "You either cancelled, or did not enter anything."
Else
' you can do as much checking of IDs as you want here
' you really should validate the string to avoid an
' "SQL Injection Attack"
' However , I 'm just going to check that the string
' contains nothing except digits And commas
i = 0
Do While i < Len(strList) And Not bAskAgain
i = i + 1
Select Case Mid(strList, i, 1)
Case "0" To "9", ","
' do nothing
Case Else
bAskAgain = True
MsgBox "The list may only the digits 0-9 and "","" characters"
End Select
Loop
If Not bAskAgain Then
strSQL = _
" SELECT * FROM Employees" & _
" WHERE EmployeeID IN (" & _
strList & _
")"
' alter the pathname of the database
' as necessary for your system
ActiveDocument.MailMerge.OpenDataSource _
Name:="z:\vmshare\Northwind.mdb", _
sqlstatement:=strSQL
End If
End If
Loop Until Not bAskAgain
End Sub



Peter Jamieson

http://tips.pjmsn.me.uk

 

[cram]

James,
the way I did this.
I added a yes/no field to the employee(record) say "SEL". Default set to NO
I open a form with only the name and some main info to identify the employee
and of course the SEL field. I check the SEL field for the selected
employees. At that moment you wil have a recordset with only selected
employees to make your merge.
To reset all the yes/no SEL field after the merge, I included an
update-macro to reset all SEL values to the default NO value.
Marc