Open form or database at last entry or used area

[Katrina]

I am needing a form that I work with to open at the last 'record' that was
last edited. If I am in the middle of 'cleaning or editing' and leave for
the day, come back the next work day, and open up my form, it would be nice
to open it at the last record 'touched'.

I hope there is an anwer out there.

 

[scubadiver]

Off the top of my head you could think about having a field with a date and
time stamp that is updated by changes in any other field to the record.

In the update event for each field you could put

datetimefield = now()

 

[Katrina]

In the actual table, do I create a new column for this info? or where.

 

[Ken Sheridan]

Yep. Add a column, TimeStamp say, of date/time data type to the table. Then
in the form's BeforeUpdate event procedure put:

Me.TimeStamp = Now()

and in its Open event procedure put:

Dim rst As Object
Dim dtmLatestTime As Date
Dim strCriteria As String

Set rst = Me.Recordset.Clone

dtmLatestTime = DMax("TimeStamp", "YourTable")

strCriteria = "TimeStamp = #" & _
Format(dtmLatestTime, "mm/dd/yyyy hh:nn:ss") & "#"

With rst
.FindFirst strCriteria
If Not .NoMatch Then
Me.Bookmark = rst.Bookmark
End If
End With

substituting the real name of your table in place of 'YourTable' in the above.

The TimeStamp column's value will be set to the current date and time when
you change a record. Note that 'change' also includes changing data to its
current value, or changing it to a different value and then back again. It
doesn't necessarily mean the values themselves have changed. There are ways
of detecting if the values in the fields have actually changed. This
involves storing the current values temporarily in an array and then storing
the new values temporarily in another array, and finally comparing the values
in the two arrays. If you are interested I can post the code, and how to
call it, here.

The code in the form's Open event procedure looks up the latest TimeStamp
value in the table by means of the DMax function, and then goes to that
record in a clone of the form's underlying recordset. It then synchronizes
the form's Bookmark with the recordset's Bookmark, which causes the form to
move to the record in question.

Ken Sheridan
Stafford, England

 

[BruceM]

Allen Browne describes a way of doing exactly what you want:
http://allenbrowne.com/ser-18.html

 

[Katrina]

Okay, newbie. where is the place to put in the 'unload' I cannot find
'unload' anywhere.

 

[BruceM]

Open the form in design view. Click View >> Properties. Click the Events
tab, and click Unload. Click the three dots that appear to the left of the
Unload line. Click Code Builder, OK. The VBA editor should open, and you
should see something like:

Private Sub Form_Unload(Cancel As Integer)

End Sub

Place the code between those lines. Substitute your primary key field for
CustomerID.

Create the Load event the same way, but there will be no Cancel as Integer
text in the first line.

 

[Katrina]

That's the thing. I have Before update and After update fields, no UNLOAD
field

 

[BruceM]

Back up. Close Access. Open your database. The first thing you see is
most likely the database window, which lists "Objects" on the left side, and
the names of the objects on the right. (If you don't see it, press F11.)
Click Forms on the left, click your form to select it, then click Design.
There are other options for going to design view, so use another way if you
prefer.
The form shoud be open in design view. Click View >> Properties. This
opens what is known as the Property Sheet. The Property Sheet should say
"Form" at the very top. If it does not, click the square at the top left
where the vertical and horizontal rulers meet. This square has a smaller
dark square inside it.
Now try clicking the Events tab.
When you are looking at properties, check the title bar at the top of the
Property Sheet to be sure you are looking at the correct properties.
BTW, Before Update, After Update, etc. are Events. Fields are where the
data are stored in tables. There is some information about terminology and
so forth here:
http://allenbrowne.com/tips.html
In particular, look at the Tips for Casual Users. There is a lot of good
information there.

 

[Katrina]

I'm sorry. I did all that. and still nothing. I am frustrated.

 

[BruceM]

What do you mean by "nothing"? Are you working with a form, or are you
trying to work with a report or a table? You need to work with a form. A
report does not have a Load or Unload event, and a table doesn't have events
at all.
Were you able to open the form in design view? Did you open the property
sheet, and did it say "Form" at the top? Did you click the Events tab? Did
you see "On Load" (the twelfth row from the top on the Events tab in Access
2003)? How about On Unload (two below Load)? What happened when you
clicked it? If you saw three dots to the right, what happened when you
clicked those?
I have presented you with a series of steps. If I am to help I need to know
how far you got, and at what point in the process "nothing" happened.

 

[scubadiver]

Hello,

How are you getting on?