How to go to last record when opening form

[OssieMac]

I have the following code to open a form. I want to go to the last record
when the form opens and I thought that it should be something like the
following after the form is opened but it does not work:

DoCmd.GoToRecord acDataForm, stDocName, , acLast


What code should I insert to go to the last record?

Private Sub Edit_Data_Click()

On Error GoTo Err_Edit_Data_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Product Details Ver1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Edit_Data_Click:
Exit Sub

Err_Edit_Data_Click:
MsgBox Err.Description
Resume Exit_Edit_Data_Click

End Sub

Thanks in advance for any help received.

 

[boblarson]

One quick question - Why are you wanting the "last" record and how are you
determining the last record? Do you have the form's recordsource as a query
sorted by either the primary key (if a number) or by a date/time stamp?

The reason I ask is that Access probably isn't storing the data the way you
might think because unless you impose some order, or have a numeric primary
key, the table isn't sorted in any meaningful order and you can't reliably
say that the last record is actually the last record.

So, what is it you really want. Are you just wanting to get to a new record?
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[Allen Browne]

After your OpenForm line:

If Not Forms(stDocName).NewRecord Then
RunCommand acCmdRecordsGotoNew
End If

 

[OssieMac]

Hi Bob,

Firstly, thanks for taking the time to answer. It is much appreciated.

The record source table has a primary key index which is a number. I want to
be able to identify the last record number used (not a new record).

My aim is to then add 30 numbers and then print a page of sticky labels for
the 30 new numbers added. (I think that I can achieve this but if I run into
problems then I might post some more questions).

Previously the numbers were created on the labels by hand and then simply
added individually as new records but unfortunately people make mistakes
writing them out by hand and it is a major task to correct them.

Since posting my question, I have found a workaround by putting the
following code in the form "On load" event:-

DoCmd.GoToRecord , , acLast

The above works fine. However, I would still like to know why I cannot
insert the code immediately after the following line when I open the form:-

DoCmd.OpenForm stDocName, , , stLinkCriteria

What am I missing or not understanding here?

I am reasonably competent in VBA for Excel but have a great deal to learn
about Access.

 

[Robert Morley]

The code you've written is correct as is. I suspect it may be a matter of
where/when you're running it. In theory, it should work if you put it right
after your OpenForm command, but instead of putting it in your
Edit_Data_Click event, try putting it in the form's Load event and see if
that makes any difference.


Rob

 

[OssieMac]

Thanks one and all for your replies. Robert Morley's suggestion of putting
the code in the form's Load event is the only way I can get it to work. (I
had said in my second post I had it working by doing that.)

Again thanks to everyone and I wish a Happy and Prosperous New year to all.

 

[Robert Morley]

Sorry, I had skimmed this post quickly and missed the fact that you'd
discovered the same resolution I proposed.

As to why you can't get it to work immediately after opening the form, it's
because Access is "slightly" multi-threaded and the form is being opened
asynchronously. You're issuing your command before the form recordset is
populated, so the "move to last record" command is useless at this point.
It would be an unreliable way of doing it, but if you put in some kind of a
delay loop after your OpenForm command, you'd probably find that the "move
last" would take effect normally if you delayed long enough.

A third way of doing this, which is less common, but useful in some
circumstances, would be to open the form by creating a new instance of it
instead of through OpenForm. I'm just writing this off the top of my head,
but the code would look something like this:

Private Sub Edit_Data_Click()
Dim f As [Form_Product Details Ver1]
'need square brackets because name has spaces

Set f = New [Form_Product Details Ver1]
DoCmd.GoToRecord acDataForm, f.Name, , acLast
End Sub

I *believe* that when you do it this way, the Set statement only returns
after the form is fully loaded. I might be wrong in that, though, in which
case the code gets a bit more complicated, and I won't confuse you with
facts unless you need them.

(To give you an outline, though, you either have to issue an f.Requery or
similar statement, which is redundant, but forces the form to fully
populate, or you have to use event sinking in order to catch the Form_Load
event outside of the form itself...possibly useful, but if you can do it in
the regular Form_Load event, then there's no reason to do it this way.)



Rob

 

[OssieMac]

Hi Rob,

Thanks for the added suggestion. However, it didn' twork but not to worry
because I have modified my code now to use the On load event and it works so
I don't intend to spend any more time on it. Prefer to get on with the rest
of the project.

--
Regards,

OssieMac

Sorry, I had skimmed this post quickly and missed the fact that you'd
discovered the same resolution I proposed.

As to why you can't get it to work immediately after opening the form, it's
because Access is "slightly" multi-threaded and the form is being opened
asynchronously. You're issuing your command before the form recordset is
populated, so the "move to last record" command is useless at this point.
It would be an unreliable way of doing it, but if you put in some kind of a
delay loop after your OpenForm command, you'd probably find that the "move
last" would take effect normally if you delayed long enough.

A third way of doing this, which is less common, but useful in some
circumstances, would be to open the form by creating a new instance of it
instead of through OpenForm. I'm just writing this off the top of my head,
but the code would look something like this:

Private Sub Edit_Data_Click()
Dim f As [Form_Product Details Ver1]
'need square brackets because name has spaces

Set f = New [Form_Product Details Ver1]
DoCmd.GoToRecord acDataForm, f.Name, , acLast
End Sub

I *believe* that when you do it this way, the Set statement only returns
after the form is fully loaded. I might be wrong in that, though, in which
case the code gets a bit more complicated, and I won't confuse you with
facts unless you need them.

(To give you an outline, though, you either have to issue an f.Requery or
similar statement, which is redundant, but forces the form to fully
populate, or you have to use event sinking in order to catch the Form_Load
event outside of the form itself...possibly useful, but if you can do it in
the regular Form_Load event, then there's no reason to do it this way.)



Rob

Hi Bob,

Firstly, thanks for taking the time to answer. It is much appreciated.

The record source table has a primary key index which is a number. I want to
be able to identify the last record number used (not a new record).

My aim is to then add 30 numbers and then print a page of sticky labels for
the 30 new numbers added. (I think that I can achieve this but if I run into
problems then I might post some more questions).

Previously the numbers were created on the labels by hand and then simply
added individually as new records but unfortunately people make mistakes
writing them out by hand and it is a major task to correct them.

Since posting my question, I have found a workaround by putting the
following code in the form "On load" event:-

DoCmd.GoToRecord , , acLast

The above works fine. However, I would still like to know why I cannot
insert the code immediately after the following line when I open the form:-

DoCmd.OpenForm stDocName, , , stLinkCriteria

What am I missing or not understanding here?

I am reasonably competent in VBA for Excel but have a great deal to learn
about Access.