troubleshooting Question

Run-Time Error '3709'

Avatar of Todd West
Todd WestFlag for United States of America asked on
Microsoft AccessVBA
4 Comments1 Solution180 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros