Proper event for loading subfrm procedure (1 Viewer)

AlexN · Sep 8, 2019

Hi everyone,

I have an unbound MainForm, bearing two subforms (SubFrmA, SubFrmB). The two subforms are related, so when I pick a record from SubFrmA data in SubFrmB changes showing related records. SubFrmB contains thousands of records related to every single record in SubFrmA, that’s why I want it to display only the 30 last ones of them.
I have a little piece of code (given by someone here a long time ago for another project)

Code:

Dim i As Integer
  On Error Resume Next
  DoCmd.RunCommand  acCmdRecordsGoToLast
  For i = 1 To 30
   DoCmd.RunCommand acCmdRecordsGoToPrevious
  Next i

but I don’t know under which event I should put it. Note that under the OnLoad event of the MainForm (or any of the subforms) [FONT=&quot]it doesn’t work.

Any ideas ?

Thanks
[/FONT]

isladogs · Sep 8, 2019

That will work if run from the subform itself.
If you are trying to run this from the main form or the other subform, then you need to reference that correctly. See http://access.mvps.org/access/forms/frm0031.htm

Uncle Gizmo · Sep 8, 2019

AlexN said:
The two subforms are related, so when I pick a record from SubFrmA data in SubFrmB changes showing related records.

My understanding of your arrangement is that when you click on a record in one sub form, it refreshes the related records in the other sub-form. I think you need to call the code in this procedure.

Gasman · Sep 8, 2019

Rather than go previous 30 times, you can just move a set number, forwards or back.?

Code:

DoCmd.GoToRecord acDataForm, Me.Name, acPrevious, 30

AlexN · Sep 8, 2019

isladogs said:
That will work if run from the subform itself.
If you are trying to run this from the main form or the other subform, then you need to reference that correctly. See http://access.mvps.org/access/forms/frm0031.htm

Of course proper referencing is needed, but isn't it depending on which event (of which control) I put the code in? Since I don't know where to put it I skipped the references.

AlexN · Sep 8, 2019

Uncle Gizmo said:
My understanding of your arrangement is that when you click on a record in one sub form, it refreshes the related records in the other sub-form. I think you need to call the code in this procedure.

