adding records using the not-in-list event

[ion97]

I' ve used the following code in order to add records to a table using the not-in-list event:

Dim Db As Database
Dim Rs As Recordset

Set Db = CurrentDb
Set Rs = Db.OpenRecordset("tblËÅÎÅÉÓ", dbOpenDynaset)
On Error Resume Next
Rs.AddNew
Rs![ËÅÎÇ] = NewData
Rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

Everything used to work fine at the begining. However (although I can't associate it with a specific change or addition), now when I add a not-in-list-word, I get an error message: "Type missmatch"
Does anyone know what is going wrong?

 

[DBL]

Here's the code I use to add a county to the base table when it's not in a combo box. You could compare it to see if they are any differences. Each county has an ID field and the county name field and both fields are included in the combo box but the id field is hidden.

Dim db As Database
Dim rs As DAO.Recordset
On Error GoTo HandleErr
Set db = CurrentDb
Dim Msg As String
Dim Result

If NewData = "" Then Exit Sub

Msg = "The county """ & NewData & """ is not on the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?" & vbCr & vbCr & "PLEASE CHECK THE SPELLING BEFORE PROCEEDING!"
If MsgBox(Msg, vbInformation + vbYesNo + vbDefaultButton1, "Add Area?") = vbYes Then

Set rs = db.OpenRecordset("tblCountyLookup", dbOpenDynaset)
rs.AddNew
rs!County = NewData
rs.Update
rs.Close
Set db = Nothing
Set rs = Nothing

End If

Result = DLookup("[County]", "tblCountyLookup", "[County]='" & NewData & "'")
If IsNull(Result) Then
Response = acDataErrContinue
MsgBox "Please select a county from the available list!"
Else
Response = acDataErrAdded
End If

ExitHere:
Exit Sub


Make sure that you have the Microsoft 3.6 DAO Reference library ticked or the code won't compile. You can find it in the Module window > Tools > References.

[This message has been edited by DBL (edited 05-04-2002).]

 

[bbeeching]

This is what I use, and it works great.

Private Sub cboCriteria_NotInList(NewData As String, Response As Integer)
On Error GoTo err_cboCriteria_NotInList

Dim ctl As Control
Dim strSQL As String

' Return Control object that points to combo box.
Set ctl = Me!cboCriteria
' Prompt user to verify they wish to add new value.
If MsgBox("Criteria is not in list. Add it?", vbOKCancel) = vbOK Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
' Add string in NewData argument to criteria table.
NewData = CapitalizeFirst(NewData)
strSQL = " INSERT INTO tblCriteria ( CriteriaDescription ) SELECT '" & Proper(NewData) & "'"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
ctl.Value = NewData
DoCmd.SetWarnings True
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
ctl.Undo
End If

exit_cboCriteria_NotInList:
Exit Sub

err_cboCriteria_NotInList:
If Err = 2113 Then
Err = 0
Resume Next
Else
MsgBox Str(Err)
MsgBox Err.Description
Resume exit_cboCriteria_NotInList
End If
End Sub

 

[ion97]

Ok, now it works.
All I did was changed dim rs As recordset to:
dim rs as DAO.recordset
Does anyone know why this happend and how come it worked previously?
Could this cause any more problems in the future?

 

[Jack Cowley]

If you are using Access2000 it uses ADO code and you are using DAO code. You will need to declare DAO code in the future or use ADO code. If you use DAO be sure that you have your references set properly, which you must or you would have gotten anohter error....