NotinList problem

Hi Guys,
Using Allen Browne's 'not in table' method to add details to another table
and keep getting this error message:

error 3421: Data type conversion

I would assume from this message that the value is the wrong data type, but
it's not, the field is a text field?

If I click ok to the error message it puts a blank 'null value' record in
the relevant table.

Any ideas were I am going wrong?

TIA

Les

Without seeing the actual code you're using, it's pretty difficult for
anyone to comment.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"LJG" wrote in message
...
Hi Guys,
Using Allen Browne's 'not in table' method to add details to another table
and keep getting this error message:

error 3421: Data type conversion

I would assume from this message that the value is the wrong data type,
but it's not, the field is a text field?

If I click ok to the error message it puts a blank 'null value' record in
the relevant table.

Any ideas were I am going wrong?

TIA

Les

No problem, this is my code:

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 procedu
'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





"Douglas J. Steele" wrote in message
...
Without seeing the actual code you're using, it's pretty difficult for
anyone to comment.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"LJG" wrote in message
...
Hi Guys,
Using Allen Browne's 'not in table' method to add details to another
table and keep getting this error message:

error 3421: Data type conversion

I would assume from this message that the value is the wrong data type,
but it's not, the field is a text field?

If I click ok to the error message it puts a blank 'null value' record in
the relevant table.

Any ideas were I am going wrong?

TIA

Les

So vField is a text field? What value are you trying to store in it?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"LJG" wrote in message
...
No problem, this is my code:

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 procedu
'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





"Douglas J. Steele" wrote in message
...
Without seeing the actual code you're using, it's pretty difficult for
anyone to comment.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"LJG" wrote in message
...
Hi Guys,
Using Allen Browne's 'not in table' method to add details to another
table and keep getting this error message:

error 3421: Data type conversion

I would assume from this message that the value is the wrong data type,
but it's not, the field is a text field?

If I click ok to the error message it puts a blank 'null value' record
in the relevant table.

Any ideas were I am going wrong?

TIA

Les