Checking a field in a table for a true value and then going to that record

[Triacona]

Dear All,

I have a problem, I have solved in one way, but my manager has asked me to do it in VB code.

It is basically a property database, and it has:
Table Owners
With a field:
ynCurrentOwner True or false field.

I basically want when the form opens for the subform (opening at the same time) to go to the record with the current owner checked.
The subform is:
OwnersSubform
So on the On Open function within OwnersSubform I have an Event Procedure.

Te code is

Private Sub Form_Open(Cancel As Integer)


DoCmd.GoToRecord acDataForm, ynCurrentOwner.Value, acGoTo, True


End Sub

OR

Dim tmprs As DAO.Recordset
'Set tmprs = [OwnersSubform].Form.txtCurrentOwner
'Dim fld As DAO.Field

    
'Debug.Print Error(i)
'MsgBox i, vbOKOnly

'While Not tmprs.EOF

    'For Each fld In tmprs.Fields
                  
    '    If txtCurrentOwner.Value = True Then
    '            txtCurrentOwner.SetFocus
    '    End If
    
    'i = i + 1

   ' Next 'for
    
'Wend

The way I solved it in the query was to sort the records on ynCurrentOwner in Ascending Order.
Would I execute that SQL in the code?
or
Would I run a loop checking through all fields in the records looking for ynCurrentOwner = True?

Looping through all the records and searching each field seems to be a intensive way of doing it.
Is there a way to loop through only a specific field looking for the required value only in that field?



Thanks you for all the formcoming help


Thank you,

Kind regards

Triacona

 

[Triacona]

I feel ignored...

Thank you,

Kind regards

Triacona

 

[dhookom]

I would probably use the recordsetclone of the subform. Loop through the clone and then change the subform's bookmark to the recordset's bookmark.

I don't have the code in any of my test database files.

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane,
Thanks for your reply, much aprpeciated!
I will try implement your solotion as soon as I get time.

Thank you again!

Thank you,

Kind regards

Triacona

 

[TheAceMan1]

How are ya Triacona . . .

In the On Load event of the subform ... have you tried:

   Me.Recordset.FindFirst "[ynCurrentOwner] = " & True

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]