Can I save search results data into a table? (1 Viewer)

[boknoval]

The only reason why I want to make this table, is that, in Microsoft Mail Merge, filtering option in order to select recipients for my letter is a bit more messy from there (options are limited.)

What I wanted to happen:
Say for example I have run a search and it gave me 4 results…

When I click a button (Populate Mail Merge List), it will save these 4 properties’ information into the Mail_Merge table (this table is just a clone of the Properties table except, I have removed the fields which I won’t be using for my letter merge.)

And also, everytime I populate my mail merge list, what was already stored on the Mail_Merge table must be deleted, only the new properties that were populated must be there.

Can anyone help me pls?

I have attached my db copy for reference...

 

[pr2-eugin]

Hello Bok, you do not have to bloat the Database by adding temporary Tables and replacing them.. You can export the Query to a Excel sheet and use that as the source of the Mail Merge document??

Just my opinion.. Wait and see what others say about this..

 

[boknoval]

Hi paul, what do you mean by "export the query to an excel sheet"? do you mean the data that was filtered through the search made will be the one that will be exported? That's sounds more like a great idea than what I have in mind..

 

[boknoval]

And by all means, could help me work through with both approach, one is through that temp table, and the second is through that excel sheet? Coz' I'm not sure if my partner would want to use another file aside from the database for the mail merge. Thanks!

 

[pr2-eugin]

Well the first method involves a DoCmd.TransferSpreadSheet.. and then it will be a normal Word Mail Merge, with its source looking into the newly exported file..

The other method is to use a Make Table query, research on these topics, it should give you a head start..

 

[boknoval]

now i'm lost.. tried reading the links you've given me, and can't get even get a head start out of it.. *sigh*

 

[boknoval]

I'm still stuck with this one.. Can someone help me pls?

 

[pr2-eugin]

Bok, I am sorry was a bit busy at work, so was unable to look very closely to your questions.. Okay.. inorder to create table dynamically, you will use INTO <tableName>, so something like..

strSQL = "SELECT Mail_Campaign.* [COLOR=Blue][B]INTO tmpTbl[/B][/COLOR] FROM Mail_Campaign WHERE Mail_Campaign.PID IN (SELECT PID FROM qryPropertiesData " & BuildFilter & ")"

Change the above query as per requirement.. then execute the Query using

CurrentBD.Execute(strSQL)

then export the created table to Excel using DoCmd.TransferSpreadSheet..

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tmpTbl", "C:\temp.xls", 1

Putting all pieces together, you will have the following..

Private Sub btnMailMerge_Click()
    Dim dbObj As DAO.Database
    Dim strSQL As String
    
    Set dbObj = CurrentDb

    [COLOR=Red]DoCmd.DeleteObject acTable, "tmpTbl"[/COLOR]
    strSQL = "SELECT Mail_Campaign.* INTO tmpTbl FROM Mail_Campaign WHERE Mail_Campaign.PID IN (SELECT PID FROM qryPropertiesData " & BuildFilter & ")"
    
    dbObj.Execute (strSQL)
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tmpTbl", "C:\temp.xls", 1
End Sub

Just make sure some important things.. I have NOT placed a proper Error handler, and the highlighted line is sure to cause error if you delete the table manually, (that line is placed to delete old table and place new ones.. every time the operation is performed)

Good luck..

 

[boknoval]

Awesome! thank you very much Paul! Works perfectly.. thanks again!

 

[pr2-eugin]

Goody good.. Glad to help Bok..