I am trying to write records to a table (called "tbl_Assigned Job Class Skills") that has 2 fields. The information for the first field comes from the items picked in a List Box (JCList) and the information for the second field is from a Text Field (SkillNumber). When I run the following code, I get an "Item Not Found In This Collection" error.
Private Sub btn_SaveAndCont_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
On Error GoTo ErrorHandler
Set db = CurrentDb()
Set rs = db.OpenRecordset("tbl_Assigned Job Class Skills", dbOpenDynaset, dbAppendOnly)
'add selected value(s) to table
Set ctl = Me.JCList
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs![AssignedJobClass] = ctl.ItemData(varItem)
rs![AssignedSkillNumber] = Me.SkillNumber.Value
rs.Update
Next varItem
ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
Select Case Err
Case Else
MsgBox Err.Description
DoCmd.Hourglass False
Resume ExitHandler
End Select
End Sub
The problem line is "rs![AssignedSkillNumber] = Me.SkillNumber.Value
When I cursor over "SkillNumber" on the above line, it shows me the value I typed in, but when I cursor over "AssignedSkillNumber" it just says, "<Item not found in this collection>". I am sure there is a simple explanation here, but I am a VBA noob so I don't even know where to start with this one.
Private Sub btn_SaveAndCont_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
On Error GoTo ErrorHandler
Set db = CurrentDb()
Set rs = db.OpenRecordset("tbl_Assigned Job Class Skills", dbOpenDynaset, dbAppendOnly)
'add selected value(s) to table
Set ctl = Me.JCList
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs![AssignedJobClass] = ctl.ItemData(varItem)
rs![AssignedSkillNumber] = Me.SkillNumber.Value
rs.Update
Next varItem
ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
Select Case Err
Case Else
MsgBox Err.Description
DoCmd.Hourglass False
Resume ExitHandler
End Select
End Sub
The problem line is "rs![AssignedSkillNumber] = Me.SkillNumber.Value
When I cursor over "SkillNumber" on the above line, it shows me the value I typed in, but when I cursor over "AssignedSkillNumber" it just says, "<Item not found in this collection>". I am sure there is a simple explanation here, but I am a VBA noob so I don't even know where to start with this one.