Link to home
Create AccountLog in
Avatar of Todd West
Todd WestFlag for United States of America

asked on

Run-Time Error '3709'

I have an access database that will be used for issue tracking within my organization.  I had added a crude security (Log-in) feature consisting of choosing a Program Office from a combo box and an Access-Key entered into a text box; it was not the most elegant code but was working fine.  Then I made some edits/changes to other features of the database, nothing that should have effected the code I have for logging in but started receiving a Run-Time Error 3709, The search key was not found in any record.  The change I made was to a long text field in one of my tables changing it to track comment history by making it an append only field.  I then went back to earlier draft versions of the database to double check that I had not accidentally changed something in the code only to find all previous versions were experiencing the same error.  

It is really important that I keep a running history of comments added to a record but it is also important that I limit program office access to only those issues that apply to them; I am using the log-in features to set a temp variable used later to filter records.  My code is below but I am not certain the problem is in the code.  

First I test for Null values; then I test for User credentials, this is where I am getting the error; finally I test for admin credentials which work perfectly.  I am a novice at best and have ran out of ideas.  Any assistance you can provide is greatly appreciated.

Private Sub cmdLogin_Click()
Dim varPOFilter As String
Dim tvarAdmin As String

If IsNull(Me.cboPgmUser) Then  
    MsgBox "You must select a Program Office from the dropdown list provided.", vbInformation, "Program Office Required"  
ElseIf IsNull(Me.txtAccessKey) Then  
    MsgBox "You must enter the Access Key for the Program Office selected.", vbInformation, "Access Key Required"  
' Test for Permissions
        If Me.cboPgmUser.Column(5) = "User" And Me.txtAccessKey = Me.cboPgmUser.Column(4) = True Then
                TempVars.Add "varPOFilter", cboPgmUser.Column(4)
                DoCmd.Close acForm, "LOGIN", acSaveNo
                DoCmd.OpenForm ("Switchboard User")      'THIS IS WHERE THE DEBUGGER HIGHLIGHTS FOR THE ERROR
        ElseIf Me.cboPgmUser.Column(5) = "Admin" And Me.txtAccessKey = Me.cboPgmUser.Column(4) = True Then
                  TempVars.Add "tvarAdmin", "Grant"
                  DoCmd.Close acForm, "LOGIN", acSaveNo
                  DoCmd.OpenForm ("Switchboard Admin")
                MsgBox "The Program Office and Access Key are not a match, please try again.", vbInformation, "Log In Failure"
                Me.txtAccessKey = ""
                Exit Sub
                  End If
         End If
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

With out more inside it would be hard to guess.

Check if the underlying table/query of Switchboard User has some kind of corruption.

Also a longshot...but i found a rather interesting case of this error ...when a field name starts with a space " "

Check the thread here :

Avatar of Todd West


Thank you, I will give it a try and update the results.
There are a few things that can cause that error message and typically it is a record that is corrupt.

 You'd need to delete the record involved and then do a compact and repair.

 Also watch out for any spaces in front of column names as John said, although that's typically when you are exporting something where that occurs.

Avatar of PatHartman
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account