copy query to another query on the fly (1 Viewer)

[nelson]

I have a hybrid code below (got it from someone else here...) doing UNION...now the results are placed in QryGroupsCommitteeMultiSelect, now I want to put a "Save Query" button so a user can place it in a temporary Query so he/she can use it for Mail Merging, is this possible?

what I'm planning to do is iterate thru the query(QryGroupsCommitteeMultiSelect) and record by record insert it in a new Query, is this right? I'm confused. Anyone can shed light on this? thanks in advance.

Regards,
Nelson



_____________________________________________
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("QryGroupsCommitteeMultiSelect")


' Loop through the selected items in the list box and build a text string
If Me!listGroups.ItemsSelected.Count > 0 Then
For Each varItem In Me!listGroups.ItemsSelected
txtSelected = Me!listGroups.ItemData(varItem)
strCriteria = strCriteria & " SELECT People.FirstName, People.LastName,People.Email AS [E-mail],People.Position,People.Address,People.City,People.State, People.PostCode,People.WorkPhone,People.MobilePhone,People.FaxNumber FROM People, " & txtSelected & _
" WHERE People.contactID=" & txtSelected & ".contactID" & " Union"
Next varItem
'this one truncates the last " OR"
strCriteria = left(strCriteria, Len(strCriteria) - 5)
Else
'lists ALL
MsgBox "Please select an organisation"
End If

' Build the new SQL statement incorporating the string
strSQL = strCriteria
'MsgBox strSQL
' Apply the new SQL statement to the query and check if it's == 0
If Me!listGroups.ItemsSelected.Count = 0 Then
MsgBox "Nothing is selected. Please select an organisation again"
Else
qdf.SQL = strCriteria
End If

' Open the query
'---------
Me.subForm.SourceObject = "Query.QryGroupsCommitteeMultiSelect"

'formatting
'Me.subForm.Form.NavigationButtons = False
Me.subForm.Form.RecordSelectors = False
Me.subForm.Form.AllowAdditions = False
Me.subForm.Form.AllowEdits = False
Me.subForm.Form.AllowDeletions = False
Me.subForm.Form.DataEntry = False
Me.subForm.Form.DataChange = False

'update the query
subForm.Requery
'---------

' Empty the memory
Set db = Nothing
Set qdf = Nothing

 

[nelson]

ok here's the tricky part.....tried to do open recordset with no success...it doesn't like SQL with "UNION" in it...will try MAKE TABLE QUERY...and see what happens...

 

[nelson]

dont worry..I fixed this issue... , recycled code from this forum !! *yay* !!! problem solved !!!


Dim strIn As String
Dim strOut As String
Dim strName As String
Dim strQuery As String

strQuery = "qryMyQuery"
strName = "tblMyTable"

strIn = CurrentDb.QueryDefs(strQuery).SQL
strOut = Replace(strIn, "From", " INTO [" & strName & "] FROM")

CurrentDb.Execute strOut

 

[nelson]

it works if it has no UNION, anyone here know how to execute a Select query with UNION ? thanks in advanced.