Search Current Records and test for condition

L

Lloyd

I have been reading the discussion groups and trying to adapt other example,
but I dont think I am on the right track...

My form is a single form (not continuous). If a user selects a dispo of
Cleared Judicially, I want to check subfrmPersons and make sure that on EACH
person the "Suspect" is in custody by selecting the "InCustody" control for a
true value.

The code below works for the first record, but if it finds the first person
that meets the criteria, then it stops. I tried using .FindNext, but that
doesnt seem to work at all since the first code does the .FindFirst and then
the code is done.

How do I look through all the persons in the current record to see if
"InCustody" is checked on all of them? Do I need to do a loop (Havnt done
one of those before)??

help please....



'Checks to make sure all suspects are in custody before allowing user to
clear case Judicially

If Me.Dispo = "Cleared Judicially" Then

With Me.subfrmPersons.Form.RecordsetClone
Select Case Me.CaseType

Case "Murder"
.FindFirst "[PersonType] = ""Suspect"""

'Checks the in-custody control, 0 = false (not
checked), -1 = True (checked)
If Me.subfrmPersons.Form.InCustody = 0 Then
MsgBox "You can not clear this case
Judicially as not all suspects are in custody", vbQuestion, "Are all suspects
really in custody?"
Me.Undo
End If


Case "Murder"
.FindNext "[PersonType] = ""Suspect"""
If Me.subfrmPersons.Form.InCustody = 0 Then
MsgBox "You can not clear this case
Judicially as not all suspects are in custody", vbQuestion, "Are all suspects
really in custody?"
Me.Undo
' End If

End Select
End With

End If
 
M

Marshall Barton

Lloyd said:
I have been reading the discussion groups and trying to adapt other example,
but I dont think I am on the right track...

My form is a single form (not continuous). If a user selects a dispo of
Cleared Judicially, I want to check subfrmPersons and make sure that on EACH
person the "Suspect" is in custody by selecting the "InCustody" control for a
true value.

The code below works for the first record, but if it finds the first person
that meets the criteria, then it stops. I tried using .FindNext, but that
doesnt seem to work at all since the first code does the .FindFirst and then
the code is done.

How do I look through all the persons in the current record to see if
"InCustody" is checked on all of them? Do I need to do a loop (Havnt done
one of those before)??


'Checks to make sure all suspects are in custody before allowing user to
clear case Judicially

If Me.Dispo = "Cleared Judicially" Then

With Me.subfrmPersons.Form.RecordsetClone
Select Case Me.CaseType

Case "Murder"
.FindFirst "[PersonType] = ""Suspect"""

'Checks the in-custody control, 0 = false (not
checked), -1 = True (checked)
If Me.subfrmPersons.Form.InCustody = 0 Then
MsgBox "You can not clear this case
Judicially as not all suspects are in custody", vbQuestion, "Are all suspects
really in custody?"
Me.Undo
End If
Click to expand...
[snip]

Yes, you need a loop. It could be something like this air
code:

If Me.Dispo = "Cleared Judicially" Then
With Me.subfrmPersons.Form.RecordsetClone
Select Case Me.CaseType
Case "Murder"
.FindFirst "[PersonType] = ""Suspect"""
Do Until .NoMatch
If Me.subfrmPersons.Form.InCustody = 0 Then
MsgBox "You can not clear this case ...
Me.Undo
Exit Do
End If
.FindNext "[PersonType] = ""Suspect"""
Loop
. . .
 
L

Lloyd

Marchall,

Thanks for your help, this code is definately a lot cleaner with the loop,
but if I select "in-custody" on the first suspect, the loop keeps going back
to the same person and not moving onto the next record. Any thoughts, I am
new to loops so still trying to wrap my head around them.

Marshall Barton said:
Lloyd said:
I have been reading the discussion groups and trying to adapt other example,
but I dont think I am on the right track...

My form is a single form (not continuous). If a user selects a dispo of
Cleared Judicially, I want to check subfrmPersons and make sure that on EACH
person the "Suspect" is in custody by selecting the "InCustody" control for a
true value.

