The message is pretty explicit.
You have a statement "For Each varItm In ctl.ItemsSelected" in your code,
but you don't have a "Next" (or "Next varItm") statement anywhere.
I'm guessing it needs to be between
DoCmd.RunSQL (strSQL)
and
'Cleans out the data from tblMailingListUnique.
DoCmd.RunSQL "DELETE tblMailingListUnique.* FROM tblMailingListUnique"
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"helegua" <(E-Mail Removed)> wrote in message
news:8C318DAB-3FBF-48D6-9FBD-(E-Mail Removed)...
> new to VB the code below keeps returning Compile error need help Please
>
> Private Sub cmdCreateList_Click()
>
> Dim Item As Variant
> Dim intCounter As Integer
> Dim intMaxCount As Integer
> Dim strSQL As String
> Dim frm As Form
> Dim ctl As Control
> Dim varItm As Variant
> Dim strGroup As String
> Dim strTable As String
>
> Application.SetOption "Confirm Action Queries", False
> 'Cleans out the data from the Mailing List table
> DoCmd.RunSQL "DELETE tblMailingList.* FROM tblMailingList"
>
> 'Set up counter to select all lists
> intMaxCount = Me.MailingList.ListCount - 1
> intCounter = 0
>
>
> 'Goes through list box and gets selected groups
> Set ctl = Me.MailingList
> For Each varItm In ctl.ItemsSelected
> strGroup = ctl.Column(0, varItm)
>
> 'Inserts records for each selected group into tblMailingList
> strSQL = "INSERT INTO tblMailingList ( Salutation, FirstName, MiddleName,
LastName, Suffix, " & _
> "Address, City, State, Zip, Company ) " & _
> "SELECT MainDonorBio.Prefix, MainDonorBio.FirstName, MainDonorBio.MI,
MainDonorBio.LastName, " & _
> "MainDonorBio.Suffix,
IIf([PreferredMailingAddress]='P',[HomeAddressLineOne],[HomeAddressLineTwo],
[WorkAddressLineOne],[WorkAddressLineTwo]) AS Address, " & _
> "IIf([PreferredMailingAddress]='P',[HomeCity],[WorkCity]) AS City, " & _
>
"IIf([PreferredMailingAddress]='P',[HomeStateOrProvince],[WorkStateOrProvinc
e]) AS State, " & _
> "IIf([PreferredMailingAddress]='P',[HomePostalCode],[WorkPostalCode]) AS
Zip, " & _
> "IIf([PreferredMailingAddress]='P',Null,[Organization]) AS Company1 " & _
> "FROM MainDonorBio INNER JOIN Mailings ON MainDonorBio.DonorID =
Mailings.DonorID " & _
> "WHERE (((Mailings.MailingList)= '" & strGroup & "') AND
(MainDonorBio.BadMailingAddressYN)=False)"
>
> DoCmd.RunSQL (strSQL)
>
>
> 'Cleans out the data from tblMailingListUnique.
> DoCmd.RunSQL "DELETE tblMailingListUnique.* FROM
tblMailingListUnique"
>
> 'Appends records from tblMailingList to tblMailingListUnique so that
> 'duplicates are removed.
> DoCmd.OpenQuery "qryMailingListAppend"
> DoCmd.OpenTable "tblMailingListUnique"
>
> End Sub
Similar Threads
