Automate Word merge document with user input

[smejicoiii]

Hi all first time here and I am trying to automate a merge document with input from the user. Debugging everything seems to be working fine until it gets time to finish and merge the document, any help would greatly be appreciated.

Code is below...

Sub testmerge()
'
' testmerge Macro
'
'
ActiveDocument.MailMerge.OpenDataSource Name:= _
"H:\worddata\Setup\database.mdb SetupFile.odc", ConfirmConversions:= _
False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=""DSN=CaseList;DBQ=\\10.0.0.10\database.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"";Initial Catalog=\\10.0.0.10\database.mdb" _
, SQLStatement:="SELECT * FROM `SetupFile`", SQLStatement1:="", SubType:= _
wdMergeSubTypeOther
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
Dim dsMain As MailMergeDataSource
Dim numRecord As Integer
Dim strReference As String
strReference = InputBox("Enter Client Reference")
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False
Set dsMain = ActiveDocument.MailMerge.DataSource
If dsMain.FindRecord(FindText:=strReference, _
Field:="File_no") = True Then
numRecord = dsMain.ActiveRecord
End If
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With dsMain
.FirstRecord = numRecord
.LastRecord = numRecord
End With
.Execute Pause:=False
End With
End Sub

I am receiving a Run Time Error 5631 "word could not merge the main document with the data source because the data records were empty"

[macropod]

That approach won't work unless you make the input box results part of the SQL statement or a condition of the .Execute statement. Question: Why are you trying to solicit the input via a macro instead of via a SKIPIF/FILLIN field combo? See, for example: http://www.vbaexpress.com/forum/showthread.php?49846-Word-macro-to-retrieve-information-from-Access-Table&p=319042&viewfull=1#post319042

[smejicoiii]

Hi and thank you for the reply. I am not married to the solution I proposed, I was just trying to use what I partially knew how to do. My users have multiple merge letters they use throughout the day and they all point to the same data source and instead of having to find record manually everytime I was trying to give them a more automated solution where they type in the case number which is field file_no and the macro merges the document for them automatically and end result would be the finished document that they can then edit if needed and save as. I am not familiar with what you recommended but will look shortly to see if I can get it to work. Any pointers or additional help is always appreciated. Thank you.

[macropod]

Cross-posted at: http://www.tek-tips.com/viewthread.cfm?qid=1742965
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

[smejicoiii]

Sorry for the cross posting macropod, my bad. Thank you for the information I just quickly tried using the SkipIf you mentioned but was not able to get it to work. I will try to look into it more tomorrow. It seems that this may be a known bug with the findrecord and vba as i found.
So by adding an additional findrecord statement that fails everything then works. but this lengthens the time for the merge to complete.

[macropod]

Did you take note of the 'Note' in the linked post - none of the braces (i.e. '{ }') can be typed.

[smejicoiii]

Yes sir I did I used the control F9. I will find additional info on this function you recommend and try to get it working tomorrow. Thank you again.