On Not In List Error "Type Mismatch"

[jbento]

Can someone please help me with the problem below?

I used the following Not In List Example, and I get a "type mismatch" error. I am using an Access 2000 database. My table that holds the values is called "Project" and I also created a query from this table, just to do sorting of the values, and it is called "qry_Project". Here is the code I am using, and I'll point out where the code is highlighting the error:

Private Sub ProjectName_NotInList(NewData As String, response As Integer)
Dim db As Database, rs As Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available Value List"
strMsg = strMsg & "@Do you want to add the new Value?"
strMsg = strMsg & "@Click Yes to add or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?&quot = vbNo Then
response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("qry_Project", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Project = NewData
rs.Update

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

The line that is highlighting the error, is the following:
Set rs = db.OpenRecordset("qry_Project", dbOpenDynaset)

Can anyone please tell me why I am getting this error? Any help will be greatly appreciated. Thanks so much.

Jerome

 

[WingandaPrayer]

Whether this will help.

Set rs = db.OpenRecordset("qry_Project", dbOpenDynaset)

change to

Set rs = db.OpenRecordset("The Table Name", dbOpenDynaset)

HTH

David

 

[jbento]

David,
I tried that. I put the table name in the spot you specified, and I put the query in the other spot, but it still didn't work. It is very frustrating, because I can't figure this one out. EEEEEEEEW!!!! Do you or anyone else have any other suggestions? I would be very appreciative. Thanks.

Jerome

 

[Aivars]

Hi, Jerome!

What's field's Project type?
NewData is string what you entered in combobox. In addition you may add new value into field with primary key, too.

Aivars

 

[jbento]

Aivars,
rs!Project = NewData. The "Project" is the main table that holds the Values. The "qry_Project" is the query I created from the "Project" table. This query is only used to protect my sorting order of the values. I hope this is enough info for you.

Jerome

 

[Aivars]

You took obvious mistake.
You tried to add record to... nothing.
Recordset contains Data. In your case recordset rs include all data of query qry_Project. Accordingly you can add data only into table(s) fields which are include in the query qry_Project, e.g.

rs.addnew
rs!FieldWithPrimaryKey = (some true value)
rs!TextField = NewData 'String of combobox
......
rs.update

Aivars

 

[jbento]

All,
I found out what the problem was with the code. You have to put DAO.Recordset as a reference, and make sure that the DAO 3.6 is chosen in the references area of your database. The code works fine after I did this.

The second line of code should read like this:
Dim db As Database, rs As DAO.Recordset

Thanks.

 

[downwitchyobadself]

You should set DAO.Database, too--it's a DAO object. Should read:

Dim db As DAO.Database, rs As DAO.Recordset

But I don't think it will mismatch for dbs...