Connecting command buttons to particular records 1

[domino3]

I'm making very heavy weather of trying to open a query by selecting the name I want from a form with a combo box on it. I've done this on a different form in the past, but now can't work out how I managed to get it working then.

When I have my form "F_Initial_Contact" open, I select the name I want from the combo box "Combo_new" and this then brings up the details for that record. I have now added a button to the form, to open a query for that record, but when I click on the button the query opens but brings up no record at all.

The query I am bringing up is:

SELECT T_Names.ID_No, T_Names.surname, T_Names.firstname
FROM T_Names
WHERE (((T_Names.ID_No)=[Forms]![F_Initial_Contact]![Combo_new]));

what I would like it to do is, for example, when I select Mr Smith on the combo box, then the record on the screen would be for Mr Smith, and clicking on the button would open the query for Mr Smith.

Any help would be gratefully received.

 

[domino3]

This is driving me mad. I've tried connecting it to a new txt document, and the fields now stay in, but the mailmerge letter now doesn't hold the details of the mailmerge source document. I've treid selectin ght esource document several times, and every time I retry the mailmerge afresh, the link has gone.

 

[Remou]

Are you attaching the textfile with the fields and data to a word document as the datasource for a mailmerge?

 

[domino3]

That's what I am trying to do, but with no success.

The macro code I am using is

Private Sub Command1029_Click()
On Error GoTo Command1029_Click_Err

DoCmd.TransferText acExportDelim, "", "ic_form_mailmerge_button", "\\company\mymerge.txt", False, "", 50000
FollowHyperlink "\\company\contact\Initial Contact\mailmerge from database.doc"
Command1029_Click_Exit:
Exit Sub
Command1029_Click_Err:
MsgBox Error$
Resume Command1029_Click_Exit
End Sub

When I try clicking on the button, I get a dialog box telling me that the mailmerge is trying to link to a file called mymerge#txt but I don't have a file with a # before the txt. ANd when I try to reopen the mailmerge file itself, it has lost the link. I am at a loss as to how to proceed.

 

[Remou]

You need field names:

[tt]DoCmd.TransferText acExportDelim, "", "ic_form_mailmerge_button", "\\company\mymerge.txt", True[/tt]

 

[domino3]

That now works, but it has gone back to missing a few of the fields off the mailmerge.

 

[Remou]

Check that the table or query that you are exporting has all the fields that you need.

 

[domino3]

I had forgotten to export the query in the frontend database into the backend database when I changed the query. It now works. Thank you so much for your help.

 

[domino3]

I'm now trying to fiddle further with the code I have, to run the mailmerge straightaway, without the user having to press the mailmerge icon in word. The code I have tried adding after the followhyperlink line is

DoCmd.DoMenuItem acFormBar, actoolsMenu, acmerge, 3, acMenuVer70

What I get is a dialog box telling me that "the command or acion New Database is not available now".

Can anyone help me by telling me what that means, how to fix it and/or if it is possible to run the mailmerge by using a docmd.domenuitem and, if so, how?

 

[Remou]

You have now got a great many of the parts of PSeale's idea working for you. If you go back and look at the FAQ, you will find that it shows how to run the mailmerge. You need to use automation to run Word commands from Access.

 

[domino3]

