Error 3265: Item not found in this collection

B

Bryan

Hopefully this will be a simple bit of ignorance on my part. I am using the
following code in a general Module:

Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
On Error GoTo Err_Append2Table
' Purpose: Append NotInList value to combo's recordset.
' Assumes: ControlSource of combo has the same name as the foreign key
field.
' Return: acDataErrAdded if added, else acDataErrContinue
' Usage: Add this line to the combo's NotInList event procedure:
' Response = Append2Table(Me.MyCombo, NewData)
Dim rst As DAO.Recordset
Dim sMsg As String
Dim vField As Variant ' Name of the field to append to.

Append2Table = acDataErrContinue
vField = cbo.ControlSource
If Not (IsNull(vField) Or IsNull(NewData)) Then
sMsg = "Do you wish to add the entry " & NewData & " for " &
cbo.Name & "?"
If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
rst.AddNew
rst(vField) = NewData
rst.Update
rst.Close
Append2Table = acDataErrAdded
End If
End If

Exit_Append2Table:
Set rst = Nothing
Exit Function

Err_Append2Table:
MsgBox "Error " & err.Number & ": " & err.Description, vbInformation,
"Append2Table()"
Resume Exit_Append2Table
End Function


Then in my form's events I am using the following:

Private Sub AssignedCAD_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![AssignedCAD], NewData)
End Sub

Private Sub Originator1_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![Originator1], NewData)
End Sub

Private Sub Originator_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![Originator], NewData)
End Sub

Private Sub TaskDescrShort1_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me!TaskDescrShort1, NewData)
End Sub

Private Sub TaskDescrShort2_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![TaskDescrShort2], NewData)
End Sub

Private Sub TaskDescrShort3_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![TaskDescrShort3], NewData)
End Sub

Private Sub TaskDescrShort4_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![TaskDescrShort4], NewData)
End Sub


The "Originator" and "AssignedCAD" work perfectly, but the 4 others
generate the error in my subject. All 4 use the same lookup table, which I
am sure is my problem, but I cannot see a reason to use 4 different lookup
tables all with the same data. The issue is that they can have up to four
different short task descriptions which I would like to be searchable by.
Any ideas on a solution here? TIA
 
R

Roger Carlson

It usually means that the field name you're referencing in your recordset
doesn't exist or is spelled incorrectly. Are you sure you're sending in the
right field/control name?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Bryan said:
Hopefully this will be a simple bit of ignorance on my part. I am using
the
following code in a general Module:

Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
On Error GoTo Err_Append2Table
' Purpose: Append NotInList value to combo's recordset.
' Assumes: ControlSource of combo has the same name as the foreign key
field.
' Return: acDataErrAdded if added, else acDataErrContinue
' Usage: Add this line to the combo's NotInList event procedure:
' Response = Append2Table(Me.MyCombo, NewData)
Dim rst As DAO.Recordset
Dim sMsg As String
Dim vField As Variant ' Name of the field to append to.

Append2Table = acDataErrContinue
vField = cbo.ControlSource
If Not (IsNull(vField) Or IsNull(NewData)) Then
sMsg = "Do you wish to add the entry " & NewData & " for " &
cbo.Name & "?"
If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK
Then
Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
rst.AddNew
rst(vField) = NewData
rst.Update
rst.Close
Append2Table = acDataErrAdded
End If
End If

Exit_Append2Table:
Set rst = Nothing
Exit Function

Err_Append2Table:
MsgBox "Error " & err.Number & ": " & err.Description, vbInformation,
"Append2Table()"
Resume Exit_Append2Table
End Function


Then in my form's events I am using the following:

Private Sub AssignedCAD_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![AssignedCAD], NewData)
End Sub

Private Sub Originator1_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![Originator1], NewData)
End Sub

Private Sub Originator_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![Originator], NewData)
End Sub

Private Sub TaskDescrShort1_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me!TaskDescrShort1, NewData)
End Sub

Private Sub TaskDescrShort2_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me![TaskDescrShort2], NewData)
End Sub

Private Sub TaskDescrShort3_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me![TaskDescrShort3], NewData)
End Sub

Private Sub TaskDescrShort4_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me![TaskDescrShort4], NewData)
End Sub


The "Originator" and "AssignedCAD" work perfectly, but the 4 others
generate the error in my subject. All 4 use the same lookup table, which
I
am sure is my problem, but I cannot see a reason to use 4 different lookup
tables all with the same data. The issue is that they can have up to four
different short task descriptions which I would like to be searchable by.
Any ideas on a solution here? TIA
Click to expand...
 
B

Bryan

Hey Roger,

Yes, they are correct. I should have noted that when I tried to run
"Originator1", (which I left in my above code,) it gave me the same message.
Originator had worked fine, I then added an "Originator1" as a field on my
form and in my table to see if it was because I had more than 1 item
referencing the same lookup table. I've checked the names thoroughly and
they seem fine. I am however concerned with the multiple references to the
same lookup table. No sure why it would be a problem, though, as it is
updating the table on the fly. The row source is "LookupTaskDescrShort"

Roger Carlson said:
It usually means that the field name you're referencing in your recordset
doesn't exist or is spelled incorrectly. Are you sure you're sending in the
right field/control name?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Bryan said:
Hopefully this will be a simple bit of ignorance on my part. I am using
the
following code in a general Module:

Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
On Error GoTo Err_Append2Table
' Purpose: Append NotInList value to combo's recordset.
' Assumes: ControlSource of combo has the same name as the foreign key
field.
' Return: acDataErrAdded if added, else acDataErrContinue
' Usage: Add this line to the combo's NotInList event procedure:
' Response = Append2Table(Me.MyCombo, NewData)
Dim rst As DAO.Recordset
Dim sMsg As String
Dim vField As Variant ' Name of the field to append to.

Append2Table = acDataErrContinue
vField = cbo.ControlSource
If Not (IsNull(vField) Or IsNull(NewData)) Then
sMsg = "Do you wish to add the entry " & NewData & " for " &
cbo.Name & "?"
If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK
Then
Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
rst.AddNew
rst(vField) = NewData
rst.Update
rst.Close
Append2Table = acDataErrAdded
End If
End If

Exit_Append2Table:
Set rst = Nothing
Exit Function

Err_Append2Table:
MsgBox "Error " & err.Number & ": " & err.Description, vbInformation,
"Append2Table()"
Resume Exit_Append2Table
End Function


Then in my form's events I am using the following:

Private Sub AssignedCAD_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![AssignedCAD], NewData)
End Sub

Private Sub Originator1_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![Originator1], NewData)
End Sub

Private Sub Originator_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![Originator], NewData)
End Sub

Private Sub TaskDescrShort1_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me!TaskDescrShort1, NewData)
End Sub

Private Sub TaskDescrShort2_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me![TaskDescrShort2], NewData)
End Sub

Private Sub TaskDescrShort3_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me![TaskDescrShort3], NewData)
End Sub

Private Sub TaskDescrShort4_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me![TaskDescrShort4], NewData)
End Sub


The "Originator" and "AssignedCAD" work perfectly, but the 4 others
generate the error in my subject. All 4 use the same lookup table, which
I
am sure is my problem, but I cannot see a reason to use 4 different lookup
tables all with the same data. The issue is that they can have up to four
different short task descriptions which I would like to be searchable by.
Any ideas on a solution here? TIA
Click to expand...
Click to expand...
 
R

Roger Carlson

Okay. This might not help, but it's worth a try. Create a database object
instead of relying on the persistance of the CurrentDb object:

Dim db as DAO.Database
....
set db = CurrentDb
Set rst = db.OpenRecordset(cbo.RowSource)

It's worth a shot.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Bryan said:
Hey Roger,

Yes, they are correct. I should have noted that when I tried to run
"Originator1", (which I left in my above code,) it gave me the same
message.
Originator had worked fine, I then added an "Originator1" as a field on my
form and in my table to see if it was because I had more than 1 item
referencing the same lookup table. I've checked the names thoroughly and
they seem fine. I am however concerned with the multiple references to
the
same lookup table. No sure why it would be a problem, though, as it is
updating the table on the fly. The row source is "LookupTaskDescrShort"

Roger Carlson said:
It usually means that the field name you're referencing in your recordset
doesn't exist or is spelled incorrectly. Are you sure you're sending in
the
right field/control name?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Bryan said:
Hopefully this will be a simple bit of ignorance on my part. I am
using
the
following code in a general Module:

Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
On Error GoTo Err_Append2Table
' Purpose: Append NotInList value to combo's recordset.
' Assumes: ControlSource of combo has the same name as the foreign
key
field.
' Return: acDataErrAdded if added, else acDataErrContinue
' Usage: Add this line to the combo's NotInList event procedure:
' Response = Append2Table(Me.MyCombo, NewData)
Dim rst As DAO.Recordset
Dim sMsg As String
Dim vField As Variant ' Name of the field to append to.

Append2Table = acDataErrContinue
vField = cbo.ControlSource
If Not (IsNull(vField) Or IsNull(NewData)) Then
sMsg = "Do you wish to add the entry " & NewData & " for " &
cbo.Name & "?"
If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") =
vbOK
Then
Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
rst.AddNew
rst(vField) = NewData
rst.Update
rst.Close
Append2Table = acDataErrAdded
End If
End If

Exit_Append2Table:
Set rst = Nothing
Exit Function

