Trouble with DoCmd.GoToRecord

  • Thread starter Thread starter Tom Lewis
  • Start date Start date
T

Tom Lewis

I am new to ADO and am having trouble finding why the
following code does not work in my Access 2002 project:

Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Dim strFacilNum As String
strFacilNum = Me.OpenArgs
Dim rst As ADODB.Recordset
Set rst = Me.RecordsetClone
rst.Find "FacilityID = '" & strFacilNum & "'"
DoCmd.GoToRecord acDataForm, _
Me.Form.Name, _
acGoTo, _
rst.AbsolutePosition
End If
End Sub

When this runs I get an error that argument 4 has an
invalid value, which is because the Offset value
of "rst.AbsolutePosition" resolves to a value of negative
3. I cannot understand this; the position of the found
record is actually record 11347 of 11349, and I would
expect that the Offset would resolve to 11347. But since
the desired record is third from the end of the recordset
I suspect that the AbsolutePosition is counting backwards
from the end of the recordset. I have tried using
MoveFirst methods and specifying a forward search, but
none of this changes the result.

Any suggestions?

TIA,

Tom
 
Tom Lewis said:
I am new to ADO and am having trouble finding why the
following code does not work in my Access 2002 project:

Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Dim strFacilNum As String
strFacilNum = Me.OpenArgs
Dim rst As ADODB.Recordset
Set rst = Me.RecordsetClone
rst.Find "FacilityID = '" & strFacilNum & "'"
DoCmd.GoToRecord acDataForm, _
Me.Form.Name, _
acGoTo, _
rst.AbsolutePosition
End If
End Sub

When this runs I get an error that argument 4 has an
invalid value, which is because the Offset value
of "rst.AbsolutePosition" resolves to a value of negative
3. I cannot understand this; the position of the found
record is actually record 11347 of 11349, and I would
expect that the Offset would resolve to 11347. But since
the desired record is third from the end of the recordset
I suspect that the AbsolutePosition is counting backwards
from the end of the recordset. I have tried using
MoveFirst methods and specifying a forward search, but
none of this changes the result.

Any suggestions?
Click to expand...

I'll state up front that I haven't tried to figure out exactly what's
going on here. But if I were writing this I'd probably move it to the
Load event and do something like

'---- start of suggested code ----
Private Sub Form_Open(Cancel As Integer)

Dim rst As ADODB.Recordset

If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
.Find "FacilityID = '" & Me.OpenArgs & "'"
If Not .EOF Then
Me.Bookmark = .Bookmark
End If
End With
End If

End Sub

'---- end of suggested code ----
 
-----Original Message-----


I'll state up front that I haven't tried to figure out exactly what's
going on here. But if I were writing this I'd probably move it to the
Load event and do something like

'---- start of suggested code ----
Private Sub Form_Open(Cancel As Integer)

Dim rst As ADODB.Recordset

If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
.Find "FacilityID = '" & Me.OpenArgs & "'"
If Not .EOF Then
Me.Bookmark = .Bookmark
End If
End With
End If

End Sub

'---- end of suggested code ----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
Thanks, Dirk

You're right, the OnLoad event makes more sense than
OnOpen, but when I use your code in either event I get a
new error: "Run-time error '2001': You cancelled the
previous operation." This error occurs when the code
reaches "Me.Bookmark = .Bookmark in the code, but if I
debug and select 'Continue' the procedure completes
without a problem and the form opens to the correct record.

Do you know what this error implies? I seem to remember
getting it with something like using system reserved names
or undefined variables, but I don't see a problem here.

This, combined with the problem described in my initial
post and the fact that I have had other odd problems and
crashes with this project, makes me suspect there may be
some corruption in the adp file. I would love to find a
explanation/fix to this issue so I won't feel compelled to
start re-constrcting everything from scratch, only to find
afterwards that a simple error in my code was the problem
all along.

Thanks for your help,

Tom
 
Oops, I should have changed that to "Private Sub Form_Load()" -- but I
trust you fixed that and it's not the cause of your problem.
You're right, the OnLoad event makes more sense than
OnOpen, but when I use your code in either event I get a
new error: "Run-time error '2001': You cancelled the
previous operation." This error occurs when the code
reaches "Me.Bookmark = .Bookmark in the code, but if I
debug and select 'Continue' the procedure completes
without a problem and the form opens to the correct record.

Do you know what this error implies? I seem to remember
getting it with something like using system reserved names
or undefined variables, but I don't see a problem here.
Click to expand...

It could be caused by a number of problems, unfortunately. Do you have
any other code in the form's Open and Load events? I trust that
FacilityID is in fact a text (char, varchar, nvarchar) field?

I mostly work in MDBs, not ADPs, so I can't readily test the code. The
following DAO equivalent works fine in an MDB where I tested it:

Private Sub Form_Load()

Dim rst As DAO.Recordset

If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
.FindFirst "ID = " & Me.OpenArgs
If Not .EOF Then
Me.Bookmark = .Bookmark
End If
End With
End If

End Sub
This, combined with the problem described in my initial
post and the fact that I have had other odd problems and
crashes with this project, makes me suspect there may be
some corruption in the adp file. I would love to find a
explanation/fix to this issue so I won't feel compelled to
start re-constrcting everything from scratch, only to find
afterwards that a simple error in my code was the problem
all along.
Click to expand...

I have heard of error 2001 (makes you think of Hal, doesn't it?) showing
up unexplainedly in corrupt databases, but I wouldn't necessarily jump
to the conclusion that it's caused by corruption in this case. I'd try
to eliminate all other possible causes first. If that fails, you could
create an exact duplicate of the form from scratch, and see if it gives
the same error.
 
You nailed it. The data type of FacilityID is an integer,
not string data type, and this seems to explain the
behavior the form has had. I would have caught it but got
too caught up thinking there was some dark and mysterious
problem lurking inside Access. Its much easier to deal
with a senior moment on my part...

All I need to do is remove the quotes around the OpenArgs
expression and I'm back in business.

Thanks a lot for your help.

Tom
 
Back
Top