Find and Find Next

[Guest]

I have a form that pulls multiple records based on a combo box and the
records are displayed in the form. The user then proceeds to enter data. I
want to set up as part of my closing of the form a find record that matches
certain criteria and the cursor goes to that record. The criteria is
"ChkUnaccept = Yes and TxtOccur = 0". The user corrects the error and then
clicks the close button to see if there are any others. If there are then
the cursor goes to the next record where this occurs until it doesn't find
any more and then a macro is run. I just need the code to have the record
found until it goes to the End of the form and finds no records. Please
Help!!

 

[Guest]

I'm assuming you have a continuous form with a bunch of records showing.

In a command button click event have the following:

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If whatever Then
Me.BookMark = .BookMark
control.SetFocus
MsgBox etc.
Exit Sub
End If
.MoveNext
Loop
End With

 

[Marshall Barton]

I have a form that pulls multiple records based on a combo box and the
records are displayed in the form. The user then proceeds to enter data. I
want to set up as part of my closing of the form a find record that matches
certain criteria and the cursor goes to that record. The criteria is
"ChkUnaccept = Yes and TxtOccur = 0". The user corrects the error and then
clicks the close button to see if there are any others. If there are then
the cursor goes to the next record where this occurs until it doesn't find
any more and then a macro is run. I just need the code to have the record
found until it goes to the End of the form and finds no records.

First, you need to save any changes to the current record.
Then you can use the FindFirst (DAO) or Find (ADO) method to
check the form's records.

The form's Unload event would look like:

If Me.Dirty Then Me.Dirty = False
With Me.RecordsetClone
.FindFirst "ChkUnaccept = Yes And TxtOccur = 0"
If Not .NoMatch Then
Cancel = True 'prevent form from closing
Me.Bookmark = .Bookmark
Enf If
End With

 

[Guest]

Thanks so much for your response. This almost works but then again it
doesn't. Again, the first time of this occurrence is found and I correct it
but once corrected, it doesn't find the other records with the same error. I
am using this same type of coding to check two errors. Then the macro runs.
But actually, if you can help me with having the cursor go to the record that
has the error I would appreciate it. I would think that I should be using
FindFirst but when I try using that instead of MoveFirst I get a type
mismatch. I am going nuts. I am very new to VBA and have purchased books on
it but haven't found the answer there either.

Here is the coding that I am using:

Private Sub SvClse_Click()

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If [ChkUnaccept] = Yes = 0 And [TxtOccur] = 0 Then
MsgBox "You have checked Unacceptable for an Activity with no indication
of the number of Occurrences!!", vbOKOnly, "Occurrences?"
Me.Bookmark = .Bookmark
Activity.SetFocus

Exit Sub
End If
.MoveNext
Loop
End With

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If [ChkUnaccept] = Yes = 0 And IsNull([Notes]) Then
MsgBox "You have checked Unacceptable for an Activity but have failed
to enter notes explaining this!! Please check your work and correct any or
all errors!!", vbOKOnly, "No Explanation"
Me.Bookmark = .Bookmark
Activity.SetFocus

Exit Sub
End If
.MoveNext
Loop
End With



DoCmd.RunMacro "mcrclosechangeform.clstr"



End Sub




Thanks in advance