Returning to previous record (if exists) instead of returning to the first record in a form after re

[Amir]

Hi,

I'm using an ADP project with SQL Server 7 back-end.

In the ADP I have a continuous form in which near each record there is a
'hide' button which does the following:
1. Runs a stored procedure to update the 'IS_SHOWN' culomn of the current
record to 0 value (It doesn't change values in records other than the
current record).
2. Requries the form.

This causes the current record to 'disappear' from the form, since the form
record source of the form (It's a SELECT statement in a stored procedure)
has a condition of (IS_SHOWN = 1) in it's WHERE clause.

This works fine.

What I wish to do is that after the requery action, the focus will be set to
one record above the 'disappeared' record, instead of setting the focus to
the first record in the form, like it does today due to the requery command.
This should take in care (in order to prevent error messages) that sometimes
the form will remain with no records, in cases where the 'disappeared'
record was the only record shown when the user clicked the 'disappear
record'.

How can I do that part of setting the focus to the 'previous record' (if
exists) after the requery?

Regards,
Amir.

 

[Arvin Meyer [MVP]]

Add a variable and set it equal to the Primary key of the record at the
start of your code. After the requery, do a find using the variable, and
move to that value.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Amir]

Thanks for the response, Arvin.

Your solution is good for cases where the record remains in the form after
the requery, but my code does exactly the opposite: it changes the value of
the IS_SHOWN field/column of the selected record, so that after the requery,
that record isn't shown on the form because the form's record source has the
following WHERE condition: IS_SHOWN = 1. Since my code changes the value of
the IS_SHOWN field of the selected record to 0, the active record should not
more appear in the form after the requery (That's the purpose of clicking
the 'disappear' button).

Therefore, what I wish to do is not to find the record that was active when
the user clicked the 'disappear' button, but rather to find the record
'above' it (if exists), instead of the focus be returned to the first record
in the form. What I don't know how to do is how to retrieve the PK of the
record which is 'above' the current record in the form.

Regards,
Amir.

 

[Arvin Meyer [MVP]]

If you are using a sequential autonumber (as opposed to a random number) and
you have a normal sort, you could simply subtract 1 from the key. If the
sort isn't normal, or you have applied a filter, or you have already deleted
the previous record, that won't work. In that case, build a new recordset
that mirrors the one in the form and move to the same record as the one in
the form, then do a MovePrevious.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Amir]

Thanks Arvin. That's exactly what I'm trying to do.
By far I've checked the solution only on a test mdb file (not on the SQL
Server), and it's working fine, but I still have a small issue to solve.
Here is the button's code:

'---------------------------------------------------------
'Variables declaration
Dim cnxn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim fld As ADODB.Field
Dim lngCurrentRecordID As Long
Dim lngPreviousRecordID As Long

'Open the connection
Set cnxn = CurrentProject.Connection

'Set current record's ID value into lngCurrentRecordID
lngCurrentRecordID = Me.ID

'Open current form's recordset
rs.Open "SELECT * FROM Table1", cnxn, adOpenDynamic, adLockPessimistic

If rs.BOF Or rs.EOF Then 'There are no records in the form's recordset
lngPreviousRecordID = -1
Else 'There are records in the form's recordset
'Find current record in recordset
rs.Find "ID = " & lngCurrentRecordID, 0, adSearchForward
'Move to the previous record
rs.MovePrevious
If rs.BOF Then 'Active record was the first record in the recordset
lngPreviousRecordID = -1
Else
lngPreviousRecordID = rs.Fields("ID").Value
End If
End If

'Close recordset
rs.Close

'The SQL 'disappearing' stuff goes here

'Form requery makes the record 'disappear'
Me.Requery

'Set focus to ID control in order to able to
'use 'acCurrent' with the DoCmd.FindRecord command.
Me.ID.SetFocus

'Go to record near the 'disappeared' record
If lngPreviousRecordID > 0 Then
DoCmd.FindRecord lngPreviousRecordID, acEntire, True, acSearchAll, ,
acCurrent
Else
'Do nothing. Requery will cause the first record (if exists) to get
focus.
End If
'---------------------------------------------------------

The issue that I don't know how to solve is that in order to use that
solution, the 'ID' field must be shown on the form. If I delete the ID
control or makes it invisible, I get error 2110: Microsoft Access can't move
the focus to the control ID.

Is there any way of setting the focus to the right record on the form
without having the ID field to be shown?

I thought about a little 'trick' of doing that: Making the ID control
dimensions very small, then hiding it 'behind' one of the existing controls
(or manipulating it's colors to meet form's background etc.), but is there a
smarter or a generic way of doing such a task?

Kind Regards,
Amir.

 

[Arvin Meyer [MVP]]

The issue that I don't know how to solve is that in order to use that
solution, the 'ID' field must be shown on the form. If I delete the ID
control or makes it invisible, I get error 2110: Microsoft Access can't move
the focus to the control ID.

Is there any way of setting the focus to the right record on the form
without having the ID field to be shown?

I thought about a little 'trick' of doing that: Making the ID control
dimensions very small, then hiding it 'behind' one of the existing controls
(or manipulating it's colors to meet form's background etc.), but is there a
smarter or a generic way of doing such a task?

It must be on the form but it can have its Visible property set to False.
You cannot set focus to an invisible control, but you can read its value.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Amir]

OK, thank you very much!

It must be on the form but it can have its Visible property set to False.
You cannot set focus to an invisible control, but you can read its value.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access