Compile Error: For without next

G

Guest

new to VB the code below keeps returning Compile error need help Pleas

Private Sub cmdCreateList_Click(

Dim Item As Varian
Dim intCounter As Intege
Dim intMaxCount As Intege
Dim strSQL As Strin
Dim frm As For
Dim ctl As Contro
Dim varItm As Varian
Dim strGroup As Strin
Dim strTable As Strin

Application.SetOption "Confirm Action Queries", Fals
'Cleans out the data from the Mailing List tabl
DoCmd.RunSQL "DELETE tblMailingList.* FROM tblMailingList

'Set up counter to select all list
intMaxCount = Me.MailingList.ListCount -
intCounter =


'Goes through list box and gets selected group
Set ctl = Me.MailingLis
For Each varItm In ctl.ItemsSelecte
strGroup = ctl.Column(0, varItm

'Inserts records for each selected group into tblMailingLis
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],[WorkStateOrProvince]) 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 tha
'duplicates are removed
DoCmd.OpenQuery "qryMailingListAppend
DoCmd.OpenTable "tblMailingListUnique

End Sub
 
C

Cheryl Fischer

You are missing a Next statement, which For...Next loops require. You need
to insert: Next varItm somewhere in your code so that Access knows when
to proceed/loop to the next selected item. I suspect the Next varItm
needs to be inserted after your "DoCmd.RunSQL (strSQL)" statement.


--

Cheryl Fischer, MVP Microsoft Access



helegua said:
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,
Click to expand...
IIf([PreferredMailingAddress]='P',[HomeAddressLineOne],[HomeAddressLineTwo],
[WorkAddressLineOne],[WorkAddressLineTwo]) AS Address, " & _
"IIf([PreferredMailingAddress]='P',[HomeCity],[WorkCity]) AS City, " & _
Click to expand...
"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
Click to expand...
 
D

Douglas J. Steele

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

(no e-mails, please!)


helegua said:
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,
Click to expand...
IIf([PreferredMailingAddress]='P',[HomeAddressLineOne],[HomeAddressLineTwo],
[WorkAddressLineOne],[WorkAddressLineTwo]) AS Address, " & _
"IIf([PreferredMailingAddress]='P',[HomeCity],[WorkCity]) AS City, " & _
Click to expand...
"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
Click to expand...
 
Top