"Item Not Found In This Collection" error when trying to create records using List Box and 1

[jkestes]

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.

 

[Andrzejek]

In your [tt]tbl_Assigned Job Class Skills[/tt] table, do you have the two fields named:[tt]
AssignedJobClass
AssignedSkillNumber[/tt]

And if so, shouldn't this line:
[tt]Set rs = db.OpenRecordset("tbl_Assigned Job Class Skills", dbOpenDynaset, dbAppendOnly)[/tt]
be
[tt]Set rs = db.OpenRecordset("[blue]Select * From [/blue][tbl_Assigned Job Class Skills]", dbOpenDynaset, dbAppendOnly)[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[jkestes]

Hi Andy,

You are correct about the names of the fields in the table. I should have mentioned that before.

I corrected the line to the correct syntax as you suggested, but it didn't resolve the issue. Still getting the same error at the same line of code.

Yes - there is a great need for a sarcasm font. LOL

 

[jkestes]

Not sure if this helps, but the full error that I am getting is, "Run-time error '3265': Item not found in this collection." Not sure if that additional info will help much.

 

[Andrzejek]

If both fields in your table are numbers, I would try this instead:

For Each varItem In ctl.ItemsSelected
    strSQL = "INSERT INTO [tbl_Assigned Job Class Skills] " _
        " (AssignedJobClass, AssignedSkillNumber) " _
        " VALUES (" & ctl.ItemData(varItem) & ", " & Me.SkillNumber.Value & ")"

    [blue]Debug.Print strSQL[/blue]

    db.Execute strSQL, dbFailOnError 
Next varItem

I would step thru your code and make sure the [tt]Debug[/tt] line gives you a valie UPDATE statement to be executed.


---- Andy

There is a great need for a sarcasm font.

 

[jkestes]

AssignedJobClass is Short Text
AssignedSkillNum is a Number

 

[Andrzejek]

Then add the single quotes around the value for AssignedJobClass:

For Each varItem In ctl.ItemsSelected
    strSQL = "INSERT INTO [tbl_Assigned Job Class Skills] " _
        " (AssignedJobClass, AssignedSkillNumber) " _
        " VALUES ([highlight #FCE94F]'[/highlight]" & ctl.ItemData(varItem) & "[highlight #FCE94F]'[/highlight], " & Me.SkillNumber.Value & ")"

    [blue]Debug.Print strSQL[/blue]

    db.Execute strSQL, dbFailOnError 
Next varItem

---- Andy

There is a great need for a sarcasm font.

 

[jkestes]

Now I get a "Compile error: Syntax error" error and these three lines of code are in red. I can't figure out what is wrong with the syntax of your code. It looks fine to me.

strSQL = "INSERT INTO [tbl_Assigned Job Class Skills] " _
" (AssignedJobClass, AssignedSkillNumber) " _
" VALUES ('" & ctl.ItemData(varItem) & "', " & Me.SkillNumber.Value & ")

 

[Andrzejek]

Some & are missing (sorry about it):

strSQL = "INSERT INTO [tbl_Assigned Job Class Skills] " [highlight #FCE94F]&[/highlight] _
    " (AssignedJobClass, AssignedSkillNumber) " [highlight #FCE94F]&[/highlight] _
    " VALUES ('" & ctl.ItemData(varItem) & "', " & Me.SkillNumber.Value & ")"

Or you can try this version:

strSQL = "INSERT INTO [tbl_Assigned Job Class Skills] " & vbNewLine _
& " (AssignedJobClass, AssignedSkillNumber) " & vbNewLine _
& " VALUES('" & ctl.ItemData(varItem) & "', " & Me.SkillNumber.Value & ")"

---- Andy

There is a great need for a sarcasm font.

 

[jkestes]

Ok - Now we have a whole new error. Maybe that is progress on some level. The error I get now is...

Run-time error '3127': The INSERT INTO statement contains the following unknown field name: 'AssignedSkillNumber'. Make sure you have typed the name correctly, and try the operation again.

Here is my code (with our revisions) just so you can see the full picture again.

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("Select * From [tbl_Assigned Job Class Skills]", dbOpenDynaset, dbAppendOnly)

'add selected value(s) to table
Set ctl = Me.JCList

For Each varItem In ctl.ItemsSelected
strSQL = "INSERT INTO [tbl_Assigned Job Class Skills] " & vbNewLine _
& " (AssignedJobClass, AssignedSkillNumber) " & vbNewLine _
& " VALUES('" & ctl.ItemData(varItem) & "', " & Me.SkillNumber.Value & ")"

Debug.Print strSQL

db.Execute strSQL, dbFailOnError
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

BTW - Thanks for your patience.

 

[Andrzejek]

Well, looks to me that 'AssignedSkillNumber' is not the name of your fieled in your table.

You may try something like this to see the names of the fields in your table:

Set Db = CurrentDb()
Set rs = Db.OpenRecordset("Select * From [tbl_Assigned Job Class Skills]", dbOpenDynaset, dbAppendOnly)
[blue]
Dim i As Integer

For i = 1 To rs.Fields.Count
    MsgBox "*" & rs.Field(i).Name & "*"
Next I[/blue]
...

Just watch for leading and/or trailing spaces in the names...

Also, where you set the color of the text in your post - use CODE icon to format your code as, well - code. Use Preview before hitting Submit.


---- Andy

There is a great need for a sarcasm font.

 

[jkestes]

Thanks for the tip about the CODE icon. This is actually my first posting on here, so I was wondering why your code looked nice and formatted and mine looked like plain text.

Also, it looks like this problem was the result of my own stupidity. Recently I changed the name of the field from "Assigned Skill Number" to "AssignedSkillNum" and didn't correct it completely in my code. *headdesk*

You are a saint for putting up with me on this. Is there some kind of reward/feedback system on this forum that I can do for you?

 

[Andrzejek]

Sometimes, as you can see, the 'problem' is (seating) between the chair and the keyboard
So your original code could be OK now
You don't have to do it now (for me), but to show appreciation for the help, and to let others know that the issue has been solved, you can click on "Great Post!" link in helpful post. That awards the star to the post/member of TT.

Welcome to Tek-Tips


---- Andy

There is a great need for a sarcasm font.