What's the correct event for this code?

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

Guest

Hi everyone

I have subform within a main form and want the last-entered data to be
displayed on the subform when the main form is entered or a different record
is displayed. I have the following code:

Private Sub Form_Current()

MySubForm.SetFocus
On Error Resume Next
DoCmd.GoToRecord , , acLast
On Error GoTo 0

code continues ...

Everything works fine when the main form is opened. However, if I scroll
through the records by using the record selector at the bottom of the form,
the records displayed on the main form change correctly, but the data on the
subform is that which was entered first, rather than last, even if I return
to the first record on the main form which was displayed when the form was
opened initially.

I suspect it's because this code is in the wrong event. I've tried several
locations, including Form On Change, Form On Got Focus, Form On Activate but
I can't get it to work correctly. Am I on the right track of having the code
in the wrong event (which is the correct one?) or is the problem likely to be
something else?

Thanks for your time.
 
How about if you base the subform on a query in which the records are sorted
in the order you choose? Or, you could use the subform's Order By property
(add DESC at the end to reverse the order). I don't know what determines
the "Last" record (time stamp? autonumber?), but whatever it is you should
be able to sort by that. Unless I'm missing something, that should do what
you need.
 
Thank you Bruce. I'd tried using a query with data in reverse order, but the
Record Selector information gives 1 as the last data entered and I want it to
stay as, for instance "Record 4 of 4", rather than "Record 1 of 4". BTW,
it's simply the last set of data entered, without a date stamp, but there is
an autonumber field.

I tried putting <DESC> in the subform Order By Property, but that doesn't
work! I thought there would be a drop-down option at this site, but there
isn't. I'll look into this further.
 
Have you tried your code in the subform's Current event? I'm not quite
clear on which Current event you used.
 
Popeye,

One way is to use the On Enter event procedure for your subform control:

Private Sub sfrmMySubform_Enter()

With Me.sfrmMySubform.Form.RecordsetClone
DoCmd.GoToRecord , , acLast
End With

End Sub

HTH,
Brian
 
Hi Bruce

I put the code in the main form's Current Event. I deleted it from there
and put it into the subform's Current Event. I had to change a few things
(resetting the focus, for instance) and it always shows the last-entered
record, even when I tried to scroll through them.

I've decided to revert to the reverse order query that you mentioned
initially. I've got rid of the Record Selector Buttons and have created some
command buttons which navigate through the records. It's not ideal, but
better than the other options/problems I've encountered.

Thanks again for your input.
 
Hi Brian

I tried that, but it always gave the final record, even when I tried to
scroll through them. I just replied to Bruce and it looks like I'll have to
use a query to reorder the data into reverse. Everything works fine now that
I've created some buttons to move through the records.

Thank you for the suggestion.
 
Popeye,

I'm surprised it didn't work for you. I copied the code from one of my forms
which has a subform in datasheet view. It does NOT fire when you move from
one record to another within your subform. It will fire anytime you enter/
re-enter your subform, whether by tabbing into it, or when the cursor is in
it and you change records on the main form.

Brian
 
If you want to reverse the numbering (so that "1 of 15" can instead be "15
of 15"), create four unbound text boxes on your form: txtCurrent, txtTotal,
txtReverse, and txtCounter in this code example, which goes into the form's
Current event:

'Inserts current record number and total number of records
Me.txtCurrent = Me.CurrentRecord
Me.RecordsetClone.MoveLast
Me.txtTotal = Me.RecordsetClone.RecordCount
Me.txtReverse = Me.txtTotal + 1 - Me.txtCurrent
Me.txtCounter = Me.txtReverse & " of " & Me.txtTotal

You can make make the first three text boxes invisible. txtCounter can of
course have other text if you choose. There are probably ways of doing this
without using four text boxes, but this is pretty simple and works quite
well (although I have never used the reverse numbering).
 
Back
Top