The code below works for the first record, but if it finds the first person
that meets the criteria, then it stops. I tried using .FindNext, but that
doesnt seem to work at all since the first code does the .FindFirst and then
the code is done.

How do I look through all the persons in the current record to see if
"InCustody" is checked on all of them? Do I need to do a loop (Havnt done
one of those before)??


'Checks to make sure all suspects are in custody before allowing user to
clear case Judicially

If Me.Dispo = "Cleared Judicially" Then

With Me.subfrmPersons.Form.RecordsetClone
Select Case Me.CaseType

Case "Murder"
.FindFirst "[PersonType] = ""Suspect"""

'Checks the in-custody control, 0 = false (not
checked), -1 = True (checked)
If Me.subfrmPersons.Form.InCustody = 0 Then
MsgBox "You can not clear this case
Judicially as not all suspects are in custody", vbQuestion, "Are all suspects
really in custody?"
Me.Undo
End If
Click to expand...
[snip]

Yes, you need a loop. It could be something like this air
code:

If Me.Dispo = "Cleared Judicially" Then
With Me.subfrmPersons.Form.RecordsetClone
Select Case Me.CaseType
Case "Murder"
.FindFirst "[PersonType] = ""Suspect"""
Do Until .NoMatch
If Me.subfrmPersons.Form.InCustody = 0 Then
MsgBox "You can not clear this case ...
Me.Undo
Exit Do
End If
.FindNext "[PersonType] = ""Suspect"""
Loop
. . .
Click to expand...
 
M

Marshall Barton

I don't see how that can happen from just this piece of
code. Maybe it has something to do with where you are using
the code or with some other code before or after the code I
posted?? If you can't spot something elsewhere, post the
rest of the code and explain where it is used.

In checking it over, I question if you want to check the
form's current record's InCustody value or if you really
meant to check InCustody in each record found in the loop.
If it's the latter, then that line should be:

If rs!InCustody = 0 Then
--
Marsh
MVP [MS Access]

Thanks for your help, this code is definately a lot cleaner with the loop,
but if I select "in-custody" on the first suspect, the loop keeps going back
to the same person and not moving onto the next record. Any thoughts, I am
new to loops so still trying to wrap my head around them.

Marshall Barton said:
Yes, you need a loop. It could be something like this air
code:

If Me.Dispo = "Cleared Judicially" Then
With Me.subfrmPersons.Form.RecordsetClone
Select Case Me.CaseType
Case "Murder"
.FindFirst "[PersonType] = ""Suspect"""
Do Until .NoMatch
If Me.subfrmPersons.Form.InCustody = 0 Then
MsgBox "You can not clear this case ...
Me.Undo
Exit Do
End If
.FindNext "[PersonType] = ""Suspect"""
Loop
. . .
Click to expand...
Lloyd said:
I have been reading the discussion groups and trying to adapt other example,
but I dont think I am on the right track...

My form is a single form (not continuous). If a user selects a dispo of
Cleared Judicially, I want to check subfrmPersons and make sure that on EACH
person the "Suspect" is in custody by selecting the "InCustody" control for a
true value.

The code below works for the first record, but if it finds the first person
that meets the criteria, then it stops. I tried using .FindNext, but that
doesnt seem to work at all since the first code does the .FindFirst and then
the code is done.

How do I look through all the persons in the current record to see if
"InCustody" is checked on all of them? Do I need to do a loop (Havnt done
one of those before)??


'Checks to make sure all suspects are in custody before allowing user to
clear case Judicially

If Me.Dispo = "Cleared Judicially" Then

With Me.subfrmPersons.Form.RecordsetClone
Select Case Me.CaseType

Case "Murder"
.FindFirst "[PersonType] = ""Suspect"""

