Search Form / Requery / Validation problem

[china99boy]

Hi Guys,

I have a search form that I found on this site a while back that works great. I think it was title cool search tool. Thanks to the original developer. The problem I have is, the user searches for a customer, then selects that record. Once the record is selected the user can make changes to the record. I have a few validation rules on the form level. Example of 1 of the rules is as follows:

'Forces user to enter Closing Date if entry in FAT
If Not IsDate(ClosingDate) Then

    Select Case Me.cboFat
       Case 1, 2, 3, 4, 5, 7
            MsgBox "Based on your selection in the Final Action Taken," & _
            vbCrLf & "          Closing Date is a required field!", vbCritical, "Missing Data!"
            Cancel = True
        End Select
End If

If the user deletes the data from the "closingDate" field and then tries to select another record I continue to get the required message but I also get an runtime error 2465 "Microsoft Access can't find the field '|' referred to in expression" and debugs at "DoCmd.Requery" in the code of the search form.

I need for the user to continue to get msgbox if the entry is missing and cancel, but I do not want them to be able to click on another record until the required entry is entered and I certainly don't want that runtime error.

Lastly, I have the following code in the afterUpdate event of the ClosingDate field on the same form. This works fine. When the requirements are met, the txtAdvanceDate field becomes visable. What I want to happen is, when the user clicks on another record that doesn't not meet the requirements, that field should not be visable. Currently if the requirements are met on one record and I select another that does not, the field is still visable on the form. I guess I will need it to requery to check if the criteria has been bet each time a new record is selected.

Please can someone point me in the right directions.

Private Sub ClosingDate_AfterUpdate()

If (Me.LoanType = "O" Or Me.LoanType = "H") And Me.cboFat = "1" And IsDate(Me.ClosingDate) Then

    txtAdvanceDate.Visible = True
    txtAdvanceDate = getDueDate(ClosingDate, 4)
Else
    txtAdvanceDate.Visible = False
    txtAdvanceDate = ""
End If

End Sub

My search form code is just like the code below with the exception of the additional validation rules that I have added to the form. Can someone please advise. Thanks in advance.

Option Compare Database
Option Explicit

Private Sub ClearIt_Click()
On Error GoTo Err_ClearIt

Me.Search = ""
 Me.Search2 = ""
  Me.QuickSearch.Requery
   Me.QuickSearch.SetFocus

Exit_ClearIt_Click:
    Exit Sub

Err_ClearIt:
    MsgBox Err.Description
    Resume Exit_ClearIt_Click

End Sub

Private Sub QuickSearch_AfterUpdate()
    
DoCmd.Requery
Me.RecordsetClone.FindFirst "[Name] = '" & Me![QuickSearch] & "'"
If Not Me.RecordsetClone.NoMatch Then
   Me.Bookmark = Me.RecordsetClone.Bookmark
Else
   MsgBox "Could not locate [" & Me![QuickSearch] & "]"
End If

End Sub


Private Sub Search_Change()
Dim vSearchString As String

 vSearchString = Search.Text
 Search2.Value = vSearchString
 Me.QuickSearch.Requery

End Sub

 

[Pat Hartman]

1. The first bit of code MUST be in the Form's BeforeUpdate event to be effective. Try checking the field for null/ZLS to get rid of the error.
2. When you change the value of the data in the current record, you should do it in the BeforeUpdate event. Doing it in the Form's AfterUpdate event puts the form into a loop since dirtying the record requires Access to save it again.
3. In addition to setting the visible property in the BeforeUpdate event, you also need to set it in the Current event so that it operates properly for each record as you scroll through a recordset.