FindRecord on yes/no

[CoachPhil]

Is it possible to use the FindRecord to scroll through records using a yes/no checkbox as criteria?
I've really been at this a while.

CoachPhil

 

[RuralGuy]

I'd like to help if I can. What version of Access are you using?

 

[datAdrenaline]

Also ... are you using and ADP or MDB ... plus (if using an MDB) .. is your data stored in a database other than Access/JET?

 

[CoachPhil]

Access 2003, and MDB and yes

 

[CoachPhil]

Sorry, it is Jet

 

[RuralGuy]

Are you talking about some of the native Access menu stuff or are you creating a SearchForm for your users?

 

[CoachPhil]

I am trying to create a search function which would allow the user to scroll through the records in the program based on their status in the yes/no checkbox. I have developed to the point where it will scroll to the first record with a 'no' status, but it will go no further.

 

[RuralGuy]

Again, is this in a form you developed or with the native menu system in Access.

 

[CoachPhil]

In a form that I developed

 

[RuralGuy]

Are you displaying the search in a SubForm that is in Continuous Form mode? How many records does your query return?

 

[datAdrenaline]

Ok ... a check box will return a value of True (-1), False (0), or if it is unbound and has the triple state property set to yes, then a Null can be returned as well (I recommend setting the Triple State property to no ...

The DoCmd object acts upon the object with the focus, so if you use the .FindRecord method, you will probably want to set the focus to the field (control) that you wish to search through since .FindRecord has the option to search ALL fields, or just the "current" field, which is the one that has the Focus. So, your code should look something like this ...

Private Sub SomeCommanButtonName_Click()
    Me.SomeControlName.SetFocus
    DoCmd.FindRecord Me.UnBoundCheckBoxControlName, acAnywhere, , , , acCurrent
End Sub

With the said, and the limitations around the DoCmd object, I would suggest that you do NOT use DoCmd.FindRecord. I would suggest you utilitize the forms RecordsetClone property and the DAO object models' .FindNext menthod (there is a FindFirst, FindPrevious, FindLast also, and they all have the same syntax structure)

Private Sub SomeCommandButton_Click()
    With Me.RecordsetClone
        .FindFirst "YourYesNoField = " & Me.UnBoundCheckBoxName
        If .NoMatch Then
            MsgBox "Not Found"
        Else
            Me.Bookmark = .Bookmark
        End If
    End With
End Sub

By using the DAO & RecordsetClone technique, you do not have to be concerned about which control has the focus, plus you gain a ton of flexibility with your search.

Hope that helps!

>> Edits <<
Sorry ... I had this post "In the Que" for a LONG time .. so .. I missed the middle part of the "conversation" ..

 

[CoachPhil]

Rural Guy,

Are you displaying the search in a SubForm that is in Continuous Form mode? How many records does your query return?

Yes, it is in continuos form and right now I have about 30 records, probably go up to 2000.

CoachPhil

 

[RuralGuy]

The user should be able to scroll through the records in the SubForm. Is that not true in your case?

 

[gemma-the-husky]

the problem is that the code for find first and find next is slightly different - so you need two buttons, a find first, and a find next

code for FIRST READS
fieldname.setfocus
DoCmd.FindRecord searchstring, acAnywhere, False, acSearchAll, False, acCurrent, True

code for SUBSEQUENT READS
fieldname.setfocus
DoCmd.FindRecord searchstring, acAnywhere, False, acDown, False, acCurrent, False


hope this helps

--------
i often do this as its easier than using the binoculars icon

 

[CoachPhil]

Rural Guy,

Yes, they can scroll through all records but, I want them to be able to scroll through only the records in the yes/no checkbox which reads no.

Gemma, I will try your suggestion.

Thanks to both of you for your time.

CoachPhil

 

[RuralGuy]

Rural Guy,
Yes, they can scroll through all records but, I want them to be able to scroll through only the records in the yes/no checkbox which reads no.
CoachPhil

Then the RecordSource of the SubForm needs to be a query that includes your criteria in a WHERE clause.