Err_Append2Table:
MsgBox "Error " & err.Number & ": " & err.Description,
vbInformation,
"Append2Table()"
Resume Exit_Append2Table
End Function


Then in my form's events I am using the following:

Private Sub AssignedCAD_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me![AssignedCAD], NewData)
End Sub

Private Sub Originator1_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me![Originator1], NewData)
End Sub

Private Sub Originator_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me![Originator], NewData)
End Sub

Private Sub TaskDescrShort1_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me!TaskDescrShort1, NewData)
End Sub

Private Sub TaskDescrShort2_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me![TaskDescrShort2], NewData)
End Sub

Private Sub TaskDescrShort3_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me![TaskDescrShort3], NewData)
End Sub

Private Sub TaskDescrShort4_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me![TaskDescrShort4], NewData)
End Sub


The "Originator" and "AssignedCAD" work perfectly, but the 4 others
generate the error in my subject. All 4 use the same lookup table,
which
I
am sure is my problem, but I cannot see a reason to use 4 different
lookup
tables all with the same data. The issue is that they can have up to
four
different short task descriptions which I would like to be searchable
by.
Any ideas on a solution here? TIA
Click to expand...
Click to expand...
Click to expand...
 
B

Bryan

Roger,
I found the following in another post that worked great!

http://www.databasedev.co.uk/not_in_list.html

Thanks for your input!

Roger Carlson said:
Okay. This might not help, but it's worth a try. Create a database object
instead of relying on the persistance of the CurrentDb object:

Dim db as DAO.Database
....
set db = CurrentDb
Set rst = db.OpenRecordset(cbo.RowSource)

It's worth a shot.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Bryan said:
Hey Roger,

Yes, they are correct. I should have noted that when I tried to run
"Originator1", (which I left in my above code,) it gave me the same
message.
Originator had worked fine, I then added an "Originator1" as a field on my
form and in my table to see if it was because I had more than 1 item
referencing the same lookup table. I've checked the names thoroughly and
they seem fine. I am however concerned with the multiple references to
the
same lookup table. No sure why it would be a problem, though, as it is
updating the table on the fly. The row source is "LookupTaskDescrShort"

Roger Carlson said:
It usually means that the field name you're referencing in your recordset
doesn't exist or is spelled incorrectly. Are you sure you're sending in
the
right field/control name?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hopefully this will be a simple bit of ignorance on my part. I am
using
the
following code in a general Module:

Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
On Error GoTo Err_Append2Table
' Purpose: Append NotInList value to combo's recordset.
' Assumes: ControlSource of combo has the same name as the foreign
key
field.
' Return: acDataErrAdded if added, else acDataErrContinue
' Usage: Add this line to the combo's NotInList event procedure:
' Response = Append2Table(Me.MyCombo, NewData)
Dim rst As DAO.Recordset
Dim sMsg As String
Dim vField As Variant ' Name of the field to append to.

Append2Table = acDataErrContinue
vField = cbo.ControlSource
If Not (IsNull(vField) Or IsNull(NewData)) Then
sMsg = "Do you wish to add the entry " & NewData & " for " &
cbo.Name & "?"
If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") =
vbOK
Then
Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
rst.AddNew
rst(vField) = NewData
rst.Update
rst.Close
Append2Table = acDataErrAdded
End If
End If

Exit_Append2Table:
Set rst = Nothing
Exit Function

Err_Append2Table:
MsgBox "Error " & err.Number & ": " & err.Description,
vbInformation,
"Append2Table()"
Resume Exit_Append2Table
End Function


Then in my form's events I am using the following:

Private Sub AssignedCAD_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me![AssignedCAD], NewData)
End Sub

Private Sub Originator1_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me![Originator1], NewData)
End Sub

Private Sub Originator_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me![Originator], NewData)
End Sub

Private Sub TaskDescrShort1_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me!TaskDescrShort1, NewData)
End Sub

Private Sub TaskDescrShort2_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me![TaskDescrShort2], NewData)
End Sub

Private Sub TaskDescrShort3_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me![TaskDescrShort3], NewData)
End Sub

Private Sub TaskDescrShort4_NotInList(NewData As String, Response As
Integer)
Response = Append2Table(Me![TaskDescrShort4], NewData)
End Sub


The "Originator" and "AssignedCAD" work perfectly, but the 4 others
generate the error in my subject. All 4 use the same lookup table,
which
I
am sure is my problem, but I cannot see a reason to use 4 different
lookup
tables all with the same data. The issue is that they can have up to
four
different short task descriptions which I would like to be searchable
by.
Any ideas on a solution here? TIA
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

error 3265: Item not found in this collection 1
On Not in List 1
updating field value threw a combobox MDB VS ADP 4
NotInList Firing Problem 12
item not found in this collection 1
Cbo Not In List code error 9
NotInList Issue 1
Not In List Problems 2

Top