Going to last record in subform (Access 2000)

Topic

New Reply

Hi,

I am trying to workout how to go to the last record in a subform.
I have used a macro to do this in a form but it appears not to work in a subform.
I would appreciate if you could tell me if what l am doing is possible.

This is done in Access 2000

Regards
Justin

Reply | Quote

Replies

Here is the technique I use. I’m assuming your subform control is named subMisc (the actual formname it contains is irrelevant). This code executes from the mainform.
me.subMisc.form.recordsetclone.movelast
me.subMisc.form.bookmark = me.subMisc.form.RecordsetClone.bookmark

Reply | Quote

Edited by HansV to reduce huge screenshot in size. It caused horizontal scrolling. Please keep attached images small! Thanks.

Hi Mark,

I tried to place this code in the form but had a few problems in doing so.
See attachment for my queries.

Justin

Reply | Quote

If you want to use a command button on the main form to go to the last record in the subform, the code goes into the On Click event of the command button.

By the way, do yourself a BIG favor and give your controls more meaningful names than Command37, Text19 and List8. If you or somebody else has to perform maintenance on the database in the future, you will save a lot of time if the name of a control says something about its purpose. A convention used by many developers is to use a three letter prefix, such as cmd for command button, txt for text box, lst for list box etc., followed by a descriptive name without spaces in which each word starts with an upper case letter. The command button in question could be named cmdGoToLast, for example.

The On Click code could look like this, if the name of the subform as a control on the main form is sbfSomething:

Private Sub cmdGoToLast_Click()
Me.sbfSomething.SetFocus
DoCmd.GoToRecord , , acLast
End Sub

Reply | Quote

Edited by HansV to reduce huge screenshot in size. It caused horizontal scrolling. I repeat: please keep attached images small! Thanks.

I might not have explained myself properly.
What l would like is to go the last record automatically and not on a command button.
I have used the code you specified but still have problems.
If l used a control then the user would have to click on it every time he enters the form.
Is is possible to make this happen automatically?

See attachment.

Regards
Justin

Reply | Quote

The screenshot you posted (please don’t post 1024 by 768 images, they cause horizontal scrolling) shows that you have just created an On Click event for the form as a whole. That is probably not what you want.

If you want to activate the last record in the subform automatically, you can do it in the On Current event of the main form. When the user opens the main form, or moves to another record, this event will be fired.

• Open the main form in design view.
• Activate the Event tab of the Properties window.
• Click in the On Current event.
• Select Event Procedure from the dropdown list.
• Click the builder button, i.e. the three dots … to the right of the dropdown arrow.
• Make the code look like this:

  Private Sub Form_Current()
  Me.subformname.SetFocus
  RunCommand acCmdRecordsGoToLast
  End Sub

  where subformname must be replaced by the name of the subform as a control on the main form. You can find this name by clicking once on the subform in the main form (in design view), and looking at the caption of the Properties window. This name can be different from the name that the subform has in the database window.

• Close and save the form.
  [/list]

Reply | Quote

I tried this but got an error, see attachment.

Regards
Justin

Reply | Quote

You seem to be writing the code in a general module, not in the module behind the form. And there is no space after Sub.

Please read my post again. I provided very detailed instructions.

Reply | Quote

I am sorry l don’t understand what you mean.

I followed the instructions several times over and over again but everytime appear to be in general mode.
I am a bit confused as to what l am doing wrong.
Please can you explain where l am going wrong?

Many Thanks
Justin

Reply | Quote

The code in your last screenshot shows

Private SubcmdGotoLast_Click()

Even with the missing space inserted, this has nothing to do with the instructions I posted. The instructions are very detailed, I would not know how to make it still clearer than that. Sorry.

Reply | Quote

The code in your last screenshot shows

Private SubcmdGotoLast_Click()

Even with the missing space inserted, this has nothing to do with the instructions I posted. The instructions are very detailed, I would not know how to make it still clearer than that. Sorry.

Reply | Quote

I am sorry l don’t understand what you mean.

I followed the instructions several times over and over again but everytime appear to be in general mode.
I am a bit confused as to what l am doing wrong.
Please can you explain where l am going wrong?

Many Thanks
Justin

Reply | Quote

You seem to be writing the code in a general module, not in the module behind the form. And there is no space after Sub.

Please read my post again. I provided very detailed instructions.

Reply | Quote

I tried this but got an error, see attachment.

Regards
Justin

Reply | Quote

The screenshot you posted (please don’t post 1024 by 768 images, they cause horizontal scrolling) shows that you have just created an On Click event for the form as a whole. That is probably not what you want.

If you want to activate the last record in the subform automatically, you can do it in the On Current event of the main form. When the user opens the main form, or moves to another record, this event will be fired.

• Open the main form in design view.
• Activate the Event tab of the Properties window.
• Click in the On Current event.
• Select Event Procedure from the dropdown list.
• Click the builder button, i.e. the three dots … to the right of the dropdown arrow.
• Make the code look like this:

  Private Sub Form_Current()
  Me.subformname.SetFocus
  RunCommand acCmdRecordsGoToLast
  End Sub

  where subformname must be replaced by the name of the subform as a control on the main form. You can find this name by clicking once on the subform in the main form (in design view), and looking at the caption of the Properties window. This name can be different from the name that the subform has in the database window.

• Close and save the form.
  [/list]

Reply | Quote

Edited by HansV to reduce huge screenshot in size. It caused horizontal scrolling. I repeat: please keep attached images small! Thanks.

I might not have explained myself properly.
What l would like is to go the last record automatically and not on a command button.
I have used the code you specified but still have problems.
If l used a control then the user would have to click on it every time he enters the form.
Is is possible to make this happen automatically?

See attachment.

Regards
Justin

Reply | Quote

If you want to use a command button on the main form to go to the last record in the subform, the code goes into the On Click event of the command button.

By the way, do yourself a BIG favor and give your controls more meaningful names than Command37, Text19 and List8. If you or somebody else has to perform maintenance on the database in the future, you will save a lot of time if the name of a control says something about its purpose. A convention used by many developers is to use a three letter prefix, such as cmd for command button, txt for text box, lst for list box etc., followed by a descriptive name without spaces in which each word starts with an upper case letter. The command button in question could be named cmdGoToLast, for example.

The On Click code could look like this, if the name of the subform as a control on the main form is sbfSomething:

Private Sub cmdGoToLast_Click()
Me.sbfSomething.SetFocus
DoCmd.GoToRecord , , acLast
End Sub

Reply | Quote

Edited by HansV to reduce huge screenshot in size. It caused horizontal scrolling. Please keep attached images small! Thanks.

Hi Mark,

I tried to place this code in the form but had a few problems in doing so.
See attachment for my queries.

Justin

Reply | Quote

Here is the technique I use. I’m assuming your subform control is named subMisc (the actual formname it contains is irrelevant). This code executes from the mainform.
me.subMisc.form.recordsetclone.movelast
me.subMisc.form.bookmark = me.subMisc.form.RecordsetClone.bookmark

Reply | Quote