The search key was not found in any record ?!

[munkee]

Well I created my first try at form validation through using the before update event. However I am now having issues.

I am validating a subform on my main form. The validation code is held within the subform. I have 2 comboboxes and 2 text areas. The validation is messing up if I fill out 1 of any of these boxes and leave the rest blank.

I get the following error:

"The search key was not found in any record."

Here is a screen shot of the state:

Weirdly if I fill in the Cost figure field with a non zero value it doesnt happen, however it doesnt validate the department cost inccurred by field:

The code I am using is as follows:

Option Explicit
Option Compare Database
 
 
 
Private Sub costadd_Click()
On Error GoTo Err_btnaddactioncorrective_Click
 
 
    DoCmd.GoToRecord , , acNewRec
 
Exit_btnaddactioncorrective_Click:
    Exit Sub
 
Err_btnaddactioncorrective_Click:
    MsgBox Err.Description
    Resume Exit_btnaddactioncorrective_Click
End Sub
 
Private Sub costdelete_Click()
On Error GoTo Err_btndeleteacorrective_Click
 
 
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
 
Exit_btndeleteacorrective_Click:
    Exit Sub
 
Err_btndeleteacorrective_Click:
    MsgBox Err.Description
    Resume Exit_btndeleteacorrective_Click
End Sub
 
Private Sub costfirst_Click()
On Error GoTo Err_btncorrectivefirst_Click
 
 
    DoCmd.GoToRecord , , acFirst
 
Exit_btncorrectivefirst_Click:
    Exit Sub
 
Err_btncorrectivefirst_Click:
    MsgBox Err.Description
    Resume Exit_btncorrectivefirst_Click
 
End Sub
 
Private Sub costlast_Click()
On Error GoTo Err_btngotolastcorrective_Click
 
 
    DoCmd.GoToRecord , , acLast
 
Exit_btngotolastcorrective_Click:
    Exit Sub
 
Err_btngotolastcorrective_Click:
    MsgBox Err.Description
    Resume Exit_btngotolastcorrective_Click
End Sub
 
Private Sub costnext_Click()
On Error GoTo Err_btnnextcorrective_Click
 
 
    DoCmd.GoToRecord , , acNext
 
Exit_btnnextcorrective_Click:
    Exit Sub
 
Err_btnnextcorrective_Click:
    MsgBox Err.Description
    Resume Exit_btnnextcorrective_Click
End Sub
 
Private Sub costprevious_Click()
On Error GoTo Err_btnpreviouscorrective_Click
 
 
    DoCmd.GoToRecord , , acPrevious
 
Exit_btnpreviouscorrective_Click:
    Exit Sub
 
Err_btnpreviouscorrective_Click:
    MsgBox Err.Description
    Resume Exit_btnpreviouscorrective_Click
 
End Sub
 
Private Sub costsave_Click()
On Error GoTo Err_btnsavecorrective_Click
 
 
    DoCmd.RunCommand acCmdSaveRecord
 
 
 
Exit_btnsavecorrective_Click:
    Exit Sub
 
Err_btnsavecorrective_Click:
 
'capture the correct error number and just change it.
If Err = 2501 Then 'The command save record has been cancelled
MsgBox "Save cancelled.", vbInformation, "Info"
 
Else
    MsgBox Err.Description
    Resume Exit_btnsavecorrective_Click
End If
End Sub
 
Private Sub Form_AfterUpdate()
On Error GoTo helpme
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious
 
erm:
Exit Sub
 
helpme:
MsgBox Err.Description
Resume erm
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ErrorStrings As String
 
 
 
'The error string can be set to null for this first run
ErrorStrings = vbNullString
 
 
'If the user tries to move off the record or any other event fires a save lets ask whether they actually want to do something with the changes or discard them
If MsgBox("Changes have been made to this record." _
        & vbCrLf & vbCrLf & "Do you want to save these changes?" _
        , vbYesNo, "Changes Made...") = vbYes Then
 
