Requery Form & Stay At Same Record

[Guest]

I have a form frmRenewals, in data sheet view. The first field for each
record is a checkbox, Select. After checking the box I tried to requery the
form and stay at the same record, after update, using the following:
Private Sub Select_AfterUpDate()
Me!Select.SetFocus
Me.Requery
End Sub
I tried to set the focus using the ID field for the same record thinking
that a unique identifier was need to set focus.

I need to stay at and requery the selected record because I double click on
another field, LastName, in the same record to open another form with further
details. This form needs to show the new changes from frmRenewals but I’m at
a loss.

Can anyone help?

Thanks
Nick

 

[Allen Browne]

Why do you need to requery the form after every change to every field? That
sounds really inefficient.

If you just need to save the record so that (for example) calculated fields
update, you can do that with:
Me.Dirty = False
or
RunCommand acCmdSaveRecord

If you really do need to requery to form (e.g. to find other records entered
by other users or programmatic processes), you will need to save the primary
key value into a variable, and then after the Requery, FindFirst on the
RecordsetClone of the Form and set the Bookmark.

 

[Guest]

Allen,
Thank you very much.
I didn’t realise that you could or needed to save a record. I was of the
understanding that all you had to do was requery the form and the entry would
be up-dated.
I used the RunCommand acCmdSaveRecord and that achieved exactly what I
wanted and makes sense.
Thanks again

Nick

 

[sencan777]

same record not first page

hi..
I really do need to requery to form using a botton..
I requery with a botton - Onclick event..is there an way to requery without press any button.?
when I run requery I go to first record..
I want to stay at same record..how will I do?

 

[Allen Browne]

1. Save the primary key value into a variable.
2. Requery.
3. FindFirst in the RecordsetClone of the form.

This example assumes an AutoNumber field named "ID":

Private Sub cmdRequery_Click()
Dim varID as Variant 'To save the primary key

If Me.Dirty Then 'Save First
Me.Dirty = False
End If
varID = Me.ID

Me.Requery

If IsNull(varID) Then 'Must have been a new record
RunCommand acCmdRecordsGotoNew
Else
With Me.RecordsetClone
.FindFirst "ID = " & varID
If .NoMatch Then
MsgBox "Disappeared."
Else
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

 

[Guest]

What if my primary key isn't an autonumber, how can I still do this.

 

[George Nicholson]

Actually, i think that code will work whether the field is an Autonumber or
not.

The code does assume a *numerical* Primary Key named ID, which *can* be an
Autonumber. (If it isn't an autonumber, ID will never be Null so the
RunCommand line will never run, but that causes no problems).

If your primary key is a text field, change the appropriate line to:

..FindFirst "ID = ' " & varID & " ' "
note: spaces between ' and " in the above are there for clarity & should be
removed, leaving you with:

..FindFirst "ID = '" & varID & "'"

HTH,


"Professional_in_need_of help"

 

[Guest]

Thanks that was the issue.

Actually, i think that code will work whether the field is an Autonumber or
not.

The code does assume a *numerical* Primary Key named ID, which *can* be an
Autonumber. (If it isn't an autonumber, ID will never be Null so the
RunCommand line will never run, but that causes no problems).

If your primary key is a text field, change the appropriate line to:

..FindFirst "ID = ' " & varID & " ' "
note: spaces between ' and " in the above are there for clarity & should be
removed, leaving you with:

..FindFirst "ID = '" & varID & "'"

HTH,


"Professional_in_need_of help"