Problem Opening Form at Specific Record

[Tom Lewis]

I am using an OpenArgs property to pass a primary key
value to a form, and want to open the form with this
record active. When I try to do this, the form opens, but
the bookmark to the specified record is ignored. Oddly, if
I change the form to design view and then back again to
form view, the desired record is current when the form
opens.

The code I am using is:

Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Dim rst As ADODB.Recordset
Dim strSearchExp As String
Dim varBookMark As Variant
Set rst = Me.RecordsetClone
strSearchExp = "[FacilityID] = " & Me.OpenArgs
With rst
.MoveFirst
.Find strSearchExp
If Not .EOF Then
varBookMark = rst.Bookmark
Me.Bookmark = varBookMark
End If
End With
rst.Close
Set rst = Nothing
End If
End Sub

I have been able to confirm that the OpenArgs property is
being received by the form, but assigning the bookmark is
not working. I have also tried to use the DoCmd.GoToRecord
method to open the form at the desired record, but this
seems to result in the same problem. I am new to ADO, so I
suspect I am missing something simple, but I have not been
able to figure out where the problem is.

Any help will be greatly appreciated.

Tom

 

[BK]

How about throwing a Refresh in there. After you open the form if you
press F9 and it shows up, then you'll know a refresh is in order. If
that doesn't work, what I usually like to do is just set the recordset
of the form on the fly.

So you would create the sql statement like (psuedo air-code follows):

strSQL = "SELECT Stuff FROM Table WHERE FacilityID=" & Me.OpenArgs
Docmd.OpenForm "MyForm"
Me.Recordset=strSQL

I usually like to set all my forms to dataentry and then turn it off
after I set the recordsource, just to avoid a flashing screen. But
that's more out of aesthetics than anything.

I'm pretty new to ado myself, so I could be way off base, but I've
always opened recordsets with:

rst.Open strSQL, con

where rst is Dimmed as a NEW ADODB.Recordset and con is a connection
object to the database.

Hope something in there helps.

Bill

 

[Tom Lewis]

Thanks Bill,

You put me on the right track and I figured out the
problem:

The form is bound to a 12,000 row view, but when the code
creates the RecordsetClone the entire recordset has not
yet been scanned by Access; (the rst.RecordCount property
returns 50 when the code hits the .Find method), so
records at the end of the recordset are not included in
the search for a match for OpenArgs, and finding no match
the form opens at the first record of the recordset.

I've seen this behavior before, as when Access returns
partially calculated results for calculated controls as
large recordsets are loading, but I didn't know that
RecordsetClones exhibit the same problem. The immediate
fix is easy enough: just add a line of code before setting
the clone:

"DoCmd.GoToRecord acDataForm, Me.Form.Name, acLast"

This works ok for now, but I think your suggestion of
building the recordset on the fly is better in the long
run. It makes little sense to query so much data from the
server that Access cannot keep up with it... I've still
got to shake the bad habits I developed working with Jet
db's ;-)

Thanks again,

Tom

-----Original Message-----
How about throwing a Refresh in there. After you open the form if you
press F9 and it shows up, then you'll know a refresh is in order. If
that doesn't work, what I usually like to do is just set the recordset
of the form on the fly.

So you would create the sql statement like (psuedo air- code follows):

strSQL = "SELECT Stuff FROM Table WHERE FacilityID=" & Me.OpenArgs
Docmd.OpenForm "MyForm"
Me.Recordset=strSQL

I usually like to set all my forms to dataentry and then turn it off
after I set the recordsource, just to avoid a flashing screen. But
that's more out of aesthetics than anything.

I'm pretty new to ado myself, so I could be way off base, but I've
always opened recordsets with:

rst.Open strSQL, con

where rst is Dimmed as a NEW ADODB.Recordset and con is a connection
object to the database.

Hope something in there helps.

Bill

I am using an OpenArgs property to pass a primary key
value to a form, and want to open the form with this
record active. When I try to do this, the form opens, but
the bookmark to the specified record is ignored. Oddly, if
I change the form to design view and then back again to
form view, the desired record is current when the form
opens.