'Carry out the form validation to ensure everything is filled in correctly if it isnt lets get this stuff fixed and not save the current record
 
 
 
            If Len(Nz(Me.Costtype)) < 1 Then
                Me.Costtype.SetFocus
                Me.Costtype.BackColor = vbRed
 
                ErrorStrings = ErrorStrings & "You must select the type of cost." & vbCrLf
 
            Else
 
                Me.Costtype.BackColor = 16579561
 
            End If
 
            If Len(Me.CostDept) < 1 Then
                Me.CostDept.SetFocus
                Me.CostDept.BackColor = vbRed
 
                ErrorStrings = ErrorStrings & "You must select the department that inccurred the cost." & vbCrLf
 
            Else
 
                Me.CostDept.BackColor = 16579561
 
            End If
 
            If Len(Nz(Me.CostDesc)) < 5 Then
                Me.CostDesc.SetFocus
                Me.CostDesc.BackColor = vbRed
 
                ErrorStrings = ErrorStrings & "You must enter an adequate cost description." & vbCrLf
 
            Else
 
                Me.CostDesc.BackColor = 16579561
 
            End If
 
            If Len(Nz(Me.CostFig)) < 1 Or (Me.CostFig) < 1 Then
                Me.CostFig.SetFocus
                Me.CostFig.BackColor = vbRed
 
                ErrorStrings = ErrorStrings & "You must enter a cost figure." & vbCrLf
 
            Else
 
                Me.CostFig.BackColor = 16579561
 
            End If
 
 
 
 
'Create the if statement to see if anything has been done incorrectly before allowing continuation
 
            If Len(Nz(ErrorStrings)) > 0 Then
'error has occured cancel any save of the record
                MsgBox ErrorStrings, vbInformation, "Errors in your entries"
                Cancel = True
                Exit Sub
            Else
'everything looks to be ok allow the code to continue running
                MsgBox "Cost record saved.", vbInformation, "Success"
 
            End If
 
Else
            DoCmd.RunCommand acCmdUndo
            'Clear out any of the vbred backgrounds if an undo is selected
            'Me.CorrectiveDate.BackColor = 16579561
Me.CostFig.BackColor = 16579561
Me.CostDesc.BackColor = 16579561
Me.Costtype.BackColor = 16579561
Me.CostDept.BackColor = 16579561
 
End If
End Sub
 
Private Sub Form_Current()
Dim rst As DAO.Recordset
Dim lngCount As Long
 
Set rst = Me.RecordsetClone
 
With rst
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
End If
 
If Me.CurrentRecord > .RecordCount Then
 
Me.txtCostRecNo = "New Cost Record"
 
Else
 
'Now output the results and capture new recorded added event so we do not have 3 of 2 situation.
 
Me.txtCostRecNo = "Cost record: " & Me.CurrentRecord & " of " & .RecordCount
 
End If
End With
End Sub
 

I have this code on a couple of other subforms (modified to fit the form) and I get the same error now occuring with those.

If I open the subform ONLY everything works perfectly fine, it is only happening when I am using the subforms on a main form and validating.

Any help is appreciated!

[munkee]

Ok I have got the validation part working ok however this error still keeps occuring at the end of the procedure. If I step through the code the error crops up within the save button on click event within the generic error handler after running through all of the before update event.

I guess I could find the error code and supress this but I want to make sure there is nothing fundamentally going wrong here?

I have read a lot of similar posts where people have suggested it is a sign of database corruption but I really do not think this is the case here. Like I said it now runs as intended but this error just pops up at the end.

[munkee]

More information to add:

If I fill I select options for both of the combo boxes i.e. department cost incurred by and cost type. I do not get any errors.

If I delete the selected value from the combo boxes and click save (to validate the form again). I do not get any errors.

So this only occurs on a record where no combo selection has been made.

Using debug.print

When there is a save without a selection made in the combo boxes the values of the combo boxes are both 0's

If I then make a selection in the for example the cost department the value changes to the correct ID number such as 2.

If I delete the selected value (department name) from the combo box and then resave the form validation spews out "Null" in the debug.print.