Question about Requery

[Ruthless Artist]

I have a table with over 800 records. I made the query to only show "Is
Null" in the DateComplete in record of the Table. When I made the form I
made a button "Date Complete" which give the DateComplete.Value = Now(), but
I have to make it Requery so the record won't show up. Then if im in the
middle of the records in the Form and I hit Date Complete I go all the way
back to the begenning. Is there a way to make go back to the record # when
it requeries or is there a way I can create a duplicate on my Form of the
Navigation Buttons.

Thanks.

 

[Allen Browne]

Yes, Requerying a form reloads all the records, and so you find yourself at
the first one - just as if you had just opened the form.

You can save the primary key value in a variable, perform the requery, and
then find that record again. You will need some understanding of VBA code to
do this.

Dim varID As Variant
Dim strWhere As String

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If

varID = Me.[ID]
Me.Requery
If IsNull(varID) Then 'Must have been at the new record.
If Not Me.NewRecord Then
RunCommand acCmdRecordsGotoNew
End If
Else
strWhere = "[ID] = " & Me.[ID]
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Disappeared."
Else
Me.Bookmark = .Bookmark
End If
End With
End If

Notes
====
1. Replace [ID] with the name of your primary key field.

2. If your primary key is a text field, you need extra quotes:
strWhere = "[ID] = """ & Me.[ID] & """"

3. If the record you were on is gone, and you want the next record (assuming
the form is sorted by ID), use:
strWhere = "[ID] >= " & Me.[ID]

4. Add error handing. (E.g. the attempt to save the record could fail if a
required field is not filled in.)