Absolute position in current subform

[rbm]

I have an Access97 form with a subform (subD1W) that displays a number of
values from a query. The query name is "qryD1W". The values are displayed as
a continuous form. When I change one of the values in the subform, I want to
determine the recordnumber (absoluteposition) of the value I just changed.
This will direct other functions.

I had it working in an Access2000 database, but when I converted it back to
Access97, it stopped. In Access2000, when I changed one of the values, the
value field AfterUpdate event worked correctly. It was simply:

lngPos = Me.Recordset.AbsolutePosition
If lngPos = ..... Then .... etc.

In Access97, the same code gets a "method or data member not found" error
message and highlights ".Recordset"

In the Access2000 database, I had not Dim'd the Recordset, but that seems to
be the stopper now.

Any help would be greatly appreciated.

 

[Douglas J. Steele]

Try

Me.RecordsetClone.AbsolutePosition

 

[rbm]

Thanks, but I tried that too. I think that it gets back to the fact that I
did not use something like:

Dim rs as Recordset
Set rs as .......

I usually just refer to open and current forms and don't really understand
the part about declaring objects and variables (if declaring is the correct
term). I tried things like:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = Me.RecordsetClone

Me.RecordsetClone.AbsolutePosition

I also tried setting the recordset to the underlying query.
Set rst = db.OpenRecordset("qryD1W")

I think I need some remedial Recordset trainng.

 

[rbm]

The RecordsetClone works - to a degree. It doesn't give me an error, but it
continues to return a value of zero (0). I have tried putting a procedure in
a button on the subform that, on click, says "MsgBox
Forms!D1W.RecordsetClone.AbsolutePosition" and I get 0. I tried a similar
button on the parent form and get the same response. I even tried the same
thing with a field on the subform in an OnClick event and in an OnChange
event.

Still no luck.

 

[Dirk Goldgar]

The RecordsetClone works - to a degree. It doesn't give me an error, but
it
continues to return a value of zero (0). I have tried putting a procedure
in
a button on the subform that, on click, says "MsgBox
Forms!D1W.RecordsetClone.AbsolutePosition" and I get 0. I tried a similar
button on the parent form and get the same response. I even tried the
same
thing with a field on the subform in an OnClick event and in an OnChange
event.

Still no luck.

You may need to ensure that the form's RecordsetClone is on the same record
as the form itself. For code running on the form (subform) itself, try
this:

With Me.RecordsetClone
.Bookmark = Me.Bookmark
lngPos = .AbsolutePosition
End With

Though I'm not sure what's wrong with just Me.CurrentRecord (though that is
1-based, rather than 0-based).

Incidentally, it's not generally a good idea to think in terms of "record
numbers".

 

[Betadine Spain]

Ey! Im in the same Chapter!
I have NavigateButtons in SubForm controlling the parent Form...and AbsolutePosition returns 0
I have spend the all day in this line
So I was writing to you offering an alternative way (without using more than one line of code) but suddenly I dont know what I have do...that ... it was very hidden and stupid solution

I always get returns of 0 in Me.Parent.RecordsetClone.AbsolutePosition
but in
Me.Parent.RecordsetClone.RecordCount all worked ok.

So Whats the trick?
Remove Clone... So...
Me.Parent.Recordset.AbsolutePosition
Me.Parent.Recordset.RecordCount
And it works for me.

Hope you get working with that! Cheers!

 

[Dirk Goldgar]

in message

Ey! Im in the same Chapter!
I have NavigateButtons in SubForm controlling the parent Form...and
AbsolutePosition returns 0
I have spend the all day in this line
So I was writing to you offering an alternative way (without using more
than one line of code) but suddenly I dont know what I have do...that ...
it was very hidden and stupid solution

I always get returns of 0 in Me.Parent.RecordsetClone.AbsolutePosition
but in
Me.Parent.RecordsetClone.RecordCount all worked ok.

So Whats the trick?
Remove Clone... So...
Me.Parent.Recordset.AbsolutePosition
Me.Parent.Recordset.RecordCount
And it works for me.

Hope you get working with that! Cheers!

That was the first thing that occurred to me, too, but in the original post,
it was mentioned that this is Access 97. In Access 97, the Recordset of the
form is not directly accessible -- there is no Recordset property.

 

[rbm]

The code that I used that finally worked is:

==============================
Private Sub W_AfterUpdate()
'W is a field in the subform
.. . .

Dim lngPos As String
Me.RecordsetClone.Bookmark = Me.Bookmark
lngPos = CStr(Me.RecordsetClone.AbsolutePosition + 1)

Select Case lngPos
Case 1
Parent.W1 = Me.W
Case 2
Parent.W2 = Me.W
. . . . .
==============================
I don't really rely on the record numbers. I am trying to update other
fields in the main form (fields W1, W2, W3, etc) with values that the user
enters into the subform (field W). If they change a value in the subform, I
want it to be reflected in the main form fields.

Thank you all for your input. I find that there is less and less info
available on Access97 these days. My original program was developed in 97
and I hate to change to a later version with more overhead and baggage -
plus, other than these "enhancements" I can't seem to stop making, it works.

 

[Dirk Goldgar]

The code that I used that finally worked is:

==============================
Private Sub W_AfterUpdate()
'W is a field in the subform
. . .

Dim lngPos As String
Me.RecordsetClone.Bookmark = Me.Bookmark
lngPos = CStr(Me.RecordsetClone.AbsolutePosition + 1)

Select Case lngPos
Case 1
Parent.W1 = Me.W
Case 2
Parent.W2 = Me.W
. . . . .

That's pretty much what I recommended, but why on Earth are you defining
lngPos as String, and going to the trouble of converting the position to a
string, when after all that your Case structure is checking for numeric
values? Why not just:

Dim lngPos As Long

With Me.RecordsetClone
.Bookmark = Me.Bookmark
lngPos = .AbsolutePosition + 1
End With

Select Case lngPos
Case 1
Parent.W1 = Me.W
Case 2
Parent.W2 = Me.W
' . . . . .

??

I find that there is less and less info
available on Access97 these days. My original program was developed in 97
and I hate to change to a later version with more overhead and baggage -
plus, other than these "enhancements" I can't seem to stop making, it
works.

Access 97 was terrific. If it still suits your needs, why change? And
there are an awful lot of us who post in these newsgroups who are familiar
with 97. I don't think I have a copy installed anymore, though I used to
keep the help file installed on my old computer, just so I'd have a useful
reference (even for later versions).

 

[rbm]

The only reason I used a sting is because the code that worked, at the time,
wanted a string ("CStr..."). I originally had it as Long or integer. I
guess I just stopped messing with it when I got it to work. Thanks a lot for
your input. It is greatly appreciated.