Error when executing SQL statment

[ColinWard]

whenever I run the following code I get an error saying that 'Trade Show
and Conference Contacts database could not find the form 'Events_Form'
referred to in a macro or in Visual Basic Code. The code I am running in
the On_Click event of a button is as follows:

--Code Start--

Private Sub cmdAddTeams_Click()
On Error GoTo Err_cmdAddTeams_Click
Dim VarItem As Variant, SpecStr As String, strSQL As String
Dim strEventID As String, strYear As String
strEventID = ""
strYear = ""
'Both the Event ID and the Year are passed to this form as open
arguments separated by a : (colon)
If Not IsNull(Me.OpenArgs) Then 'Strip out the eventID and the
EventYear
strEventID = Left$(Me.OpenArgs, InStr(1, Me.OpenArgs, ":") - 1)
'Strip out the Event ID
strYear = Right$(Me.OpenArgs, Len(Me.OpenArgs) - InStr(1,
Me.OpenArgs, ":")) 'Strip out the Year
Else
FormattedMsgBox "Error: This form needs to be opened through
code", vbCritical, "Error!"
DoCmd.Close
End If
'This for loop iterates through each of the selected items in the
Teams List and
'adds each tem to the Teams by Year by Event Table and then
populates the
'lstTeams Listbox on the Events Form with the teams that were
selected.
For Each VarItem In lstTeams.ItemsSelected
strSQL = "Insert Into [Teams by Year by Event
Table](EventID,TeamID,EventYear) "
strSQL = strSQL & " VALUES (" & strEventID & "," &
lstTeams.Column(0, VarItem) & ", " & strYear & ");"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL 'add the selected teams to the event
DoCmd.SetWarnings True
DoCmd.Close
--> Forms!Events_Form!lstTeams.Requery

Next VarItem

Exit_cmdAddTeams_Click:
Exit Sub

Err_cmdAddTeams_Click:
FormattedMsgBox Err.Description
Resume Exit_cmdAddTeams_Click
End Sub

--Code End--

the line with the arrow next to it is where the error must be occurring
because it is the only place in the code where I refer to the Events
Form explicitly. Please note that the code does what it is supposed to
do but it always gives that error.

Thank you

Colin


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It appears that the ListBox you are Requerying is on the same form as
the VBA module. That means you can refer to it this way:

Me!lstTeams.Requery

"Me" refers to the form the VBA code module is attached to.

Another thing - you have a requery inside a loop. It would be better
programming logic to put the Requery after the loop. This will make
the loop run faster, and, once you've updated the ListBox's
Recordsource data, the ListBox only needs to be Requeried once.

HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP9Yz5IechKqOuFEgEQLOJQCdGaidARJORvIgetnOEjHtFP OYEcMAoK/5
MxyJH7iganD6JXzAgqBD25d6
=jEcN
-----END PGP SIGNATURE-----

ColinWard wrote:

whenever I run the following code I get an error saying that 'Trade Show
and Conference Contacts database could not find the form 'Events_Form'
referred to in a macro or in Visual Basic Code. The code I am running in
the On_Click event of a button is as follows:

--Code Start--

Private Sub cmdAddTeams_Click()
On Error GoTo Err_cmdAddTeams_Click
Dim VarItem As Variant, SpecStr As String, strSQL As String
Dim strEventID As String, strYear As String
strEventID = ""
strYear = ""
'Both the Event ID and the Year are passed to this form as open
arguments separated by a : (colon)
If Not IsNull(Me.OpenArgs) Then 'Strip out the eventID and the
EventYear
strEventID = Left$(Me.OpenArgs, InStr(1, Me.OpenArgs, ":") - 1)
'Strip out the Event ID
strYear = Right$(Me.OpenArgs, Len(Me.OpenArgs) - InStr(1,
Me.OpenArgs, ":")) 'Strip out the Year
Else
FormattedMsgBox "Error: This form needs to be opened through
code", vbCritical, "Error!"
DoCmd.Close
End If
'This for loop iterates through each of the selected items in the
Teams List and
'adds each tem to the Teams by Year by Event Table and then
populates the
'lstTeams Listbox on the Events Form with the teams that were
selected.
For Each VarItem In lstTeams.ItemsSelected
strSQL = "Insert Into [Teams by Year by Event
Table](EventID,TeamID,EventYear) "
strSQL = strSQL & " VALUES (" & strEventID & "," &
lstTeams.Column(0, VarItem) & ", " & strYear & ");"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL 'add the selected teams to the event
DoCmd.SetWarnings True
DoCmd.Close
--> Forms!Events_Form!lstTeams.Requery

Next VarItem

Exit_cmdAddTeams_Click:
Exit Sub

Err_cmdAddTeams_Click:
FormattedMsgBox Err.Description
Resume Exit_cmdAddTeams_Click
End Sub

--Code End--

the line with the arrow next to it is where the error must be occurring
because it is the only place in the code where I refer to the Events
Form explicitly. Please note that the code does what it is supposed to
do but it always gives that error.

Thank you

Colin

[Larry Linson]

"OnClick event of a button" doesn't tell us what Form that button is in. If
the button is in "Events_Form", then, of course, you just closed it before
referring to it (with the "DoCmd.Close"). If not, perhaps you would clarify
for us.

Larry Linson
Microsoft Access MVP

Forms!Events_Form!lstTeams.Requery"
"ColinWard" <je*********@hotmail.com> wrote in message
news:3f*********************@news.frii.net...

whenever I run the following code I get an error saying that 'Trade Show
and Conference Contacts database could not find the form 'Events_Form'
referred to in a macro or in Visual Basic Code. The code I am running in
the On_Click event of a button is as follows:

--Code Start--

Private Sub cmdAddTeams_Click()
On Error GoTo Err_cmdAddTeams_Click
Dim VarItem As Variant, SpecStr As String, strSQL As String
Dim strEventID As String, strYear As String
strEventID = ""
strYear = ""
'Both the Event ID and the Year are passed to this form as open
arguments separated by a : (colon)
If Not IsNull(Me.OpenArgs) Then 'Strip out the eventID and the
EventYear
strEventID = Left$(Me.OpenArgs, InStr(1, Me.OpenArgs, ":") - 1)
'Strip out the Event ID
strYear = Right$(Me.OpenArgs, Len(Me.OpenArgs) - InStr(1,
Me.OpenArgs, ":")) 'Strip out the Year
Else
FormattedMsgBox "Error: This form needs to be opened through
code", vbCritical, "Error!"
DoCmd.Close
End If
'This for loop iterates through each of the selected items in the
Teams List and
'adds each tem to the Teams by Year by Event Table and then
populates the
'lstTeams Listbox on the Events Form with the teams that were
selected.
For Each VarItem In lstTeams.ItemsSelected
strSQL = "Insert Into [Teams by Year by Event
Table](EventID,TeamID,EventYear) "
strSQL = strSQL & " VALUES (" & strEventID & "," &
lstTeams.Column(0, VarItem) & ", " & strYear & ");"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL 'add the selected teams to the event
DoCmd.SetWarnings True
DoCmd.Close
--> Forms!Events_Form!lstTeams.Requery

Next VarItem

Exit_cmdAddTeams_Click:
Exit Sub

Err_cmdAddTeams_Click:
FormattedMsgBox Err.Description
Resume Exit_cmdAddTeams_Click
End Sub

--Code End--

the line with the arrow next to it is where the error must be occurring
because it is the only place in the code where I refer to the Events
Form explicitly. Please note that the code does what it is supposed to
do but it always gives that error.

Thank you

Colin


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!