Hi Paul,
I'm not certain I'm with you on your first comment. I do have 'option explicit' in the VBA module, and it it didn't catch any error. StrSrc is also in the macro ... StrSrc=ThisWorkbook.Fullname . In the debug process, StrSrc = "U:\Sports13\Sports13B.xlsm" , which is correct. This prompt for a table only started appearing when I added the code in red to the SQL statement. If removed, it works, there is just no filtering of records.
It is a different document ... it's the "DR Version" of the document I sent you, which was for "FR" reports. They use the same datasource, and are formatted identically. The only difference is in the fields they display.
If we look at my SQL line in the VBA:
Code:
Name:=StrSrc, ReadOnly:=True, AddToRecentFiles:=False, LinkToSource:=False, _
Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
"Data Source=StrSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
SQLStatement:="SELECT * FROM `CONTROL_1$` where *'Type$'*='DR'", _
SQLStatement1:="", SubType:=wdMergeSubTypeAccess
... if I remove the code in red, the merge will work ... no prompt.
I really need to stay away from input boxes Paul ... the most simple thing I can do for the user would be to include the "filter" (Type = DR and SubResp = RPL1) in the SQL ... much like you would if you were defining the recipients of the mail merge in Word.
The way I used to do this, was to make an individual main merge document for each of 36 different reports available. These individual reports were saved with the SQL parameters, so that when the user loaded that document in Word, only the relevant records would be displayed. That option worked well, but required the less than tech savy user to have to leave Excel, open word, find the report and print it.
What I want to do withthe Excel VBA, and we're almost there ... is for the SQL to provide the flexibility of automatically loading the proper report based on the "type" and "subrep" variables in the coded SQL statement.
eg. without success, my current code:
Code:
SQLStatement:="SELECT * FROM `CONTROL_1$` where *'Type$'*='DR' and *'SubResp'*='RPL1'"
would access Word and apply the sql to select records of type DR and subresp RPL1
Ideally, the code would look something like this quasi-coded example ...
Code:
SQLStatement:="SELECT * FROM `CONTROL_1$` where *'Type$'*='variable1' and *'SubResp'*='variable2'"
where variable1 would be DR, DT, FR, FT, CR or CT and variable2 would be CUE1, CUL1, WPE1, WPL1, RPE1, RPL1, HPE1 or HPL1 ... which make for a possible 36 possible SQL combinations.
Is this possible? Right now, I can't say for certian because of the dialogue box that is hanging things up when it reaches the SQL statement part of our code.
Thanks for the advice on the workaround for using your macro. I didn't realize you could copy and paste the code fields within Word ... I didn't think you could paste them at all. Will definitely try.
Sorry for being a pain.
Jenn