We help IT Professionals succeed at work.

Run-Time Error '3709'

Todd West
Todd West asked
on
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"  
    Me.cboPgmUser.SetFocus  
   
ElseIf IsNull(Me.txtAccessKey) Then  
    MsgBox "You must enter the Access Key for the Program Office selected.", vbInformation, "Access Key Required"  
    Me.txtAccessKey.SetFocus  
   
' Test for Permissions
    Else
        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")
           
              Else
                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
Comment
Watch Question

John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:

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 : https://access-programmers.co.uk/forums/showthread.php?t=297504


Todd WestBudget Analyst

Author

Commented:
Thank you, I will give it a try and update the results.
Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
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.

Jim.
Distinguished Expert 2017
Commented:
Remove the append only field and add a proper table to hold the comments.  The append only field is an ACE only abomination and can never be upsized.  Best to do this using proven methods rather than a hack.  You can add code to the comment sub FORM's BeforeUpdate event to prevent changes to existing records:
If Me.NewRecord Then
Else
    Msgbox "Existing comments may not be changed.", vbOKOnly
    Cancel = True
    Me.Undo
    Exit Sub
End If

Open in new window


Add a subform to your main form to hold the comments.  You can sort the comments by date ascending or descending depending on which order will make most sense to your users.  The comments table should be:

CommentID
FKtoexistingtable
CommentDT
UserID
Comment