DAO to ADODB MultiVariable issue 2

[ZPBJ]

I am trying to write a procedure that will create multiple table entries (Access2002)in tblErrorTracker from a form titled frmErrorTracker and the multiple variables are in Combo5

Here is my attempt

Private Sub cmdEnd_Click()

Dim rs As New ADODB.Recordset
rs.Open "tblErrorTracker", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Dim varItem As Variant
Dim CASS_ID As String
For Each varItem In Me.Combo5.ItemsSelected
rs("me.Combo5.itemsselected") = "errordetailid"
rs.Update
Next varItem
rs.Close
Set rs = Nothing

Thanks for your help

__________________________________
Remember that time when I took the box? - Peter Griffin

 

[JerryKlmns]

I assume you have only one field in tblErrorTracker and you just add the values of that Combo5

Private Sub cmdEnd_Click()

Dim varItem As Variant
Dim CASS_ID As String
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "tblErrorTracker", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
For Each varItem In Me.Combo5.ItemsSelected
rs.AddNew
rs.Fields(0) = varItem
rs.Update
Next varItem
rs.Close
Set rs = Nothing

End Sub

 

[lupins46]

A combo box cannot have multiple selections. You need to use a list box.

 

[ZPBJ]

Jerry,

there are 9 fields all the others should stay the same with each entry.
They are;
ACCT_ID (number field)
Error (text field - can be either int or ext)
ErrorDetailID (number - is the field which is the variable on form)
System (text field - can either be OSI or OBI)
FunctionID (number (one selction from indexed table))
and the three remaining fields UserID, Date, Time are all auto-captured

Lupins,

It was a combo when it was a single select and I didn't rename it.

__________________________________
Remember that time when I took the box? - Peter Griffin

 

[JerryKlmns]

Dim varItem As Variant
Dim CASS_ID As String
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "tblErrorTracker", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
For Each varItem In Me.Combo5.ItemsSelected
rs.AddNew
rs.Fields("ACCT_ID") = Variable1
rs.Fields("Error") = Variable2 'Pls rename this field
rs.Fields("ErrorDetailID") = Variable3
rs.Fields("System") = Variable4 'Pls rename this field
rs.Fields("FunctionID") = varItem
rs.Fields("UserID") = Variable6
rs.Fields("Date") = Format(Date(),"yyyy-mm-dd") 'Pls rename this field
rs.Fields("Time") = Format(Time(),"hh:nn:ss") 'Pls rename this field
rs.Update
Next varItem
rs.Close
Set rs = Nothing

End Sub

 

[ZPBJ]

Jerry, I think I've almost got it. I'm now getting a message that says Ambiguous Name Detected and one of the error possibilities for that is trouble evaluating event

Private Sub cmdErrorLog_Click()
Dim varItem As Variant
Dim CASS_ID As String
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "tblErrorTracker", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
For Each varItem In Me.Combo5.ItemsSelected
rs.AddNew
rs.Fields("CASS_ID") = Variable1
rs.Fields("ErrorType") = Variable2
rs.Fields("ErrorDetailID") = Variable3
rs.Fields("System") = Variable4 'Pls rename this field
rs.Fields("FunctionID") = varItem
rs.Fields("CompletedBy") = Variable6
rs.Fields("Date") = Format(Date, "mm/dd/yyyy") 'Pls rename this field
rs.Fields("Time") = Format(Time(), "hh:mm:ss") 'Pls rename this field
rs.Update
Next varItem
rs.Close
Set rs = Nothing

End Sub

__________________________________
Remember that time when I took the box? - Peter Griffin

 

[ZPBJ]

This is where I am now and still getting the error

Private Sub cmdErrorLog_Click()
Dim varItem As Variant
Dim CASS_ID As String
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "tblErrorTracker", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
For Each varItem In Me.Combo5.ItemsSelected
rs.AddNew
rs.Fields("CASS_ID") = Me.CASS_ID
rs.Fields("ErrorType") = Me.Combo3
rs.Fields("ErrorDetailID") = varItem
rs.Fields("System") = Me.Combo7 'Pls rename this field
rs.Fields("FunctionID") = Me.Combo9
rs.Update
Next varItem
rs.Close
Set rs = Nothing

End Sub

__________________________________
Remember that time when I took the box? - Peter Griffin

 

[RoyVidar]

The compile error "Ambiguous name detected: <the name>", usually means you have more than one routine whith the same name within the same scope. Remove or rename the excess routine(s).

Roy-Vidar

 

[ZPBJ]

I found the error...this is the code that works. Thanks everyone for your assistance

Dim varItem As Variant
Dim CASS_ID As String
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "tblErrorTracker", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
For Each varItem In Me.Combo5.ItemsSelected
rs.AddNew
rs.Fields("CASS_ID") = Me.CASS_ID
rs.Fields("ErrorType") = Me.Combo3
rs.Fields("ErrorDetailID") = varItem
rs.Fields("ProcessorID") = Me.Combo7
rs.Fields("System") = Me.Combo9
rs.Fields("FunctionID") = Me.Combo11
rs.Update
Next varItem
rs.Close
Set rs = Nothing

__________________________________
Remember that time when I took the box? - Peter Griffin