I looked at PSeale's ideas, but was struggling with declaring the word oject. I didn't think most of the code was relevant (at the moment, but I may try bits later as, or if, I get more confident, but the code I did try was:

Private Sub Command67_Click()
On Error GoTo Err_Command67_Click
Set objWordDoc = GetObject(GetStartDirectory() & MergeDocumentFilename, "C:\Documents and Settings\FR\My Documents\mailmerge.doc")

objWordDoc.Application.Visible = True

DoCmd.TransferText acExportDelim, "", "q_mailmerge_letter", "C:\Documents and Settings\FR\My Documents\mymerge.txt", True, "", 50000
FollowHyperlink objworddoc
objWordDoc.MailMerge.Execute
Exit_Command67_Click:
Exit Sub

Err_Command67_Click:
MsgBox Err.Description
Resume Exit_Command67_Click
End Sub

The error dialog box I got was "the item was not found in this collection". So I decided to try to find a way round the problem instead. But I am grateful for any help you can give to get PSeales idea to work for me. Thank you.

 

[domino3]

I've gone back to looking at PSeale's module, as it probably would be what I am looking for if I could get it to work for me.

However, after reading it through several times, I still don't understand how you use it. Do you attach the whole code to the button on the form that you use to select the record required? Or does it sit in the background and get called up some other way? Also, when I tried copying the code into a module, the code gets divided into several different units. Is that meant to happen, or am I doing something wrong?

 

[domino3]

In PSeale's module, am I right in thinking that the example query he gives, copied below:

Private Sub cmdPrintInvoice_Click()
RunMailmerge "SELECT * FROM qryInvoicesFormatted " & _
"WHERE CustID = " & txtCustID.Value & _
" AND SaleDate = #" & _
Format(txtSaleDate.Value, "mm/dd/yyyy") & _
"#", "exInvoice", "invoice.doc"
End Sub

is the code that gets attached to the click button on the form where you have already selected the record you want from a combo box. And then that calls up the module called run mailmerge. If not, I've wasted all day and still not cracked it.

If the answer is yes, I am assuming that I put my query in where qryInvoicesFormatted is at the moment, and my mailemerge document where "invoice.doc" is. That leaves me the text file to put in, I suppose where "exInvoice" is.

However, my query already picks out the required record and I don't have a sales record field, so I tried doing the above having taken out & _
"WHERE CustID = " & txtCustID.Value & _
" AND SaleDate = #" & _
Format(txtSaleDate.Value, "mm/dd/yyyy") & _
"#"

and that didn't work. Can anyone tell me if I have taken out too much, the wrong things, or am I barking up the wrong tree completely?

 

[Remou]

You are very nearly there, in fact, you have gone past the turning. The code asks for SQL as string, export specification and the word document. You do not really need the export specification, so you can edit the code to leave it out, or you can create an export specification by saving you query using the export wizard, and selecting the Advanced button. Let us say you edit the code to remove the specification:

Private Sub cmdPrintInvoice_Click()
RunMailmerge "SELECT * FROM NameOfYourQuery" & _
"WHERE CustID = " & txtCustID.Value & _
" AND SaleDate = #" & _
Format(txtSaleDate.Value, "mm/dd/yyyy") & "#" & _
[s],"exInvoice"[/s], "NameOfYourTemplateDocument.doc"
End Sub

You can slim down Pseale's code, if you wish, by adding directory names and such like.

 

[domino3]

But when I tried to run the code like this:

Private Sub cmdPrintInvoice_Click()
RunMailmerge "SELECT * FROM q_mailmerge_letter", , "C:\Documents and Settings\FR\My Documents\mailmerge.doc"
End Sub

I got "compile error - argument not optional."

I've obviously taken out something vital, but I don't need the ID or the date.

 

[Remou]

What you are missing is the Specification I was talking about. It fits between the two commas. You will either have to edit the code to remove references to the specification or create one. I described how to create one in my last post.

 

[domino3]

I have created a specification ina delimited format, and found that I have had to remove the parameter value in the query referring to the combo box before the system would bring the export wizard up.

The code I have still doesn't work. It brings up a run time error. The code is:

Private Sub Command69_Click()

RunMailmerge "SELECT * FROM q_mailmerge_letter" & _
"WHERE ID_no = " & txtID_No.Value, "C:\Documents and Settings\FR\My Documents\q_mailmerge_letter.txt", "C:\Documents and Settings\FR\My Documents\mailmerge.doc"
End Sub


"C:\Documents and Settings\FR\My Documents\q_mailmerge_letter.txt", is the file I saved my export specification in. I've tried running the code with the file name it is saved and also the name of the specification itself "ic_mailmerge_export_specification" and neither works.

 

[Remou]

Please read my post dated 26 May 07 16:16 again, I said:
"you can create an export specification by saving you query using the export wizard, and selecting the Advanced button". What you have is not a Specification, it is a Where statement. An export specification will have a name like "qryXYZ Export Specification", though you can change that.

If you want to include paths with your document name, you will have to edit the code, that is, "slim down PSeale's code", as I mentioned. This line, for one, will have to change:

[tt]Set objWordDoc = GetObject(GetStartDirectory() & MergeDocumentFilename, "Word.Document")[/tt]

Here is the code for GetStartDirectory:

Private Function GetStartDirectory() As String
    GetStartDirectory = CurrentBackendPath() & "mm\"
End Function

 

[domino3]

I had already tried saving an export specification as "ic_mailmerge_export_specification" and put it between the two commas as below

Private Sub Command69_Click()
RunMailmerge "SELECT * FROM q_mailmerge_letter" & _
"WHERE ID_no = " & txtID_No.Value, "ic_mailmerge_export_specification", "C:\Documents and Settings\FR\My Documents\mailmerge.doc"
End Sub

I don't understand why this is a where statement, though. The original example had a where in it, as well.

I must admit, I don't really want to try fiddling with the module as I'm not sure what i can take out, and what I can't. And I might do more damage than I am capable of fixing at the moment.

 

[Remou]

You are right, your previous example was not a Where statement, but neither was it an export specification. Your current example looks more like it, but you seem to have missed my notes regarding directories. Do not include directory names.

[tt]RunMailmerge "SELECT * FROM q_mailmerge_letter" & _
"WHERE ID_no = " & txtID_No.Value, "ic_mailmerge_export_specification", "mailmerge.doc"[/tt]

The code requires a directory for several reasons, you can include your directory name with the edit shown below:

Private Function GetStartDirectory() As String
    GetStartDirectory = "C:\Documents and Settings\FR\My Documents\" 'CurrentBackendPath() & "mm\"
End Function