Navigating to a specific row on a subform

[dickhob]

I need to navigate to a control on a specific row on a subform. I have an control called PLY (think “line item #” in an order processing appl).

A user supplied search field = txtCurrentPly. (row selection via clicking is not an option)

I can get to the subform.
I can get the number of rows on the subform.
But my IF statement generates a “451” error.

I tried using the CurrentRecord property, but I simply don’t know how to use it.

Here’s some data

TxtCurrentPly = 2b (this is the data I want to find and change)

SfrmPly.Form.Ply (this is the subform) contains 7 rows

2
2a
2b
2c
2d
2e
2f

=============
Here’s my attempt:

Private Sub txtCurrentPly_AfterUpdate()
Dim sfRows
Dim x

sfRows = sfrmPly.Form.Count
x = 1

With sfrmPly.Form 'subform
For x = x To sfRows
If .Ply(x) = Me.txtCurrentPly Then -- code generates 451 error msg
.Ply(x).BackColor = vbBlue
.Ply(x).ForeColor = vbYellow

End If
Next x
End With

End Sub
=============

What is the correct way to do this?
Thanks

 

[PHV]

Take a look at the RecordsetClone, FindFirst, NoMatch and Bookmark methods/properties of the Form/DAO.Recordset objects.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dickhob]

PHV,
I've been playing with your suggestion all morning- no luck.

Here's some code:

Dim rs As Recordset
Dim sfRows
Dim x

Set rs = Me.sfrmPly.Form.RecordsetClone
rs.MoveFirst
====
I can't get by the SET statement- Error 13 Type Mismatch.

I'm not sure this will work with a subform.

Plus, if I read the RecordsetClone help properly I'll get a read-only recordset and I need it updateable.

 

[RoyVidar]

Try explicit declaration:

[tt]dim rs as dao.recordset[/tt]

And ensure you have Microsoft DAO 3.# Object Library referenced (in VBE - Tools | References)

Try the recordsetclone first...

But, from the initial sample, it seems you're trying to perform conditional formatting on a continuous forms controls. If that's so, take a look at conditional formatting in the format menu in stead.

Roy-Vidar