Combo Box Selection Code on NOT IN LIST

[Bob]

Can someone tell explain why this works in one DB and not
in the new one I am trying to create.

Private Sub FIELDNAME_NotInList(NewData As String,
Response As Integer)
Dim strmsg As String
Dim rst As Recordset
Dim db As Database
Const MB_YESNO = 4
Const MB_Question = 32
Const IDNO = 7
strmsg = "'" & NewData & "' is not in list. "
strmsg = strmsg & "Would you like to ad it?"

If MsgBox(strmsg, MB_YESNO + MB_Question, "New
Question") = IDNO Then
Response = DATA_ERRDISPLAY
Else
Set db = DBEngine.Workspaces(0).Databases(0)
Set rst = db.OpenRecordset("TABLENAME") <<<<
IT ERRORS OUT HERE "type mismatch ERR0R 13"
rst.AddNew
rst("FIELDNAME") = NewData
rst.Update
Response = DATA_ERRADDED
rst.Close
End If
End Sub

If I change the TABLENAME to something that doesn't exist
the error message that comes up I can understand >> "
The Microsoft Jet database engine cannot find the input
table or query <name>. Make sure it exists and that its
name is spelled correctly. (Error 3078)"

 

[Dirk Goldgar]

Can someone tell explain why this works in one DB and not
in the new one I am trying to create.

Private Sub FIELDNAME_NotInList(NewData As String,
Response As Integer)
Dim strmsg As String
Dim rst As Recordset
Dim db As Database
Const MB_YESNO = 4
Const MB_Question = 32
Const IDNO = 7
strmsg = "'" & NewData & "' is not in list. "
strmsg = strmsg & "Would you like to ad it?"

If MsgBox(strmsg, MB_YESNO + MB_Question, "New
Question") = IDNO Then
Response = DATA_ERRDISPLAY
Else
Set db = DBEngine.Workspaces(0).Databases(0)
Set rst = db.OpenRecordset("TABLENAME") <<<<
IT ERRORS OUT HERE "type mismatch ERR0R 13"
rst.AddNew
rst("FIELDNAME") = NewData
rst.Update
Response = DATA_ERRADDED
rst.Close
End If
End Sub

If I change the TABLENAME to something that doesn't exist
the error message that comes up I can understand >> "
The Microsoft Jet database engine cannot find the input
table or query <name>. Make sure it exists and that its
name is spelled correctly. (Error 3078)"

I imagine it's because the database where it succeeds has a reference to
DAO and either doesn't have a reference to ADO or the DAO reference is
higher in the priority list, while the database where it fails either
has only a reference to ADO or (most likely) the ADO reference is higher
in the list of references. ADO and DAO both define a Recordset object,
and they aren't compatible. Safest is to disambiguate your declarations
of objects from these libraries, like this:

Dim rst As DAO.Recordset
Dim db As DAO.Database

Technically, you don't have to do it for the Database object, because
ADO has no Database object, but it's easier just to specify the library
for each declaration -- that way you don't have to keep the list of
common objects in your head.

 

[Roger Carlson]

If it is a new database, is it Access 2000 or higher and if so, do you have
a reference to DAO set?
See here:
http://members.iinet.net.au/~allenbrowne/ser-38.html

for further details.

 

[Guest]

-----Original Message-----


I imagine it's because the database where it succeeds has a reference to
DAO and either doesn't have a reference to ADO or the DAO reference is
higher in the priority list, while the database where it fails either
has only a reference to ADO or (most likely) the ADO reference is higher
in the list of references. ADO and DAO both define a Recordset object,
and they aren't compatible. Safest is to disambiguate your declarations
of objects from these libraries, like this:

Dim rst As DAO.Recordset
Dim db As DAO.Database

Technically, you don't have to do it for the Database object, because
ADO has no Database object, but it's easier just to specify the library
for each declaration -- that way you don't have to keep the list of
common objects in your head.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

YOU ARE THE MAN !!!
Thank you, I had chacked the references compared them and
varified that they both had the same libraries checked...
But Failed to consider the priority !!!

Thank you again... Is there a Global Module that I can
add to all DB's to ensure that they have the same list of
active refernces??? And a means of prioritizing them??

 

[Dirk Goldgar]

Thank you again... Is there a Global Module that I can
add to all DB's to ensure that they have the same list of
active refernces??? And a means of prioritizing them??

I believe it is possible to write such code by manipulating the
References collection, but I don't have any handy. See also Michael
Kaplan's article on how to gurantee that references will work in your
applications: http://www.trigeminal.com/usenet/usenet026.asp?1033 .

But the simplest thing to do is ensure that you disambiguate your
declarations of objects from the DAO and ADO libraries:

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim cn As ADODB.Connection
Dim rs2 As ADODB.Recordset

Here's a list of the objects that are (currently) defined in both DAO
and ADO:

Connection
Error
Errors
Field
Fields
Parameter
Parameters
Property
Properties
Recordset

Note: the following objects exist with the same names in the ADOX and
DAO models as well:

Group
Groups
Index
Indexes
Property
Properties
User
Users