Bear with me, this is complicated.
I have a form called DBUsableObjects that lists pretty well all the tables,
queries, forms & reports in the database basically loaded from MsysObjects.
This has a conrtol on it ObjName which is the name of the table, query, form
or report. From this I open a second form SendEmails which uses the header
and message information to send individually addressed emails. The
DBUsableObjects form passes a SQL string to the SendEmails form. It could be
the QueryDef.SQL or the Form's Recordsource etc. See code below


Private Sub Email_Click()

Dim Frm As Form
Dim Rpt As Report
Dim SQLStg As String

On Error GoTo Email_Err

If [Type] = 5 Then ' Query
SQLStg = CurrentDb.QueryDefs(ObjName).SQL
ElseIf [Type] = 6 Then ' Tables
SQLStg = "SELECT " & ObjName & ".* FROM " & ObjName & ";"
ElseIf [Type] = -32768 Then ' Forms
DoCmd.OpenForm ObjName, , , , acFormReadOnly, acHidden, "Hidden"
Forms(ObjName).Visible = False
Set Frm = Forms(ObjName)
If Left(Frm.RecordSource, 7) = "SELECT " Then
SQLStg = Frm.RecordSource
Else
SQLStg = CurrentDb.QueryDefs(Frm.RecordSource).SQL
End If
Set Frm = Nothing
DoCmd.Close acForm, ObjName
ElseIf [Type] = -32764 Then ' Reports
DoCmd.OpenReport ObjName, acViewPreview, , , acHidden, "Hidden"
Reports(ObjName).Visible = False
Set Rpt = Reports(ObjName)
If Rpt.RecordSource = "" Then
MsgBox "There is no information for this report", vbInformation
Set Rpt = Nothing
DoCmd.Close acReport, ObjName
Exit Sub
End If
If Left(Rpt.RecordSource, 7) = "SELECT " Then
SQLStg = Rpt.RecordSource
Else
SQLStg = CurrentDb.QueryDefs(Rpt.RecordSource).SQL
End If
Set Rpt = Nothing
DoCmd.Close acReport, ObjName
Else
MsgBox "Unknown object type - Can't do an Email", vbInformation
End If

DoCmd.OpenForm "SendEMails"

' Pass Info
Set Frm = Forms!SendEMails
Frm!FormName = Me.Name
Frm!SQLIn QLIn = ExpandSQL(SQLStg)' Replaces MyTable.* with all the field
names in the table Exit Sub

Email_Err:
If Err = 2501 Then ' The OpenReport action was canceled.
MsgBox MsgBox "Sorry - can't use this report as a source for mail merge",
vbInformation Else
MsgBox "Error " & Err & " " & Err.Description
End If

End Sub

This all works perfectly and can be used for emails or mail merge.

My problem is that in the SendEmails form, I want to have a from & to range.
Generally this will be "from ClubMember" to "to ClubMember", but not always.
Depending on the source of the SQL, it could be people who live in a town
where "from Town" = "to Town" or People who own Sailing Dingys.

Any ideas how I can start to examine the passed SQL to feed the CboFrom &
CboTo combo boxes so that I can limit the range of people to send emails to

Thanks

Phil

Phil wrote:
Maybe add a column, ex: RangeCode, in the SQL? 1 = From/To member, 2 =
From/To town, etc. Or make the columns heading "MemberCode",
"TownCode", etc if your need the type prior to opening the form? Of
create a lookup table based on types?

On 30/10/2010 20:06:43, Salad wrote:
Hi Salad
I was thinking more of trying to parse the SQL to find the table names.
I think the table names always come after the word "FROM" or "JOIN", but on
the other hand, so do query names, but as most of my queries begin with the
letter "q" it should be possibe to eliminate them. Vaguely wondering if I can
use the above to populate 1 combo box to allow the user to select the table
they are interested in, then populate a second combo box to show the fields
in that table, then use that to populate the CboFrom & CboTo combos. Looks an
"interesting" exercise

Thanks again

Phil

Phil wrote:
OK. I thought originally you had specific fields per type of select.
Or perhaps certain fields for certain objnames. Sounds complicated.
Good luck.

populating the combo boxes. That was in the end the easy bit.
Now all that is left is to rebuild the original query with apart from any
existing criteria to add the >= CboFrom & <= CboTo and ensure the ORDER BY
clause has the selected field before any existing ORDER BY clauses. Phil