error 3265: Item not found in this collection

[The Mecca]

I have searched and read up on the different posts about the same error- but
nothing helps- what am I doing wrong?

I have a combo box- limited to list. In my notinlist [event procedure] I got
from another source/website the following codes:

Private Sub Notes_NotInList(NewData As String, Response As Integer)
Response = append2table(Me![Notes], NewData)
Response = DATA_ERRCONTINUE
End Sub

as a module i have saved-

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

 

[Graham Mandeno]

This function (Append2Table) will work only in certain special
circumstances. Generally a combo box is used in a situation where you have
a one-to-many relationship between two tables. The combo box may be bound
to the related field on the "many" side, and the bound column of its
RowSource will list the possible values from the "one" side.

However, often, the related fields in the two tables have different names,
and it is common practice for the displayed column of the combo box not to
be the bound column (for example, the bound column might be "CustomerID",
while the displayed column is "CustomerName").

For this function to work, several criteria must be met:

1) The combo box must be bound to the related field on the "many" side
(often combo boxes are unbound - for example, when used to select records)

2) The two related fields must have the same name (it assumes that the field
in the "one-side" table has the same name as cbo.ControlSource)

3) The bound column must be the visible one (otherwise you will be trying to
assign a CustomerName to the CustomerID field, ot similar)

4) The RowSource of the combo box must be an updateable query

I suggest that you explicitly pass extra arguments containing:
a) the name of the table to append to
b) the name of the field to fill

A combo box itself does does not need to be passed.

So, here is the amended function:

Function Append2Table(sTable as string, sField as string, NewData As string)
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

append2table = acDataErrContinue
sMsg = "Do you wish to add the entry " & NewData _
& " for " & sField & "?"
If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
Set rst = CurrentDb.OpenRecordset(sTable)
rst.AddNew
rst(sField) = NewData
rst.Update
rst.Close
append2table = acDataErrAdded
End If

Exit_Append2Table:
Set rst = Nothing
Exit Function

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


Call it like this:

Private Sub Notes_NotInList(NewData As String, Response As Integer)
Response = append2table("Name of table", "name of field", NewData)
' Response = DATA_ERRCONTINUE <<< lose this line
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have searched and read up on the different posts about the same error-
but
nothing helps- what am I doing wrong?

I have a combo box- limited to list. In my notinlist [event procedure] I
got
from another source/website the following codes:

Private Sub Notes_NotInList(NewData As String, Response As Integer)
Response = append2table(Me![Notes], NewData)
Response = DATA_ERRCONTINUE
End Sub

as a module i have saved-

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