Adding ListBox selections and combo box selection to Query.

[Torinjr]

Hi,

I have followed this link http://www.databasedev.co.uk/query_using_listbox.html but I am now trying to add a combo box selection to all the results in the query. My code looks identical to the one used in the example link. And i get the query to be created depending on the listbox selections, what code can i add to the example so it also displays the selected cbo box value next to every County selected.

Thanks

[Torinjr]

I have solved this by creating a second query based off the first. However this is a problem because i need to use the wordmailmerge, which will now not display the new query to be merged. Is there a way around this?

Same problem as this guy http://forums.techguy.org/business-a...uery-when.html.
I cant open the link posted by Rockn.

[ItsMe]

I would try creating a new Word doc by copying the other one. With the copy, select your new Query Object in the Navigation Pane so it is highlighted. Under the External Data tab in the Ribbon, click the Microsoft Word option for MailMerge. Use the wizard and see if the wizard overwrites the Connection String in the new Word Doc.

If not, maybe you can copy and paste the contents of the old Doc into a new one and then run the Wizard.

[Torinjr]

I worked around it by setting the query to make table, then pulling the mail merge from the new table. The only problem being that i get messages popping up, "You are about to run a make-table query that will modify data in your table", "The existing table 'Query Table' will be deleted before you run the query." and "You are about to paste x rows into a new table." All of which needs "yes" to be clicked for this to work. Anyway i can tell Access to click Yes on all of these messages so the user does not need to see them?

[June7]

Where is this code - macro or VBA?

If you are using OpenQuery or RunSQL, can disable warnings:

DoCmd.SetWarnings = False
...
DoCmd.SetWarnings = True

Or use CurrentDb.Execute which will not trigger warnings.

[Torinjr]

Thanks June that worked, changed my macros to vba so i could do this. Here is the code:
Private Sub Command4_Click()
'Turns hour glass cursor on during loading
DoCmd.Hourglass True
'Turns warning messages off
DoCmd.SetWarnings False
'Opens Query To add to table
DoCmd.OpenQuery "qryITT2", acViewNormal
'Opens Table to run mail merge from
DoCmd.OpenTable "New_Table"
DoCmd.RunCommand acCmdWordMailMerge
'Turns Hour glass cursor off
DoCmd.Hourglass False
'Turns warning messages back on
DoCmd.SetWarnings True

End Sub

Do you know how i might be able to set the word document that thye mail merge opens? I also want to be able to save each recipients as an individual pdf file.

[Torinjr]

I found this link http://www.gmayor.com/individual_merge_letters.htm with the code to split mail merge into seperate files and save them with different names:

Sub Splitter()
' Based on a Macro created 16-08-98 by Doug Robbins
' to save each letter created by a
' mailmerge as a separate file.
Dim Mask As String
Dim Letters As Long
Dim Counter As Long
Dim DocName As String
Dim oDoc As Document
Dim oNewDoc As Document
Set oDoc = ActiveDocument
oDoc.Save
Selection.EndKey Unit:=wdStory
Letters = Selection.Information(wdActiveEndSectionNumber)
Mask = "ddMMyy"
Selection.HomeKey Unit:=wdStory
Counter = 1
While Counter < Letters
DocName = "D:\My Documents\Temp\Workgroup\" & Format(Date, Mask) _
& " " & LTrim$(Str$(Counter)) & ".doc"
oDoc.Sections.First.Range.Cut
Set oNewDoc = Documents.add
'Documents are based on the Normal template
'To use an alternative template follow the link.
With Selection
.Paste
.EndKey Unit:=wdStory
.MoveLeft Unit:=wdCharacter, Count:=1
.Delete Unit:=wdCharacter, Count:=1
End With
oNewDoc.SaveAs FileName:=DocName, _
FileFormat:=wdFormatDocument, _
AddToRecentFiles:=False
ActiveWindow.Close
Counter = Counter + 1
Wend
oDoc.Close wdDoNotSaveChanges
End Sub

Where do i include this code into my current code and how do i call it???

[June7]

Perhaps this additional code at the end of the Click event. Or call the second procedure.