'Checks the in-custody control, 0 = false (not
checked), -1 = True (checked)
If Me.subfrmPersons.Form.InCustody = 0 Then
MsgBox "You can not clear this case
Judicially as not all suspects are in custody", vbQuestion, "Are all suspects
really in custody?"
Me.Undo
End If
Click to expand...
[snip]
Click to expand...
Click to expand...
 
L

Lloyd

Marshall,

I have posted the entire code below from the below update event. I thought
that maybe the code could be in the wrong event as well, but I added a msgbox
to watch what happens as it goes through the names and, I have 2 people that
meet the criteria, Murder and Suspect, first names are robert and then james.
The code hits robert and it displays -1 for the in-custody value telling me
that its marked as true. but as the code does the loop, it then hits robert
again giving the same value, but never displays James as the second person.
the code then ends. There are a total of 3 seperate persons in the overall
record, and the code correctly only loops through 2 of them that meet the
criteria, but it seems to get stuck on the first person robert.

I tried adding your suggested code of If rs!InCustody = 0 Then, but I got an
error #424 object requered.

thanks again for your help!!

Below is the complete code for the event:

Private Sub Dispo_BeforeUpdate(Cancel As Integer)

If Me!Dispo = "Open" And _
Not IsNull(Me!DispoDate) Then
If MsgBox("Are you sure you want to set Dispo " _
& "to Open? If you select YES, Dispo Date will be erased",
vbExclamation + vbYesNo) = vbNo Then
Cancel = True
Me!Dispo.Undo
Exit Sub
End If
End If

Me!DispoDate.Enabled = Not (Me!Dispo = "Open" _
Or IsNull(Me!Dispo))

'Checks to make sure all suspects are in custody before allowing user to
clear case Judicially

If Me.Dispo = "Cleared Judicially" Then
With Me.subfrmPersons.Form.RecordsetClone
Select Case Me.CaseType
Case "Murder"
.FindFirst "[PersonType] = ""Suspect"""
Do Until .NoMatch
'Checks the in-custody control, 0 = false (not checked),
-1 = True (checked)
MsgBox Me.subfrmPersons.Form.FirstName & " " &
Me.subfrmPersons.Form.InCustody
'If rs!InCustody = 0 Then
If Me.subfrmPersons.Form.InCustody = 0 Then
MsgBox "You can not clear this case Judicially as
not all suspects are in custody", vbQuestion, "Are all suspects really in
custody?"
Me.Undo
Exit Do
End If
.FindNext "[PersonType] = ""Suspect"""
Loop

End Select
End With

End If

End Sub




Marshall Barton said:
I don't see how that can happen from just this piece of
code. Maybe it has something to do with where you are using
the code or with some other code before or after the code I
posted?? If you can't spot something elsewhere, post the
rest of the code and explain where it is used.

In checking it over, I question if you want to check the
form's current record's InCustody value or if you really
meant to check InCustody in each record found in the loop.
If it's the latter, then that line should be:

If rs!InCustody = 0 Then
--
Marsh
MVP [MS Access]

Thanks for your help, this code is definately a lot cleaner with the loop,
but if I select "in-custody" on the first suspect, the loop keeps going back
to the same person and not moving onto the next record. Any thoughts, I am
new to loops so still trying to wrap my head around them.

Marshall Barton said:
Yes, you need a loop. It could be something like this air
code:

If Me.Dispo = "Cleared Judicially" Then
With Me.subfrmPersons.Form.RecordsetClone
Select Case Me.CaseType
Case "Murder"
.FindFirst "[PersonType] = ""Suspect"""
Do Until .NoMatch
If Me.subfrmPersons.Form.InCustody = 0 Then
MsgBox "You can not clear this case ...
Me.Undo
Exit Do
End If
.FindNext "[PersonType] = ""Suspect"""
Loop
. . .
Click to expand...
Lloyd wrote:
I have been reading the discussion groups and trying to adapt other example,
but I dont think I am on the right track...

My form is a single form (not continuous). If a user selects a dispo of
Cleared Judicially, I want to check subfrmPersons and make sure that on EACH
person the "Suspect" is in custody by selecting the "InCustody" control for a
true value.

