Focus on record in subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using a main form in single view to enter data to a table.
On the main form I have a subform that is a continuous form to show that
data. Both main and subforms have the same RecordSource.

On the main form, After Update event is:
Private Sub Form_AfterUpdate()
Me.Subform.Requery
End Sub
in order to update the subform to show the user what has been entered.
What I want is for the subform to move to the record that was last entered
on the mainform (without the cursor actually staying in the subform) after
the subform has been updated. I'm trying to keep the user from having to
scroll down inside the subform the check on what's been entered. My VB is
quite rusty so sample/generic code would be quite helpful.

Thanks,
Benny
 
From Benny :
I am using a main form in single view to enter data to a table.
On the main form I have a subform that is a continuous form to show that
data. Both main and subforms have the same RecordSource.

On the main form, After Update event is:
Private Sub Form_AfterUpdate()
Me.Subform.Requery
End Sub
in order to update the subform to show the user what has been entered.
What I want is for the subform to move to the record that was last entered
on the mainform (without the cursor actually staying in the subform) after
the subform has been updated. I'm trying to keep the user from having to
scroll down inside the subform the check on what's been entered. My VB is
quite rusty so sample/generic code would be quite helpful.

Thanks,
Benny
Click to expand...

I think the following should do the trick:

dim rsClone as DAO.Recordset
set rsclone = me.<subfornname>.form.recordsetclone
if not rsclone.eof then
rsclone.movelast
me.<subfornname>.form.bookmark = rsclone.bookmark
endif
rsclone.close
set rcslone = nothing
 
Thanks for the suggestion. I should add that my VB programming is not only
rusty, it's not that extensive. Here is how I implemented your suggestion
(Child0 is my subform (it's the only subform in my app)). I don't know if I
inserted the code into the correct spot or not, or if the requery is in the
correct spot etc.

Private Sub Form_AfterUpdate()

Dim rsClone As DAO.Recordset

Me.Child0.Requery

Set rsClone = Me.Child0.Form.RecordsetClone
If Not rsClone.EOF Then
rsClone.MoveLast
Me.Child0.Form.Bookmark = rsClone.Bookmark
End If
rsClone.Close
Set rcslone = Nothing
End Sub

The result I get is that the subform moves to the last record in the
underlying table. I would like to get it to move to the last record entered
into the table, via the mainform. Don't know if it makes a difference, but
the table is ordered by UnitNumber for the purpose of easier viewing. This
appears to be a little over my head, so don't be shy about "talking down" to
me. Thanks for any and all help.

Benny
 
From Benny :
Thanks for the suggestion. I should add that my VB programming is not only
rusty, it's not that extensive. Here is how I implemented your suggestion
(Child0 is my subform (it's the only subform in my app)). I don't know if I
inserted the code into the correct spot or not, or if the requery is in the
correct spot etc.

Private Sub Form_AfterUpdate()

Dim rsClone As DAO.Recordset

Me.Child0.Requery

Set rsClone = Me.Child0.Form.RecordsetClone
If Not rsClone.EOF Then
rsClone.MoveLast
Me.Child0.Form.Bookmark = rsClone.Bookmark
End If
rsClone.Close
Set rcslone = Nothing
End Sub

The result I get is that the subform moves to the last record in the
underlying table. I would like to get it to move to the last record entered
into the table, via the mainform. Don't know if it makes a difference, but
the table is ordered by UnitNumber for the purpose of easier viewing. This
appears to be a little over my head, so don't be shy about "talking down" to
me. Thanks for any and all help.

Benny
Click to expand...

Well, in the example I gave, the recordpointer always goes to the last
record, correct. Based on the assumption that the sort-order of that
form would be equal to the 'number' that the users entered would be
incrementing and thus will always be the last one.

If you want to go to a specific record, you'll have to change the
movelast to a findfirst and pass the number that the user entered in
the mainform.
In other words, if you're user enters UnitNumber as the unique
identifier and UnitNumber is also the fieldname on the form, you can
use that field in the example below. Otherewise, find another unique
field to search on.

rsClone.FindFirst("UnitNumber=" & CStr(Me.UnitNumber)) 'or
Forms!<MainFormName>.UnitNumber if you're not on the form where the
control resides
If Not rsClone.NoMatch Then
'Records is found, set the bookmark
Me.Child0.Form.Bookmark = rsClone.Bookmark
else
MsgBox "Corresponding record not found in the subform", vbexclamation
+ vbokonly 'Or some more meaningfull message
endif

Keep in mind that this will only work after the record is added to the
table, otherwise the record will never be found and the recordpointer
isn't moved. The same goes when the user deletes a record...

Also note that searching large recordsets can take a while and not
having an index on the field you're searching will increase the
searchtime...
 
Back
Top