Error 3709 in VBA/macros

A runtime error of 3709 keeps popping up when I run this part of the program. Does anyone know what I can do to fix it?

Public cn As New ADODB.Connection

Public rs As New ADODB.Recordset

Public CustomerID As Integer

Public CustFirstName As String

Public CustLastName As Stri开发者_开发知识库ng


Sub GetCustomerList()

    Dim strSQL As String
    Dim Customers As Variant

    ' Import customer info and use it to populate the list box.
    ' After frmcustomers is unloaded, we will know the CustomerID
    ' and Customer Name of the selected order.

    strSQL = "SELECT CustomerID, CustFirstName, CustLastName FROM Customers"
    rs.Open strSQL, cn

    frmCustomers.Show

    rs.Close
End Sub

Thanks,

JM

I got the same error code when trying to edit one specific record. It was a regular record, with no data or validation inconsistency.

Apparently this was a random data corruption issue, the 1st I believe to have gotten after some years working with MS Access (just for statistical reference, not a pro or con statement!)

Problem solved by running Compact And Repair routine.

Solved, I got this error "3709 the search key was not found in any record", while using this code :

DoCmd.CopyObject , "Data_OldCurrentMemberDetails", acTable, "Data_CurrentMemberDetails"

Leter what I leant is it was an issue with the name of my file which contained "[, ]" (square brackets).

To avoid these kind of errors keep your file names in plain text i.e Do not include "[, ], %, ..."

From the error you are reporting, it would seem that at the point you call GetCustomerList() the connection is not open.

In a database, when you create a Memo field that includes an index or when it creates a field name that contains the text automatically indexed, it can not write to the text field that includes more than 3450 characters or so. When you try to write more text in the field or modify existing data, receive an error message similar to the following Exception 3709

http://support.microsoft.com/kb/302525

Just for completeness, I get error 3709 (the search key was not found in any record) when trying to import an Excel spreadsheet that has a leading or trailing space in a column header. Remove that and it goes away. This is in Access 2013.