On Not in List

[R]

I have a combo box that runs off of the on not in list event procedure and
with a module..

My module is this

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 Recordset
Dim sMsg As String
Dim vField As Variant ' Name of the field to append to.

Append2Table = acDataErrContinue
vField = cbo.RowSource
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

My Event procedure is this:

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

Now when i type a new value into the combo box it asks me if i would like to
add the entry into my combo box..I hit yes and then i get an error that says
Append2Table()
Error 13: Type MisMatch

Any suggestions?? Thanks!

 

[Allen Browne]

Type Mismatch means either that the object is not of the expected type, or
the data is not of the correct type.

Try replacing:
Dim rst As Recordset
with:
Dim rst As DAO.Recordset
to disambiguate between the DAO and ADO recordset objects. If it now throws
an "unknown type" error, choose References from the Tools menu, and check
the box beside:
Microsoft DAO 3.6 Library
More on references:
http://members.iinet.net.au/~allenbrowne/ser-38.html

The other possibility is that the NewData is text, whereas the field you are
trying to assign it to is Number (or the reverse). This typically happens if
the bound column of the combo is zero-width, so that what you are typing the
in box is not actually the value that is being stored. If that is the case,
the NotInList event is not going to be useful.