Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    15

    Unhappy Unanswered: help with type mismatch error in access 2000

    I am trying to copy the data in a field from one table to the same field in another table (mirror that field). I have found a function in a boook that is very similiar to what I want to do but when I try to execute it I get a type mismatch error and it highlights the first set command.

    I have looked through several other books and I can seem to find the problem. Can anyone give a hint of the problem?

    This is the function... it's very basic.
    When the field UIC in the EDVR form loses focus it should copy the value in the UIC filed in table EDVR to the UIC field in the PERS table.

    Private Sub UIC_LostFocus()
    On Error GoTo Err_UIC_LostFocus

    Dim edvr As Recordset
    Dim pers As Recordset

    Set edvr = CurrentDb.OpenRecordset("SELECT * FROM edvr WHERE SSN = " & Me.SSN, dbOpenDynaset)
    Set pers = CurrentDb.OpenRecordset("SELECT * FROM pers WHERE SSN = " & Me.SSN, dbOpenDynaset)

    pers!UIC = edvr!UIC

    Exit_UIC_LostFocus:
    Exit Sub

    Err_UIC_LostFocus:
    MsgBox Err.Description
    Resume Exit_UIC_LostFocus
    End Sub

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    currentdb is DAO
    a2000 defaults to ADO unless you do something to prevent it.

    either switch all your code over to ADO

    or

    force your code into DAO.
    - reference the DAO library (code window, tools¦references)
    dim evdr as DAO.recordset
    dim dabs as DAO.database
    set dabs = currentdb
    set evdr = dabs.openrecordset(bah blah
    etc etc

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Oct 2003
    Posts
    15
    Originally posted by izyrider

    force your code into DAO.
    - reference the DAO library (code window, tools¦references)
    dim evdr as DAO.recordset
    dim dabs as DAO.database
    set dabs = currentdb
    set evdr = dabs.openrecordset(bah blah
    etc etc

    izy
    izy,
    Thanks for the reply.

    I went with the second option. Here's the changes:

    Dim db As DAO.Database
    Dim EDVR As DAO.Recordset
    Dim pers As DAO.Recordset

    Set db = CurrentDb
    Set EDVR = db.OpenRecordset("SELECT * FROM edvr WHERE SSN = " & Me.SSN, dbOpenDynaset)
    Set pers = db.OpenRecordset("SELECT * FROM pers WHERE SSN = " & Me.SSN, dbOpenDynaset)

    pers!UIC = EDVR!UIC

    I get the same error... Just to be sure, what references should I have selected? I have the DAO 3.6 object library, VBA, Access 9.0, VBA extensibility 5.3, and OLE automation. Am I missing something there?

  4. #4
    Join Date
    Oct 2003
    Posts
    15
    if I change this:

    Set EDVR = db.OpenRecordset("SELECT * FROM edvr WHERE SSN = " & Me.SSN, dbOpenDynaset)

    to

    Set EDVR = db.OpenRecordset("edvr")

    The error for that line goes away. Is there a problem in my SQL statement? I can find one.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •