Invalid Qualifier

[Guest]

I'm trying to build a distribution list for an email by stepping through a table of names and titles. If the title is "director", add the name to the dist-list string. The idea seemed pretty simple, but the following code get "invalid qualifier" error with stTableName (the table name variable) highlighted. Any idea what's wrong

Public Function Email_SD_Rpt(
Dim Distlist, stTableName As Strin

stTableName = "Account Managers
DoCmd.OpenTable stTableName, acViewNormal, acReadOnl

Do While Not EOF(stTableName
If stTableName.[TITLE] Like "Director*" The
stDistlist = stDistlist & "; " & stTableName.[FULLNAME
stTableName.MoveNex
Loo

MsgBox ("stDistlist = " & stDistlist

End Functio

 

[Gerald Stanley]

You will need to populate a recordset in order to process
the 'Account Managers' table. Here is an example of how it
would be done in ADO.

Public Function Email_SD_Rpt()
Dim Distlist As String
Dim rs As ADODB.Recordset
Dim comm As ADODB.Command

Set comm = New ADODB.Command
Set comm.ActiveConnection = CurrentProject.Connection
comm.CommandText = "SELECT [Title], [FullName] FROM
[Account Managers]"

Set rs = comm.Execute

Do While Not rs.EOF
If rs![TITLE] Like "Director*" Then
stDistlist = stDistlist & "; " & rs![FULLNAME]
End If
rs.MoveNext
Loop

MsgBox ("stDistlist = " & stDistlist)

rs.Close
Set rs = Nothing
Set comm = Nothing

End Function

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
I'm trying to build a distribution list for an email by

stepping through a table of names and titles. If the title
is "director", add the name to the dist-list string. The
idea seemed pretty simple, but the following code get
"invalid qualifier" error with stTableName (the table name
variable) highlighted. Any idea what's wrong?

Public Function Email_SD_Rpt()
Dim Distlist, stTableName As String

stTableName = "Account Managers"
DoCmd.OpenTable stTableName, acViewNormal, acReadOnly

Do While Not EOF(stTableName)
If stTableName.[TITLE] Like "Director*" Then
stDistlist = stDistlist & "; " & stTableName.[FULLNAME]
stTableName.MoveNext
Loop

MsgBox ("stDistlist = " & stDistlist)

End Function

.

 

[SA]

Dorci:

You are mixing code activities that are mutually exclusive

Docmd.Open table opens the table in the user interface which is not what you
want. You need to open it in memory as a recordset, then you can loop
through it. Its much faster to select the records when the recordset is
opened rather than to scan the entire table looking for matches.

Here's code that would get you to the point you are seeking (Note it would
be very wise to create a non-unique index the Title field):

Public Function Email_SD_Rpt() as String
Dim Cdb as DAO.Database
Dim rsAcctMgrs as DAO.Recordset
Dim stDistList as String, StrSQL as String

Set Cdb = CurrentDb()
strSQL = "Select [FullName], FROM [Account Managers] " & _
"WHERE [Account Managers].[Title] Like 'Director*'"
Set rsAcctMgrs = Cdb.OpenRecordset(strSQL, dbOpenDyanset, dbReadOnly)
If rsAcctMgrs.EOF = False Then
rsAcctMgrs.MoveFirst
Do Until rsAcctMgrs.EOF
stDistlist = stDistlist & rsAcctMgrs![FULLNAME] & " [SMTP: " & _
rsAcctMgrs![Email] & "];"
rsAcctMgrs.MoveNext
Loop
End If
rsAcctMgrs.Close
Set rsAcctMgrs = Nothing
Set Cdb = Nothing
If len(stDistList)> 0 Then 'i.e. you've got values
'Strip the last ";" from the DistList
stDistList = Left(stDistList, Len(stDistList)-1)
Email_SD_Rpt = stDistList
Else
Email_SD_Rpt=""
End If
End Function

 

[Guest]

Gerald
I have no idea what all that means, but it works!!! Thanks so much!