You mean in the OnClick Event of the first subform? (tried it, doesn't work)

AlexN · Sep 8, 2019

Gasman said:
Rather than go previous 30 times, you can just move a set number, forwards or back.?

Code:

DoCmd.GoToRecord acDataForm, Me.Name, acPrevious, 30

Could be done but still don't know where to put the code.

isladogs · Sep 8, 2019

AlexN said:
Of course proper referencing is needed, but isn't it depending on which event (of which control) I put the code in? Since I don't know where to put it I skipped the references.

I'm not sure there's much logic in that....

First of all it depends whether both subforms are children of the main form or if the second subform is a sub-subform of the first

If the former is true, then follow the relevant references in the link followed by a requery if needed. If the latter, then you may just need a requery (or nothing at all)

Agree with Gasman's suggestion to simplify the code

EDIT
Use the same event as that used to filter the first subform

Gasman · Sep 8, 2019

Well if you put it in the click event of subforma and used the name of subformb in the GoToRecord line I would have expected that to work.

You are not using form references, so executing anywhere except subformb is not going to work.

Try from the click event of subformA

Code:

DoCmd.GoToRecord acDataForm, "SubFormB", acLast
DoCmd.GoToRecord acDataForm, "SubFormB", acPrevious, 30

Uncle Gizmo · Sep 8, 2019

isladogs said:
EDIT
Use the same event as that used to filter the first subform

Yes that's what I said in post 3 "I think you need to call the code in this procedure."

But the OP said he'd already tried that and it didn't work?

Edit:-

AlexN said:
You mean in the OnClick Event of the first subform? (tried it, doesn't work)

Don't understand why it does one thing and not the other? Is there's something we're not being told!

Uncle Gizmo · Sep 8, 2019

Just a thought maybe it's not an event that is filtering the subform? maybe it uses queries? need a bit more information really!

AlexN · Sep 8, 2019

Let’s go again

MainForm is unbound and has only a few textboxes whose (imput) values filter SubFormA (bound to table). SubFormA and SubFormB (bound to another table) are connected via a textfield, call it ID. This way, when I pick a record form (filtered) SubFormA, SubFormB shows all (hundreds, even thousands) records of its table. So far so good, everything works smoothly.
Because of the vast number of related records in SubFormB, every time I pick a (filtered) record in SubFormA, I want it to load of course all records, but display only last 30 that are more significant.

I tried to use this little piece of code I posted, with the correct references (not posted), under various events on all three forms but managed nothing. So, I’m asking for any ideas not necessarily including the code I posted.

Thank you

@Gasman
Tried these two lines of code you suggested under the OnClick event of SubFrmA. It only worked for the first click. When I picked another record (clicked again on another) SubFormB showed all related records focusing on the first

moke123 · Sep 8, 2019

Just a thought, why not set the rowsource of subform b upon selection from subform a.

"select top 30 .... order by whatever criteria."

Uncle Gizmo · Sep 8, 2019

AlexN said:
MainForm is unbound and has only a few textboxes whose (imput) values filter SubFormA (bound to table).

When when you enter a new value in that input "textbox" or "text boxes" what event re-lists the data displayed? There must be some VBA code, something happening under the hood that creates the new sets of records?

If it's not an event, explain how it works.. .

Sent from my Pixel 3a using Tapatalk

theDBguy · Sep 8, 2019

Hi Alex. I’m sorry if you have tried this already and I missed it, but how about in the Current event of subform A, something like:

Code:

Me.Parent.SomeTextbox.SetFocus
Me.Parent.SubformB.SetFocus
DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acPrevious, 30

Just make sure you use the correct names for your objects. Hope that helps...

AlexN · Sep 8, 2019

moke123 said:
Just a thought, why not set the rowsource of subform b upon selection from subform a.

"select top 30 .... order by whatever criteria."

Thank you for your answer.
"Select" would force SubFormB to load only selected records. I want it to load them all but display only last 30

arnelgp · Sep 8, 2019

add Timer event to SubformB.
Set the Timer Interval to 100.
add code to timer event:

Code:

Private Sub Form_Timer()
    Dim i As Integer
    On Error Resume Next
    Me.TimerInterval = 0
    DoCmd.Echo False
    With Me.Recordset
        .MoveLast
        Me.Bookmark = .Bookmark
        For i = 1 To 30
            .MovePrevious
        Next
    End With
    DoCmd.Echo True
End Sub

add code to SubformA, Current Event:

Code:

Private Sub Form_Current()
Me.Parent![SubfrmB].Form.TimerInterval = 100
End Sub

AlexN · Sep 8, 2019

Uncle Gizmo said:
When when you enter a new value in that input "textbox" or "text boxes" what event re-lists the data displayed? There must be some VBA code, something happening under the hood that creates the new sets of records?

If it's not an event, explain how it works.. .

Sent from my Pixel 3a using Tapatalk

Under the OnChange event of the textboxes there's a procedure that makes SubFormA "relist" data (the filter as you type procedure). Problem is, filtered SubFormA could show let's say 10 records. Picking on of them is triggering SubFormB's requery.

AlexN · Sep 8, 2019

@ arnelgp
@ theDBguy

Thank you both for your answers. I'll try them both and I'll provide feedback.

AlexN · Sep 8, 2019

@ arnelgp
@ theDBguy

You guys are my Sunday heroes. Both suggestions seem to work perfectly.

@ arnelgp Immediate click on another record in SubFormA makes SubFormB go to the first record. Should I raise the timer interval?

Thank you!!! Thank you!!! Thank you all!!!

Proper event for loading subfrm procedure (1 Viewer)

Registered User.

MVP / VIP

Nifty Access Guy

Enthusiastic Amateur

Registered User.

Registered User.

Registered User.

MVP / VIP

Enthusiastic Amateur

Nifty Access Guy

Nifty Access Guy

Registered User.

AWF VIP

Nifty Access Guy

I’m here to help

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread