Go to last record on subform

[Gasman]

Hi all,

I have a form with an unbound combo box
The subform is linked to the combobox value

The subform records are going to grow in number with the last always at the bottom with an empty field [processed] as I wish to show them in date ascending order, and only the last 5 records will need to be amended.

Consequently i would like to go to the last record on loading the form and any change in the combobox.

From http://www.access-programmers.co.uk/forums/showthread.php?t=128815&page=2 ia have tried

Me.YourSubformContainerName.Form.Recordset.MoveLast

where YourSubformContainerName is named Commission

That does not work. there is a delay

I then found

Me.Commission.SetFocus
DoCmd.GoToRecord , , acLast

but I get an error on the DoCmd stating 'You can't go to the specified record.'

I have this structure in a few forms and would like it to happen in all all them, but how do I do this please?

TIA

 

[Minty]

This code

I then found

Me.Commission.SetFocus
DoCmd.GoToRecord , , acLast

but I get an error on the DoCmd stating 'You can't go to the specified record.'

Although you have set focus on the subform, you are still (as far as you code is concerned) trying to go to the last record of your unbound main form.

Try this as it simulates doing the menu command ;

Me.Commission.SetFocus
DoCmd.RunCommand accmdRecordsGoToLast

 

[missinglinq]

Is your Main Form Bound..i.e. does a different Record appear on it when a selection is made from the Combobox? This will do what you want when the Form opens and each time a different Record appears on the Main Form:

In the Main Form:

Private Sub Form_Current()
  SubFormControlName.SetFocus
End Sub

Private Sub SubFormControlName_Enter()
  DoCmd.GoToRecord , , acLast
End Sub

Note that SubFormControlName may or may not be the same as the name of the Form the Subform is based on.

Linq ;0)>

 

[Gasman]

This code

Although you have set focus on the subform, you are still (as far as you code is concerned) trying to go to the last record of your unbound main form.

Try this as it simulates doing the menu command ;

Me.Commission.SetFocus
DoCmd.RunCommand accmdRecordsGoToLast

Thank you Minty,

I had hardly a chance to test this before I left work tonight and win10 wanted to reboot and update my PC.

I am not sure if this is also causing a problem.

I have conditional formatting on the fields. If the Processed date is not null, then I protect all the controls. In this scenario it appears to do something, but does not move to the last record in the form. If the data is editable (processed id NULL) then it does? However I would like to go to the bottom regardless of whether the data is processed or not.

I will test more tomorrow.

 

[Gasman]

Is your Main Form Bound..i.e. does a different Record appear on it when a selection is made from the Combobox? This will do what you want when the Form opens and each time a different Record appears on the Main Form:

In the Main Form:

Private Sub Form_Current()
  SubFormControlName.SetFocus
End Sub

Private Sub SubFormControlName_Enter()
  DoCmd.GoToRecord , , acLast
End Sub

Note that SubFormControlName may or may not be the same as the name of the Form the Subform is based on.

Linq ;0)>

missinglinq,

The main form is unbound. It only holds the combobox for selecting an employee. The subform then shows the records for that employee.
Each set of records is processed each week and a date of processing uodated to the record. When that field is populated I protect all the other fields via Conditional formatting so that they cannot be amended. Basically the data is done.

I suppose I could just order the data date descending and leave the form at the top. Will try that tomorrow as well

 

[Minty]

If the Processed date is not null, then I protect all the controls.

How are you doing this? - if you set the whole sub form to locked or disable edits it has some strange effects on what does and doesn't work.

 

[Gasman]

How are you doing this? - if you set the whole sub form to locked or disable edits it has some strange effects on what does and doesn't work.

Hi Minty,

That it does.

My intention is to protect all the controls once the record has been 'processed', that is a having a date in the Processed field.

I have just removed dates from one employee's set of records and it works as expected.
However for an employee where everything has been processed, it appears to try, but does not move to the last record of the subform.

 

[Minty]

Set a tag on the controls you want locked on the sub form "ProgLock" or similar, then on the current event of your form loop around the individual controls that are set to tag "ProgLock" and lock / unlock them appropriately. Something like this (aircode but you get the idea)

Dim ctl As Control

For Each ctl In Me.Controls
            If ctl.Tag = "ProgLock" Then
                 ctl.BackColor = 16777215
                 ctl.Locked = True
            End If
     Next

This way you don't lock the whole form just the editing of the controls you need locked.

 

[Gasman]

Set a tag on the controls you want locked on the sub form "ProgLock" or similar, then on the current event of your form loop around the individual controls that are set to tag "ProgLock" and lock / unlock them appropriately. Something like this (aircode but you get the idea)

Dim ctl As Control

For Each ctl In Me.Controls
            If ctl.Tag = "ProgLock" Then
                 ctl.BackColor = 16777215
                 ctl.Locked = True
            End If
     Next

This way you don't lock the whole form just the editing of the controls you need locked.

I *think* I tried that previously, but will try again tomorrow and report back.
The form is in datasheet view with only about 5 controls on each record, so will not take long to change. However all controls on the record are/need to be protected.

Thanks Minty.

 

[Gasman]

Hi Minty,

Sorry I have been so late in coming back, but have been snowed under with other work.
I did try enabling/disabling the controls using tags previously, but my mistake was disabling the control, not locking it. I wished to do that to give some visual clue, but can see from your code that the same can be achieved with the backcolor property.

Have amended one form and it works great. Thank you. just need to change the other forms when I have time.

 

[Minty]

Glad you sorted it - now if only i could get my looping round a badly formatted spreadsheet import working... Sometimes you can't see the wood for the trees