Re: Combo Box Selection Code on NOT IN LIST

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Dirk Goldgar (dg_at_NOdataSPAMgnostics.com)
Date: 06/14/04 

Date: Mon, 14 Jun 2004 14:10:13 -0400

"Bob" <drbobb1@hotmail.com> wrote in message
news:1bfb901c45237$702cf550$a601280a@phx.gbl
> 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. 

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)

Relevant Pages

  • Requery of Listbox does not display new data
    ... add a record to the database. ... Then the Lisbox control's requery method is ... The ADO command is run using a connection string to the mdb containing the ... Dim cmd As ADODB.Command ...
    (microsoft.public.access.formscoding)
  • Re: Using ADO and Late Binding
    ... but it is possible to use ADO with early binding. ... Note that the .xla is saved with a low ADO version reference. ... Dim ADOConn As Object ... Dim strADOFile As String ...
    (microsoft.public.excel.programming)
  • Re: mailmerge and sql
    ... means that you will not be able to see them in a database you open using the ... I believe you may have to use DAO instead of ADO to ... then creates a View containing a UNION query. ... Dim oCatalog As ADOX.Catalog ...
    (microsoft.public.word.mailmerge.fields)
  • Re: My switchboards wont work with WinXP !
    ... The Switchboard code you posted is, without a doubt, ADO code. ... order for that code to run properly as is a Reference must be set to ADO. ... the Switchboard form itself than the solution would be as easy as this: ... the database and you are up and running. ...
    (microsoft.public.access.forms)
  • Re: mailmerge and sql
    ... that is essentially a database application with a document ... the recordset to the Word Template and use it as if I got ... >> using an ADO recordset as a datasource (if it could be ... >Dim oCatalog As ADOX.Catalog ...
    (microsoft.public.word.mailmerge.fields)