I have a number of forms dealing perhaps with members, boats, cruises, diner
evenings etc and want to send personal emails out using these forms as the
source of the eamil. There is (will be) a button on these forms to open a
form called "SendEmails" and the recordsource of the calling form is passed
to a field (SQLIn) on the SendEmail form.

I am trying to scan a message (memo field) on SendEmail form to find say
"XXX" e.g the message is "My surname is XXX" I then want to substiture the
person's name for XXX. The XXX is an unbound field SearchString on the
SendEmail form.

ReplaceField1 is a ComboBox that lists all the field name that it finds in
the forms SQLIn field, even if the source is something like "SELECT Member.*
FROM Member"

Code is
If Not IsNull(ReplaceField1) Then
SQLStg SQLStg = Replace(SQLIn, ReplaceField1, ReplaceField1 & " AS RF1 ", ,
1) End If

Later on I have
If Not IsNull(ReplaceField1) Then
EmailTextOut = Replace(EMailText, SearchString, !rf1 )
End If

Where EMailText is the message and EMailTextOut show "My surname is Smith"
All works perfectly providing the the original SQLStg is something like
"SELECT MemSurname, MemFirstName, MemEmail, ... From Member" However many of
the input queries are more like "SELECT Member.* FROM Member".

I want to be able to use this form to send anyone a personalised email, so my
question is how can I automatically expand the "*" in the SELECT clause so
that it will find MemSurname for example?

Thanks

Phil

On Fri, 20 Aug 2010 12:49:18 +0100, "Phil" <phil (AT) stantonfamily (DOT) co.uk>
wrote:

One option would be to open a recordset using that sql statement, and
then iterate over its Fields collection.

-Tom.
Microsoft Access MVP

On 20/08/2010 14:50:38, Tom van Stiphout wrote:
Thanks Tom, have partially solved the problem using the following code:-

Function ExpandSQL(FrmName As String) As String

Dim MyDb As Database
Dim MySet As Recordset
Dim Frm As Form
Dim QDF As QueryDef
Dim Fld As Field
Dim SQLIn As String
Dim ToExpand As String, ExpandedFlds As String
Dim TblName As String
Dim i As Integer, j As Integer, k As Integer

If Not IsLoaded(FrmName) Then
DoCmd.OpenForm FrmName
Set Frm = Forms(FrmName)
If Left(Frm.RecordSource, 7) = "SELECT " Then
SQLIn = Frm.RecordSource
Else
' ' Set QDF = Frm.RecordSource ' *******THIS IS WHERE I'M STUCK *******
'SQLIn = QDF.SQL
End If
DoCmd.Close acForm, FrmName
Else
Set Frm = Forms(FrmName)
SQLIn = Frm.RecordSource
End If

SQLIn = Replace(SQLIn, "!*,", ".*,") ' Replace any !* with .*
SQLIn = Replace(SQLIn, "!*", ".*") ' Replace any !* with .*

StartExpanding:
i = InStr(i + 1, SQLIn, ".*") ' Look for .*
If i = 0 Then
ExpandSQL = SQLIn ' No change
Exit Function
End If

ExpandIt:
j j = InStrRev(SQLIn, " ", i) ' look for preceding space
TblName = Mid(SQLIn, j + 1, (i + 2) - (j + 3))
ToExpand = "SELECT " & TblName & ".* FROM " & TblName & ";"

Set MyDb = CurrentDb
Set MySet = MyDb.OpenRecordset(ToExpand)

With MySet
For Each Fld In .Fields
'Debug.Print Fld.Name
ExpandedFlds = ExpandedFlds & TblName & "." & Fld.Name & ", "
Next
.Close
Set MySet = Nothing
End With

If If Mid(SQLIn, i + 2, 1) = "," Then ' does the string end with a comma or
space ExpandedFlds = Left(ExpandedFlds, Len(ExpandedFlds) - 2) ' Remove last
comma space Else
ExpandedFlds ndedFlds = Left(ExpandedFlds, Len(ExpandedFlds) - 2) ' Remove
last space End If

SQLIn = Replace(SQLIn, TblName & ".*", ExpandedFlds)
GoTo StartExpanding

End Function

A partial check seems to work providing the form's reordsource is a "SELECT"
statement. If it's recordsource is a query or table, I'm not sure how to
convert this into the SQL. The bit in the code "*******THIS IS WHERE I'M
STUCK *******" is where I am being terribly stupid

Thanks

Phil