Using Mail Merge - question on VBA

[RCheesley]

Hi all,

Just wanted to check something, I'm planning to use the mail merge solution posted here but need to run the merge ONLY for the particular record active in the form from which the button is pressed.

The VBA code on the article above incorporates an SQL statements:

SQLStatement:="SELECT * FROM [tblcompanies]"

The form is running from a table called tbl_sampleinfo with the "create letter" button sitting on a form called frm_sampleinfo.

I want the mail merge to use a query called qry_samples (as there are fields from other tables I need to incorporate and thought this would be the best way of accomplishing it). I have set up the mail merge template using the fields from this query and formatted it accordingly.

I assume I need to use a statement which says (in laymans terms) "Select records from qry_samples for NHSNo stated on frm_sampleinfo" but I am not sure on the syntax? I am taking a big guess here but would the following be anywhere near correct?

"SELECT * FROM [qry_samples] WHERE NHSNo=[frm_sampleinfo]![NHSNo]"

I tried this and get errors about onload/onclick saying "user-defined type not defined" :|

Further info on error above - VBA code as follows:

Option Compare Database

Dim WithEvents oApp As Word.Application

Private Sub But_Conslet_Click()

    Dim oMainDoc As Word.Document
    Dim oSel As Word.Selection
    Dim sDBPath As String

    Set oMainDoc = oApp.Documents.Open("<my UNC Path>\Test Letter")
oApp.Visible = True

    With oMainDoc.MailMerge

        .MainDocumentType = wdFormLetters

        sDBPath = "<my UNC Path>\<databasename>.mdb"
        .OpenDataSource Name:=sDBPath, _
           SQLStatement:="SELECT * FROM [qry_samples] WHERE NHSNo=[frm_sampleinfo]![NHSNo]"

    End With

    With oMainDoc
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.Execute
    End With

    oApp.Activate
    oApp.Documents.Parent.Visible = True
    oApp.Application.WindowState = 1
    oApp.ActiveWindow.WindowState = 1

End Sub

Private Sub Form_Load()

Set oApp = CreateObject("Word.Application")

End Sub

Private Sub Form_Unload(Cancel As Integer)

Set oApp = Nothing

End Sub

 

[DCrake]

Nealry there....

Split up

"SELECT * FROM [qry_samples] WHERE NHSNo=[frm_sampleinfo]![NHSNo]"

to read

"SELECT * FROM [qry_samples] WHERE NHSNo=" & [frm_sampleinfo]![NHSNo]

If the nhs no is being treated as a number

or

"SELECT * FROM [qry_samples] WHERE NHSNo='" & [frm_sampleinfo]![NHSNo] & "'"

if being treated as a string


David

 

[RCheesley]

Thanks David,

If I wanted to add on an additional "AND" clause, i.e. I want to say "and the sample date is less than or equal to the discharge date +62 days" would I just bolt this on to the end? as in,

AND SampleDate<=(PrevDiscDate+62)

or would this not play nicely? This is also set on the form where the button sits.

Or I thought perhaps to grey out the button using If/visible/Enabled statements?

Basically this is going to mailmerge a letter to a consultant if a sample is positive up to 2 months after discharge - I don't want them to have the option to do the mailmerge if it's been longer than 2mths - whether this is by greying out or having an error message pop up I don't mind.

Ruth

 

[RCheesley]

Partially fixed ... I had a missing reference in VB Editor..now getting problems with accessing the DB - says it's locked .. funny that, seeing as I ran the request from the database :|

Also now getting an error that it can't find the field "|" (it looks like a pipe, could be an L) referred to in your expression - on debug the following lines come up

.OpenDataSource Name:=sDBPath, _
           SQLStatement:="SELECT * FROM [qry_samples] WHERE NHSNo=" & [frm_sampleinfo]![NHSNo]

The plot thickens!

It seems to be the NHSNo bit which is causing the error, as when I take this out the merge continues (but complains about it being locked as above)

 

[RCheesley]

Is anyone able to point out why I might be getting the error above? Would really appreciate some advice! I've got as far as I can go with this one and can't see the wood for the trees

Ruth

 

[gemma-the-husky]

might just be you need to put

WHERE NHSNo = "

with extra spaces - SQL is realy unforgiving of syntax errors

try this code fragment to make sure the sqlstatement is valid

on error goto trap
docmd.openquery sqlstatement

exithere:
exit sub

trap:
call msgbox("Error with SQL " & vbcrlf & _
"error: " & err & "  Desc: " & err.description)
resume exithere

 

