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.