Question Best practice ...advice query/macro/mail merge (1 Viewer)

[mcclunyboy]

Hi,

I am looking for some advice, best practice or guidance.

I have a form, which the users obviously enter data onto. When the data is entered the click a button which runs a macro. The macro saves the record (which produces an ID displayed on the form), then it runs a query which is output to excel and used for a mailmerge (one record only).

My issue is this - the macro runs a query, which prompts the user for the new ID, this record is output to excel blah blah blah. Unfortunately the query is then displayed on screen in datasheet view when I would rather it wasn't. I have checked the macro properties for OpenQuery and can't see anyway to hide the results - is there a way to do this?

Am I able to acheive what I want via VB if the above isn't possible?

On an even more complicated note - the user opens the document in word, selects to finish the merge and archives the document before printing - is there anyway I can grab the filename automatically to populate a field in the database (at the moment the users must manually "attach" the file (in reality I am just storing a hyperlink)?

 

[mcclunyboy]

hopefully I am allowed an edit...

I have created the following which produces the mail merged document:

Private Sub Command43_Click()
       
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenQuery "qry_cp_permits_record_to_merge"
    DoCmd.Close acQuery, "qry_cp_permits_record_to_merge"
    DoCmd.OutputTo acQuery, "qry_cp_permits_record_to_merge", ".xls", "filepath"
    OpenFile ("filepath")
    DoCmd.GoToRecord acDataForm, "frm_contractor_permits_main", acNext
    
End Sub

Just had an epiphany which makes it redundant - will design a report from the above query which, when a button on the report is pressed outputs it to a .doc as a specific filename (prefixed by a field content (the ID) - this filepath can then get passed back to the database as a hyperlink.....

 

[mcclunyboy]

Hi,

I have found a solution I am happy with - please close this thread.

 

[Pat Hartman]

There is no reason to open the query prior to the OutputTo action. That is what is causing the query to open.

 

[mcclunyboy]

thanks that works - useful to know.