Help! List code not working!

[Amit]

Hi,

This is a follow-up to my earlier post re: compile error.
I got some suggestions from Graham, but I'm stuck now, and
will appreciate help!!!

I'm using a button to insert the values selected in the
list on a form.

Here's the code:
====================================
Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click

Dim varItem As Variant
Dim strSql As String
Dim db As DAO.Database

Set db = CurrentDb()

For Each varItem In Me.List2.ItemsSelected
strSql = "Insert Into tblPersonColor (PersonID,
ColorID) Values (" & _
Me.ID & ",'" & Me.List2.ItemData(varItem)
& "')"
db.Execute strSql
Next varItem
Set db = Nothing
' Screen.PreviousControl.SetFocus
' DoCmd.FindNext

Exit_cmdSaveRecord_Click:
Exit Sub

Err_cmdSaveRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRecord_Click
End Sub
=====================================

Here's the history:
1. The code was giving me a compile error
2. I commented out the line ("Dim db As DAO.Database") and
it worked fine (values were inserted into the table)
3. Following Graham's suggestion, I uncommented the line,
made changes in 'Tools-->Reference' etc. and successfully
compiled the code.
4. Don't get any compile errors, but the values are not
being inserted into the table now.

Please help!!

-Amit

 

[Gary Miller]

Amit,

Did you try the RunSQL method that I also mentioned? If you
do this, you don't need to Dim db at all. Your code would
look like this if you used that as an alternative...

Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click

Dim varItem As Variant
Dim strSql As String

For Each varItem In Me.List2.ItemsSelected
strSql = "Insert Into tblPersonColor (PersonID,
ColorID) Values (" & _
Me.ID & ",'" & Me.List2.ItemData(varItem)
& "')"
' *******
DoCmd.RunSQL strSql
' *******
Next varItem

' Screen.PreviousControl.SetFocus
' DoCmd.FindNext

Exit_cmdSaveRecord_Click:
Exit Sub

Err_cmdSaveRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRecord_Click
End Sub


--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

 

[Amit]

Gary,

I tried what you suggested. It works. But, how do I
suppress the messagebox that pops up telling me I'm about
to append one row??

-Amit

 

[Gary Miller]

Glad to hear that it worked! You can suppress the message
with the SetWarnings command. Make sure and put a copy of
the last line in your error handler as well to make sure
that the system warnings are turned back on if something
triggers an error...

DoCmd.SetWarnings = False
DoCmd.RunSQL strSql
DoCmd.SetWarnings = True

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

 

[Amit]

Hi Gary,

Thanks again. That worked like a charm.

I did change the code to "DoCmd SetWarnings False" as the
code below with the "=" was giving a compile error.

Tx.

-Amit

 

[Gary Miller]

You are right. That's what I get for being in a bit of a
hurry.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________