Subform: Accessing last record

[robtyketto]

Greetings,

I have a main form. Upon filling in a combo box it then displays a subform (based on a query that uses values in the combo box on where clause)

THe subform has the paramater Data Entry = 'No' as I want users to see all existing records and all additions/deletions and edits.

However I want to move to the last record to default, allowing the user to add a record without navigating themselves to the last record.

A popular request, so found code (see below)

Private Sub cboDeliveryName_AfterUpdate()
 
  Me.qryStudentsResultsDeliverysubform1.Enabled = True
  Me.qryStudentsResultsDeliverysubform1.Visible = True
  Me.qryStudentsResultsDeliverysubform1.SetFocus
 
    With Me.qryStudentsResultsDeliverysubform1.Form.RecordsetClone
       If Not .BOF Then
            .MoveLast
            MsgBox "In here"
            Me.qryStudentsResultsDeliverysubform1.Form.Bookmark = .Bookmark
      End If
    End With
 
End Sub

When testing the subform is populated with 1 or more rows but the debug message is NEVER displayed. If i remove the IF condition it complains with message "3021 : No current record"

I cant even print the current record when im in the subform to see whats happening.

Can anyone please advice and assist?

Thanks
Rob

[mshmyob]

To jumpt to the last record in a subform after some event you could simplify your code by just doing this:

Me.subformControlName.SetFocus
DoCmd.GoToRecord , , acLast
 

Note: Going to the last record is NOT the same as adding a new record.

Greetings,

I have a main form. Upon filling in a combo box it then displays a subform (based on a query that uses values in the combo box on where clause)

THe subform has the paramater Data Entry = 'No' as I want users to see all existing records and all additions/deletions and edits.

However I want to move to the last record to default, allowing the user to add a record without navigating themselves to the last record.

A popular request, so found code (see below)

Private Sub cboDeliveryName_AfterUpdate()
 
  Me.qryStudentsResultsDeliverysubform1.Enabled = True
  Me.qryStudentsResultsDeliverysubform1.Visible = True
  Me.qryStudentsResultsDeliverysubform1.SetFocus
 
    With Me.qryStudentsResultsDeliverysubform1.Form.RecordsetClone
       If Not .BOF Then
            .MoveLast
            MsgBox "In here"
            Me.qryStudentsResultsDeliverysubform1.Form.Bookmark = .Bookmark
      End If
    End With
 
End Sub

When testing the subform is populated with 1 or more rows but the debug message is NEVER displayed. If i remove the IF condition it complains with message "3021 : No current record"

I cant even print the current record when im in the subform to see whats happening.

Can anyone please advice and assist?

Thanks
Rob

[robtyketto]

Thanks.

Added DoCmd.GoToRecord , , acLast to code.

If the subform is populated with 1 ROW then the cursor sits in the new record.

If there is more than 1 ROW then it sits in the first row.
Very strange behaviour.

Rob

[mshmyob]

Show the whole code now.

Thanks.

Added DoCmd.GoToRecord , , acLast to code.

If the subform is populated with 1 ROW then the cursor sits in the new record.

If there is more than 1 ROW then it sits in the first row.
Very strange behaviour.

Rob

[robtyketto]

Data entry is set to "No" on subform.

Code from combo box on main form:-

Private Sub cboDeliveryName_AfterUpdate()
 
    Me.qryStudentsResultsDeliverysubform1.Enabled = True
    Me.qryStudentsResultsDeliverysubform1.Visible = True
    Me.qryStudentsResultsDeliverysubform1.SetFocus
 
    '-- Set Focus to the SubFormControl 1st
    Me.qryStudentsResultsDeliverysubform1.SetFocus
    '-- Now set Focus to the control on SubForm
    Me.qryStudentsResultsDeliverysubform1.Form!txtStudentId.SetFocus
    '-- And now move to the last record
    DoCmd.GoToRecord , , acLast
 
End Sub

[mshmyob]

Try moving to the last record and THEN set focus on your control (ie: switch your last 2 lines around). See if that changes anything.

Data entry is set to "No" on subform.

Code from combo box on main form:-

Private Sub cboDeliveryName_AfterUpdate()
 
    Me.qryStudentsResultsDeliverysubform1.Enabled = True
    Me.qryStudentsResultsDeliverysubform1.Visible = True
    Me.qryStudentsResultsDeliverysubform1.SetFocus
 
    '-- Set Focus to the SubFormControl 1st
    Me.qryStudentsResultsDeliverysubform1.SetFocus
    '-- Now set Focus to the control on SubForm
    Me.qryStudentsResultsDeliverysubform1.Form!txtStudentId.SetFocus
    '-- And now move to the last record
    DoCmd.GoToRecord , , acLast
 
End Sub

[robtyketto]

Same issues.

I've even tried to add a combo box to allow the user to select if they wish to add or not using

Forms!frmModuleResults!qryStudentsResultsDelivery_ subform1.DataEntry = False

Forms!frmModuleResults!qryStudentsResultsDelivery_ subform1.DataEntry = True

I cant even get that right!

I will also use proper naming conventions for my form and not include spaces next time.Using access 2007.

[mshmyob]

Can you upload your database file to me.

Just click on edit/delete at the bottom of your last message and you can add an attachment as a ZIP file.

Same issues.

I've even tried to add a combo box to allow the user to select if they wish to add or not using

Forms!frmModuleResults!qryStudentsResultsDelivery_ subform1.DataEntry = False

Forms!frmModuleResults!qryStudentsResultsDelivery_ subform1.DataEntry = True

I cant even get that right!

I will also use proper naming conventions for my form and not include spaces next time.Using access 2007.