Goto Current Record problem

[Chas Large]

Hi I have a simple problem with my database that I cannot find a simple
answer to.

I have a button which invokes the VB code

RecNum = CurrentRecordNumber
Me.FilterOn = False
DoCmd.GoToRecord , , acGoTo, RecNum

This gives debug errors as the RecNum is not in fact the current record
number but a value from field in the current record.

If I use acGotoLast or acGotoPrev etc the code works.

I know that the code I need requires me to calculate an offset to add to the
acGoTo so that if I have say 20 records and want to go to the 15th RecNum
should = 15.

Now what I need is to be able to determine what the current record count is
so I can use this as the offset.

Why go to all this trouble I hear you ask, well this all comes from a form
where the user searches for a record via a filter, the trouble is that when
the filter is closed the records in the form jumps back to the beginning.

I just need to be able to jump immediately back to the last record that was
viewed/edited.

Hope this explanation makes sense.

--
Cheers
Chas

***************
* Spectrum is Green *
***************

 

[Len]

Chas;
Your actual record number can change, but your primary key cannot.
I would store your primary key when you activate your VBA script. Then
write a SQL statement to fetch that record. If you need to count records
then use the ORDER By when opening your table.

Example Set rst = db.openrecordset("SELECT field names seperated by commas
FROM name of table WHERE stored variable name = Field Name in table ORDER BY
desired sequence of table")

Good Luck

Len

 

[missinglinq via AccessMonster.com]

The problem is that in VBA there is no such thing as "CurrentRecordNumber!"
It should be simply *CurrentRecord*

RecNum = CurrentRecord
Me.FilterOn = False
DoCmd.GoToRecord , , acGoTo, RecNum

 

[Chas Large]

Hi Len,
I probably didn't explain too clearly before. Please forgive an oldie.
I already can do a search for a record as you suggest. using a strWhere etc,
this activates a filter and causes the found record to be available for
editing as required. The problem is when I deactivate the filter (with a
button or via the RECORDS menu) the record display returns to the first
record in the dB. I just need the same record to remain open so the user can
browse to the next or previous record to the one they have just edited.

What I was trying to do was this.
Click a button to:
Note the record number or a key field
Close the filter
Move the record back to that same record number/key field.

I have also been experimenting with
DoCmd.GoToControl "txtFieldofKeyRecord"
DoCmd.FindRecord txtFieldofKeyRecord
Me.txtFieldofKeyRecord.SetFocus
where the field txtFieldofKeyRecord contains a key number for the record
(typed in manually for testing)
But this also throws up errors.

It seems strange that there is no Goto last viewed record function like
there is GoToLast or GoToPrev etc.

Hope this gives a better explanation.
--
Cheers
Chas

***************
* Spectrum is Green *
***************