The code below works for the first record, but if it finds the first person
that meets the criteria, then it stops. I tried using .FindNext, but that
doesnt seem to work at all since the first code does the .FindFirst and then
the code is done.

How do I look through all the persons in the current record to see if
"InCustody" is checked on all of them? Do I need to do a loop (Havnt done
one of those before)??


'Checks to make sure all suspects are in custody before allowing user to
clear case Judicially

If Me.Dispo = "Cleared Judicially" Then

With Me.subfrmPersons.Form.RecordsetClone
Select Case Me.CaseType

Case "Murder"
.FindFirst "[PersonType] = ""Suspect"""

'Checks the in-custody control, 0 = false (not
checked), -1 = True (checked)
If Me.subfrmPersons.Form.InCustody = 0 Then
MsgBox "You can not clear this case
Judicially as not all suspects are in custody", vbQuestion, "Are all suspects
really in custody?"
Me.Undo
End If
[snip]
Click to expand...
Click to expand...
Click to expand...
 
M

Marshall Barton

I think both the new msgbox and the If are referring to the
single record displayed on the form and not to each record
found in the loop. I did inadvertantly use rs!InCustody
when it should have been just !InCustody so that probably
accounts for the error.

Try changing the msgbox to
MsgBox !FirstName & " " & !InCustody
and the If to:
If !InCustody = 0 Then
This presumes that the fields in the table are named
FirstName and InCustody (can be different from the names of
the controls bound to those fields).
--
Marsh
MVP [MS Access]

I have posted the entire code below from the below update event. I thought
that maybe the code could be in the wrong event as well, but I added a msgbox
to watch what happens as it goes through the names and, I have 2 people that
meet the criteria, Murder and Suspect, first names are robert and then james.
The code hits robert and it displays -1 for the in-custody value telling me
that its marked as true. but as the code does the loop, it then hits robert
again giving the same value, but never displays James as the second person.
the code then ends. There are a total of 3 seperate persons in the overall
record, and the code correctly only loops through 2 of them that meet the
criteria, but it seems to get stuck on the first person robert.

I tried adding your suggested code of If rs!InCustody = 0 Then, but I got an
error #424 object requered.

thanks again for your help!!

Below is the complete code for the event:

Private Sub Dispo_BeforeUpdate(Cancel As Integer)

If Me!Dispo = "Open" And _
Not IsNull(Me!DispoDate) Then
If MsgBox("Are you sure you want to set Dispo " _
& "to Open? If you select YES, Dispo Date will be erased",
vbExclamation + vbYesNo) = vbNo Then
Cancel = True
Me!Dispo.Undo
Exit Sub
End If
End If

Me!DispoDate.Enabled = Not (Me!Dispo = "Open" _
Or IsNull(Me!Dispo))

'Checks to make sure all suspects are in custody before allowing user to
clear case Judicially

If Me.Dispo = "Cleared Judicially" Then
With Me.subfrmPersons.Form.RecordsetClone
Select Case Me.CaseType
Case "Murder"
.FindFirst "[PersonType] = ""Suspect"""
Do Until .NoMatch
'Checks the in-custody control, 0 = false (not checked),
-1 = True (checked)
MsgBox Me.subfrmPersons.Form.FirstName & " " &
Me.subfrmPersons.Form.InCustody
'If rs!InCustody = 0 Then
If Me.subfrmPersons.Form.InCustody = 0 Then
MsgBox "You can not clear this case Judicially as
not all suspects are in custody", vbQuestion, "Are all suspects really in
custody?"
Me.Undo
Exit Do
End If
.FindNext "[PersonType] = ""Suspect"""
Loop

End Select
End With

End If

End Sub


Marshall Barton said:
I don't see how that can happen from just this piece of
code. Maybe it has something to do with where you are using
the code or with some other code before or after the code I
posted?? If you can't spot something elsewhere, post the
rest of the code and explain where it is used.

In checking it over, I question if you want to check the
form's current record's InCustody value or if you really
meant to check InCustody in each record found in the loop.
If it's the latter, then that line should be:

If rs!InCustody = 0 Then
--
Marsh
MVP [MS Access]

Thanks for your help, this code is definately a lot cleaner with the loop,
but if I select "in-custody" on the first suspect, the loop keeps going back
to the same person and not moving onto the next record. Any thoughts, I am
new to loops so still trying to wrap my head around them.

:
Yes, you need a loop. It could be something like this air
code:

If Me.Dispo = "Cleared Judicially" Then
With Me.subfrmPersons.Form.RecordsetClone
Select Case Me.CaseType
Case "Murder"
.FindFirst "[PersonType] = ""Suspect"""
Do Until .NoMatch
If Me.subfrmPersons.Form.InCustody = 0 Then
MsgBox "You can not clear this case ...
Me.Undo
Exit Do
End If
.FindNext "[PersonType] = ""Suspect"""
Loop
. . .


Lloyd wrote:
I have been reading the discussion groups and trying to adapt other example,
but I dont think I am on the right track...

My form is a single form (not continuous). If a user selects a dispo of
Cleared Judicially, I want to check subfrmPersons and make sure that on EACH
person the "Suspect" is in custody by selecting the "InCustody" control for a
true value.

The code below works for the first record, but if it finds the first person
that meets the criteria, then it stops. I tried using .FindNext, but that
doesnt seem to work at all since the first code does the .FindFirst and then
the code is done.

How do I look through all the persons in the current record to see if
"InCustody" is checked on all of them? Do I need to do a loop (Havnt done
one of those before)??


'Checks to make sure all suspects are in custody before allowing user to
clear case Judicially

If Me.Dispo = "Cleared Judicially" Then

With Me.subfrmPersons.Form.RecordsetClone
Select Case Me.CaseType

Case "Murder"
.FindFirst "[PersonType] = ""Suspect"""

'Checks the in-custody control, 0 = false (not
checked), -1 = True (checked)
If Me.subfrmPersons.Form.InCustody = 0 Then
MsgBox "You can not clear this case
Judicially as not all suspects are in custody", vbQuestion, "Are all suspects
really in custody?"
Me.Undo
End If
[snip]
Click to expand...
Click to expand...
Click to expand...
 
L

Lloyd

Marshall,

your the best, that was the key....It works great now!

One problem down, and one to go and then i'm done.....

Thank you again!

Marshall Barton said:
I think both the new msgbox and the If are referring to the
single record displayed on the form and not to each record
found in the loop. I did inadvertantly use rs!InCustody
when it should have been just !InCustody so that probably
accounts for the error.

Try changing the msgbox to
MsgBox !FirstName & " " & !InCustody
and the If to:
If !InCustody = 0 Then
This presumes that the fields in the table are named
FirstName and InCustody (can be different from the names of
the controls bound to those fields).
--
Marsh
MVP [MS Access]

I have posted the entire code below from the below update event. I thought
that maybe the code could be in the wrong event as well, but I added a msgbox
to watch what happens as it goes through the names and, I have 2 people that
meet the criteria, Murder and Suspect, first names are robert and then james.
The code hits robert and it displays -1 for the in-custody value telling me
that its marked as true. but as the code does the loop, it then hits robert
again giving the same value, but never displays James as the second person.
the code then ends. There are a total of 3 seperate persons in the overall
record, and the code correctly only loops through 2 of them that meet the
criteria, but it seems to get stuck on the first person robert.

I tried adding your suggested code of If rs!InCustody = 0 Then, but I got an
error #424 object requered.

thanks again for your help!!

Below is the complete code for the event:

Private Sub Dispo_BeforeUpdate(Cancel As Integer)

If Me!Dispo = "Open" And _
Not IsNull(Me!DispoDate) Then
If MsgBox("Are you sure you want to set Dispo " _
& "to Open? If you select YES, Dispo Date will be erased",
vbExclamation + vbYesNo) = vbNo Then
Cancel = True
Me!Dispo.Undo
Exit Sub
End If
End If

Me!DispoDate.Enabled = Not (Me!Dispo = "Open" _
Or IsNull(Me!Dispo))

'Checks to make sure all suspects are in custody before allowing user to
clear case Judicially

If Me.Dispo = "Cleared Judicially" Then
With Me.subfrmPersons.Form.RecordsetClone
Select Case Me.CaseType
Case "Murder"
.FindFirst "[PersonType] = ""Suspect"""
Do Until .NoMatch
'Checks the in-custody control, 0 = false (not checked),
-1 = True (checked)
MsgBox Me.subfrmPersons.Form.FirstName & " " &
Me.subfrmPersons.Form.InCustody
'If rs!InCustody = 0 Then
If Me.subfrmPersons.Form.InCustody = 0 Then
MsgBox "You can not clear this case Judicially as
not all suspects are in custody", vbQuestion, "Are all suspects really in
custody?"
Me.Undo
Exit Do
End If
.FindNext "[PersonType] = ""Suspect"""
Loop

End Select
End With

End If

End Sub


Marshall Barton said:
I don't see how that can happen from just this piece of
code. Maybe it has something to do with where you are using
the code or with some other code before or after the code I
posted?? If you can't spot something elsewhere, post the
rest of the code and explain where it is used.

In checking it over, I question if you want to check the
form's current record's InCustody value or if you really
meant to check InCustody in each record found in the loop.
If it's the latter, then that line should be:

If rs!InCustody = 0 Then
--
Marsh
MVP [MS Access]


Lloyd wrote:
Thanks for your help, this code is definately a lot cleaner with the loop,
but if I select "in-custody" on the first suspect, the loop keeps going back
to the same person and not moving onto the next record. Any thoughts, I am
new to loops so still trying to wrap my head around them.

:
Yes, you need a loop. It could be something like this air
code:

If Me.Dispo = "Cleared Judicially" Then
With Me.subfrmPersons.Form.RecordsetClone
Select Case Me.CaseType
Case "Murder"
.FindFirst "[PersonType] = ""Suspect"""
Do Until .NoMatch
If Me.subfrmPersons.Form.InCustody = 0 Then
MsgBox "You can not clear this case ...
Me.Undo
Exit Do
End If
.FindNext "[PersonType] = ""Suspect"""
Loop
. . .


Lloyd wrote:
I have been reading the discussion groups and trying to adapt other example,
but I dont think I am on the right track...

My form is a single form (not continuous). If a user selects a dispo of
Cleared Judicially, I want to check subfrmPersons and make sure that on EACH
person the "Suspect" is in custody by selecting the "InCustody" control for a
true value.

The code below works for the first record, but if it finds the first person
that meets the criteria, then it stops. I tried using .FindNext, but that
doesnt seem to work at all since the first code does the .FindFirst and then
the code is done.

How do I look through all the persons in the current record to see if
"InCustody" is checked on all of them? Do I need to do a loop (Havnt done
one of those before)??


'Checks to make sure all suspects are in custody before allowing user to
clear case Judicially

If Me.Dispo = "Cleared Judicially" Then

With Me.subfrmPersons.Form.RecordsetClone
Select Case Me.CaseType

Case "Murder"
.FindFirst "[PersonType] = ""Suspect"""

'Checks the in-custody control, 0 = false (not
checked), -1 = True (checked)
If Me.subfrmPersons.Form.InCustody = 0 Then
MsgBox "You can not clear this case
Judicially as not all suspects are in custody", vbQuestion, "Are all suspects
really in custody?"
Me.Undo
End If
[snip]
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to use FindNext on subform 2
Help with row movement/filtering, and function calls 2
Require fields and refresh 14
Prompt for User to Save Changes in a Form 1
Combo box and Form_BeforeUpdate 2
Where to put error handler code for "No current record" error? 1
Search engin 5
Quotes and Appostriphies in Find strings 2

Top