Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Location
    Burnley, Lancashire, England
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    error with index after changing from ADO to DAO (2000)

    The following code doesn't work for 2 reasons-
    1. I can't use the .NoMatch without DAO Recordset, but when i add the reference and open recordset as DAO i get the error message

    Operation not supported by this type of object - for the .requery
    also if delete .reguery line i get error message

    runtime error '3800' id is not an index in this table - but it really is the primarykey and have tried referencing other tables in the datbase but get the exact same problem

    also with the following code, will it stop the record from being changed if a record already exists or do i need more code.

    also is the bookmark really needed and do i ahve to populate the recordset instead of using .requery, and if so how is this done correctly

    the code is as follows - it is for an after update event of a text box

    Dim varBookmark As Variant
    Dim strOurRef As String
    strOurRef = Our_ref.Value

    Dim rstTransmittal As Recordset
    Set rstTransmittal = CurrentDb.OpenRecordset("Transmittal_details")

    With rstTransmittal
    .Requery
    varBookmark = .Bookmark 'unsure if this is really needed
    .Index = "id"
    .Seek "=", strOurRef
    If Not .NoMatch Then
    MsgBox ("Reference already exists")
    End If
    End With

    Set rstTransmittal = Nothing

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error with index after changing from ADO to DAO (2000)

    If you want to be able to cancel the update, you must use the Before Update event instead of the After Update event.

    In this case, there is no need for .Requery or .Bookmark of the recordset. And try using .FindFirst instead of .Index and .Seek. Note the use of Chr(34) to surround the search value in quotes.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Private Sub Our_ref_BeforeUpdate(Cancel As Integer)
    Dim strOurRef As String
    Dim dbs As DAO.Database
    Dim rstTransmittal As DAO.Recordset

    strOurRef = Our_ref.Value
    Set dbs = CurrentDb
    Set rstTransmittal = dbs.OpenRecordset("Transmittal_details")

    With rstTransmittal
    ' Try to find record with matching ID
    .FindFirst "id=" & Chr(34) & strOurRef & Chr(34)
    If Not .NoMatch Then
    ' Notify user
    MsgBox "Reference already exists"
    ' Cancel the update
    Cancel = True
    End If
    .Close
    End With

    ' Clean up
    Set rstTransmittal = Nothing
    Set dbs = Nothing
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

  3. #3
    New Lounger
    Join Date
    Apr 2003
    Location
    Burnley, Lancashire, England
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error with index after changing from ADO to DAO (2000)

    i keep getting error message for the .FindFirst line of code

    run-time error '3251'

    operation not supported for this type of object

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error with index after changing from ADO to DAO (2000)

    What is Transmittal_Details?
    - A table in the current database (or a query based on a table in the current database)
    - A linked table whose source lives in an Access backend (or a query based on ...)
    - A linked table whose source lives in a SQL server or Oracle database (or a query based on ...)
    - Something else (if so, what?)

  5. #5
    New Lounger
    Join Date
    Apr 2003
    Location
    Burnley, Lancashire, England
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error with index after changing from ADO to DAO (2000)

    transmittal_details is a table in the database

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error with index after changing from ADO to DAO (2000)

    Thanks; try replacing

    Set rstTransmittal = dbs.OpenRecordset("Transmittal_details")

    by

    Set rstTransmittal = dbs.OpenRecordset("Transmittal_details", dbOpenDynaset)

  7. #7
    New Lounger
    Join Date
    Apr 2003
    Location
    Burnley, Lancashire, England
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error with index after changing from ADO to DAO (2000)

    sussed it, have moved code to before update event and all errors have gone,
    tested by copying all code to after update event and errors popped up again

    didn;'t to like the Cancel = true as well in the after update event

    Thanks people, great suggestions and good code, have learned now about "Cancel" and .Close - although unsure why use the .close and then "rstTranmittal = nothing"

  8. #8
    New Lounger
    Join Date
    Apr 2003
    Location
    Burnley, Lancashire, England
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error with index after changing from ADO to DAO (2000)

    dont understand it

    fuly went be to a backup copy to test coding in an older version, working in before update with no errors ( the origional code that i posted) but has probs in afterupdate

    does anyone know why, why are they so different that vba coding works in one and not the other

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error with index after changing from ADO to DAO (2000)

    By using OpenRecordset, you have created a connection to the table.
    By closing the recordset, you end this connection.
    By setting the recordset object to Nothing, you release the memory that was occupied by the object.
    It must be done in this order: you can't release the memory while the connection is still open.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error with index after changing from ADO to DAO (2000)

    You can't cancel the AfterUpdate event, so if you transplant code that works in BeforeUpdate to AfterUpdate, you'll get error messages.

    The BeforeUpdate event of a control is (mostly) meant to be able to check the value entered by the user, and to cancel the update if the value is not correct.
    The AfterUpdate event of a control is (mostly) meant to change the value of other controls on the basis of the value that has just been updated.

Posting Permissions

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