The code I am using is:

Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Dim rst As ADODB.Recordset
Dim strSearchExp As String
Dim varBookMark As Variant
Set rst = Me.RecordsetClone
strSearchExp = "[FacilityID] = " & Me.OpenArgs
With rst
.MoveFirst
.Find strSearchExp
If Not .EOF Then
varBookMark = rst.Bookmark
Me.Bookmark = varBookMark
End If
End With
rst.Close
Set rst = Nothing
End If
End Sub

I have been able to confirm that the OpenArgs property is
being received by the form, but assigning the bookmark is
not working. I have also tried to use the DoCmd.GoToRecord
method to open the form at the desired record, but this
seems to result in the same problem. I am new to ADO, so I
suspect I am missing something simple, but I have not been
able to figure out where the problem is.

Any help will be greatly appreciated.

Tom

.

 

[BK]

Ahhh...ok. That makes sense. In fact it was because of that exact
problem that forced me (thankfully so) to move to "on the fly"
recordsets like that. It's also a whole hell of a lot better in terms
of network load and general efficiency of the db.

Thanks Bill,

You put me on the right track and I figured out the
problem:

The form is bound to a 12,000 row view, but when the code
creates the RecordsetClone the entire recordset has not
yet been scanned by Access; (the rst.RecordCount property
returns 50 when the code hits the .Find method), so
records at the end of the recordset are not included in
the search for a match for OpenArgs, and finding no match
the form opens at the first record of the recordset.

I've seen this behavior before, as when Access returns
partially calculated results for calculated controls as
large recordsets are loading, but I didn't know that
RecordsetClones exhibit the same problem. The immediate
fix is easy enough: just add a line of code before setting
the clone:

"DoCmd.GoToRecord acDataForm, Me.Form.Name, acLast"

This works ok for now, but I think your suggestion of
building the recordset on the fly is better in the long
run. It makes little sense to query so much data from the
server that Access cannot keep up with it... I've still
got to shake the bad habits I developed working with Jet
db's ;-)

Thanks again,

Tom

-----Original Message-----
How about throwing a Refresh in there. After you open

the form if you

press F9 and it shows up, then you'll know a refresh is

in order. If

that doesn't work, what I usually like to do is just set

the recordset

of the form on the fly.

So you would create the sql statement like (psuedo air-

code follows):

strSQL = "SELECT Stuff FROM Table WHERE FacilityID=" &
Me.OpenArgs

Docmd.OpenForm "MyForm"
Me.Recordset=strSQL

I usually like to set all my forms to dataentry and then

turn it off

after I set the recordsource, just to avoid a flashing

screen. But

that's more out of aesthetics than anything.

I'm pretty new to ado myself, so I could be way off base,

but I've

always opened recordsets with:

rst.Open strSQL, con

where rst is Dimmed as a NEW ADODB.Recordset and con is a
connection

object to the database.

Hope something in there helps.

Bill


Tom Lewis wrote:

I am using an OpenArgs property to pass a primary key
value to a form, and want to open the form with this
record active. When I try to do this, the form opens,

but

the bookmark to the specified record is ignored. Oddly,

if

I change the form to design view and then back again to
form view, the desired record is current when the form
opens.

The code I am using is:

Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Dim rst As ADODB.Recordset
Dim strSearchExp As String
Dim varBookMark As Variant
Set rst = Me.RecordsetClone
strSearchExp = "[FacilityID] = " & Me.OpenArgs
With rst
.MoveFirst
.Find strSearchExp
If Not .EOF Then
varBookMark = rst.Bookmark
Me.Bookmark = varBookMark
End If
End With
rst.Close
Set rst = Nothing
End If
End Sub

I have been able to confirm that the OpenArgs property

is

being received by the form, but assigning the bookmark

is

not working. I have also tried to use the

DoCmd.GoToRecord

method to open the form at the desired record, but this
seems to result in the same problem. I am new to ADO,

so I

been


.