Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging from Access with criteria inputting
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? |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging from Access with criteria inputting
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 James wrote: 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? |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging from Access with criteria inputting
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 Peter Jamieson wrote: 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 James wrote: 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? |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging from Access with criteria inputting
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 -- cram "Peter Jamieson" wrote: 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 Peter Jamieson wrote: 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 James wrote: 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? |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inputting Multiple Drop Down Items | Microsoft Word Help | |||
Word with Access changing criteria | Mailmerge | |||
merge word with an access query with a like criteria | Mailmerge | |||
Pass Access criteria direct to a Word document | Mailmerge | |||
merging from access | Mailmerge |