[RCheesley]

Hi Gemma,

I might be missing something but where do I put this code? I tried using the spaces as you suggest, but still no joy.

Ruth

 

[jal]

When you can't fix a file lock error, try rebooting because it could be a bit of a fluke.

As for where to put that code, should be okay to put the code anywhere, maybe like this:

Dim SQL as string
SQL = :="SELECT * FROM [qry_samples] WHERE NHSNo=" & [frm_sampleinfo]![NHSNo]
Docmd.openquery SQL


Also do a Msgbox SQL (you can use Control-C to copy it to the clipboard) as to paste here your SQL so maybe we can take a look at it.

You might also be able to paste it into SqlView to see if it runs okay from there.

 

[gemma-the-husky]

my code?

i wasnt sure what you were doing with your SQL
assuming you were doing a runsql statement, then you can trap that statment with my ocde or similar

thinking about it now, i was trying ot get the query to open but I dont think

a) docmd.openquery sqlstatment works

and I am not sure

b) docmd.runsql sqlstatement will actually show you the selection

it was all just really to verify the syntax of the sql statement

 

[jal]

I downloaded the sample code and, in my case, it seems to work fine the first time and then, if I click the button again, the file C:\Test Letter.Doc is locked. I have to close the MDB file and reopen in order to get it to work. I can think of a possible workaround, by adding a good bit more code, but it would be nice to find a simpler solution.

 

[jal]

Didn't find a solution. The workaround would be to leave the document intact. Just make a copy of it every time you run mail merge.

Copy1.Doc
Copy2.Doc
Copy3.Doc'

And at startup try to delete any of the old copies that are sitting out there. The point is that you if you always load a fresh copy, it won't be a locked edition (that's my theory anyway).

 

[RCheesley]

Hi all,

Perhaps I need to clarify for people as things seem to have got lost in translation somewhere!

The following is not working:

Private Sub But_ConsLet_Click()

    
    Dim oMainDoc As Word.Document
    Dim oSel As Word.Selection
    Dim sDBPath As String

    Set oMainDoc = oApp.Documents.Open("<myuncpath>\LetterTemplate")
oApp.Visible = True

    With oMainDoc.MailMerge

        .MainDocumentType = wdFormLetters

        sDBPath = "<myuncpath>\Tracking Database.mdb"
        .OpenDataSource Name:=sDBPath, _
          [B] sqlstatement:="SELECT * FROM [qry_samples] WHERE NHSNo = " & [frm_sampleinfo]![NHSNo][/B]

    End With

    With oMainDoc
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.Execute
    End With

    oApp.Activate
    oApp.Documents.Parent.Visible = True
    oApp.Application.WindowState = 1
    oApp.ActiveWindow.WindowState = 1

End Sub

The line which is in bold is where the problem is arising - if I take out the WHERE statement then it runs fine - I guess therefore there's something wrong with this statement?

 

[jal]

Again, you need to verify that the SQL Statement is of proper syntax. You can do this:

Dim SQL as string
SQL = "SELECT * FROM [qry_samples] WHERE NHSNo=" & [frm_sampleinfo]![NHSNo]
Msgbox(SQL)

and then use control C to copy and paste it fromt the Msgbox into Wordpad. From there you can paste here for us to see - but even better yet, paste it into SQL view and run it to make sure it is returning the desired results.

 

[RCheesley]

Thanks for the advice, please appreciate that you might actually be telling me to do something which I do not understand.

I tried doing this when you first posted but gave up as I was getting even more errors than I had to start with and couldn't understand your seemingly idiot proof instructions.

Where, for example, do I enter this within my code?

I am not stupid but this is fairly advanced for my level of knowledge in this area, having only just started even looking at VBA let alone using it (and have not even really touched sql), and your instructions are not clear enough to allow me to follow them.

Wherever I insert this I get syntax errors, so evidently I am not putting it in the right place.

 

[RCheesley]

Can anyone clarify this for me? Ruth

 

[GaryPanic]

I might be mudding up the water here

but

sqlstatement:="SELECT * FROM [qry_samples] WHERE NHSNo = " & [frm_sampleinfo]![NHSNo]

should the qry itself be looking at the form (frm_sampleinfo) the qry has already done the filtering ???
also is the field NHSNo actual on the form sampleinfo (Thought i would ask the obilous question) as your button is sitting on this form and you are refering to this form ensure that the field is also on the form

if i am wrong - feel free to tell me to shut up ...