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.

 

[Remou]

What is the recordsource of your combo? Have you made sure that the bound column is ID_No? You can always check the value by putting a message box in the button code:

[tt]MsgBox Me.Combo_New & ""[/tt]

 

[domino3]

I've just realised I've used the wrong id field name. It now works. But thank you for your response.

 

[domino3]

The command button I asked about before now works fine, but I would like to know anyone could let me know how to connect the query result to a mailmerge letter in word. I've tried adding the letter itself as an inserted object on my form, but would like to be able to get the letter to then merge to the record selected from the combo box. This would then give the result of the user calling up a record on the form, then being able to click on the inserted letter object and the mailmerge producing the merged letter. If anyone could help, that would be great.

 

[Remou]

I like this:
'Native' mailmerge reports - as painless as possible
faq181-5088

It can be simplified.

You can also consider bookmarks and simply building a letter with automation.

 

[domino3]

Thanks for your reply, but my knowledge of vba is very limited, and I don't understand how I use the faq you quoted above on my form. Do I add it somehow to the button I've created on the form.

 

[Remou]

There are several ways to do a mailmerge, some of which require more coding than others.
1. You can create a document that links to a table or query and mailmerge from that. This requires very little coding, but you have very little control and it can go very wrong on occasion.
2. You can create a document with bookmarks and then fill in the document with code. This requires a little more coding. You will find a number of posts and a few FAQs on this topic.
3. Then there is PSeale's idea, which is similar to 1) above, but with more control and fewer problems. In outline, it involves saving a text file and using this to create the mail merge. The code automates the whole thing, but you can try something similar.
* First add code to a button to create a textfile from the query (DoCmd.TransferText).
* Next, create a merge document and choose the textfile you just created as the record source.
* Save this document.
* Finally, add code to the button to open this document in Word (FollowHyperlink "NameOfDocument.doc").

This is a much simpler version of PSeale's idea and your textfile will be overwritten each time you run the code, whereas PSeale's allows you to create a new file each time.

 

[domino3]

I've now discovered another reply titled "Mail Merge to Word using Access query and form" from which I've added the code to my database form button and it works really well. The only question I now have is how I would adapt the code to get the mailmerge to go straight to the mailmerged document using the record selected in the background query, rather than needing the user to click on the mailmerge button in the word toolbar? I know I am being picky, but I know that the users I have will ask if it is possible, and I'd rather have an answer before showing them what I have so far. The code I am using is below:

Private Sub Command63_Click()
On Error GoTo Err_Command63_Click
' Button launches Word and opens a specified document
' And runs an optional macro. the macro could print out the word doc and quit

Dim retval As Variant
Dim DocName, MacroName As String
' Drive and Full path to name and location of Word document
DocName = "c:\MyMerge.doc"

'Optional Macro Name. Note Word Macro name cannot have any spaces in it.
'MacroName = "/M" & "MacroName"

'Note full path to Word including Drive and folder
retval = Shell("c:\Program Files\Microsoft Office\Office\WinWord.exe" & " " & DocName & MacroName, vbNormalFocus)

Exit_Command63_Click:
Exit Sub

Err_Command63_Click:
MsgBox Err.Description
Resume Exit_Command63_Click

End Sub


Thank you.

 

[domino3]

I've can get the code in the posting above to work when used with a standalone database and a word document on a single computer. However, I'm struggling to make it work on a word document on a server. The path I have used is correct, but the message I get is that the file wasn't found. Is this a problem with files on servers when using this type of mailmerge by access button? Or is there some other basic change I need to make to the code, which I haven't even thought of yet?

 

[domino3]

My other thought was that the database I am now trying to run it from is a backend database, with the form itself on the frontend. However, I have copied the query over to the backend database, and other mailmerges I have which start with the word document instead of a form on access all work fine. If anyone has any ideas on this post or the last one and how to solve this problem, I would be very grateful. Thank you.

 

[domino3]

I'm still ploughing away at this, and am trying to do the docmd transfer text you suggested a while ago. I cnouldn't understand how to do it as vba code, so am trying to do it as a macro. However, I am getting the error message "you cannot import a text file unless it has one of these extensions". The arguments I have filled in are:

Specification Name:
Table Name: ic_mailmerge_button_form
File Name: \\ company\ Initial
Contact\L - Contact mailmerge from database.doc
Has Field Names: No
HTML Table Name:
Code Page: 50000

If anyone could help me by telling me where I ma going wrong, I would be very grateful.

 

[Remou]

You do not wish to import a text file, you wish to export a query as a textfile:

[tt]DoCmd.TransferText acExportDelim, , "qryQuery", "c:\docs\MailDat.txt"[/tt]

 

[domino3]

Thanks for the reply. It's now gone one stage further, but is giving an error message "Cannot update. Database or object is read-only". But the document I am trying to open isn't read only. Could it be this that is causing my other mailmerge command button to fail (as in posting of 22 May) and, if so, how do I check to see whether the database is read only? I've never had a problem with doing mailmerges when I start them from the word document, though.

 

[Remou]

What file name did you use for the export file? You must use:

Filename.txt

The extension is important, unless you feel like tampering with the registry.

 

[domino3]

I've now changed the file name to end in a .txt, and the error message has gone. However, I still can't get the mailmerge to work. I've opened my original mword document on the server, and pointed the mailmerge to the text file I've created with the button, and confirm that the datasource is txtfile, but it can't find any records. I've tried all sorts of combinations for the field and record delimiters, but everything seems to return blank records.

 

[Remou]

Have you made sure that there are records in the query or table that you are exporting?

 

[domino3]

I'm making progress, but very slowly. The mailmerge now selects a record as required. But I have fields in my original mailmerge query and on my mailmerge letter that I can't get to appear on the mailmerge itself. I've added all the fields I need to the combobox which i use to select the record I want, and to the query in the vba code on the button, but they still don't appear. I've also checked that the appear both in the frontend and backend database, but still nothing. I'm at a loss as to what to do next to get it to work.

 

[Remou]

Have you looked at the textfile that you exported to see if the fields are in it?

 

[domino3]

Yes, I've checked in the document mymerge.txt and the fields are there. Is that where you meant?

 

[Remou]

Yes. Why not try connecting it to a new document, as a test?