Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-08-2019, 12:27 AM   #1
AlexN
Newly Registered User
 
AlexN's Avatar
 
Join Date: Nov 2014
Location: Athens, Greece
Posts: 217
Thanks: 117
Thanked 14 Times in 14 Posts
AlexN is on a distinguished road
Proper event for loading subfrm procedure

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) it doesnít work.

Any ideas ?

Thanks

AlexN is offline   Reply With Quote
Old 09-08-2019, 01:28 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,376
Thanks: 112
Thanked 2,847 Times in 2,595 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Proper event for loading subfrm procedure

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
AlexN (09-08-2019)
Old 09-08-2019, 01:47 AM   #3
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,275
Thanks: 528
Thanked 927 Times in 879 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Proper event for loading subfrm procedure

Quote:
Originally Posted by AlexN View Post
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.

__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
The Following User Says Thank You to Uncle Gizmo For This Useful Post:
AlexN (09-08-2019)
Old 09-08-2019, 01:48 AM   #4
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,095
Thanks: 421
Thanked 753 Times in 731 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Proper event for loading subfrm procedure

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

Code:
DoCmd.GoToRecord acDataForm, Me.Name, acPrevious, 30
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
AlexN (09-08-2019)
Old 09-08-2019, 02:08 AM   #5
AlexN
Newly Registered User
 
AlexN's Avatar
 
Join Date: Nov 2014
Location: Athens, Greece
Posts: 217
Thanks: 117
Thanked 14 Times in 14 Posts
AlexN is on a distinguished road
Re: Proper event for loading subfrm procedure

Quote:
Originally Posted by isladogs View Post
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 is offline   Reply With Quote
Old 09-08-2019, 02:10 AM   #6
AlexN
Newly Registered User
 
AlexN's Avatar
 
Join Date: Nov 2014
Location: Athens, Greece
Posts: 217
Thanks: 117
Thanked 14 Times in 14 Posts
AlexN is on a distinguished road
Re: Proper event for loading subfrm procedure

Quote:
Originally Posted by Uncle Gizmo View Post
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 is offline   Reply With Quote
Old 09-08-2019, 02:11 AM   #7
AlexN
Newly Registered User
 
AlexN's Avatar
 
Join Date: Nov 2014
Location: Athens, Greece
Posts: 217
Thanks: 117
Thanked 14 Times in 14 Posts
AlexN is on a distinguished road
Re: Proper event for loading subfrm procedure

Quote:
Originally Posted by Gasman View Post
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.

AlexN is offline   Reply With Quote
Old 09-08-2019, 02:20 AM   #8
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,376
Thanks: 112
Thanked 2,847 Times in 2,595 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Proper event for loading subfrm procedure

Quote:
Originally Posted by AlexN View Post
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
AlexN (09-08-2019)
Old 09-08-2019, 02:21 AM   #9
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,095
Thanks: 421
Thanked 753 Times in 731 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Proper event for loading subfrm procedure

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
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
AlexN (09-08-2019)
Old 09-08-2019, 02:38 AM   #10
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,275
Thanks: 528
Thanked 927 Times in 879 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Proper event for loading subfrm procedure

Quote:
Originally Posted by isladogs View Post

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:-
Quote:
Originally Posted by AlexN View Post
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!
__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)

Last edited by Uncle Gizmo; 09-08-2019 at 02:40 AM. Reason: added quote for clarity
Uncle Gizmo is offline   Reply With Quote
The Following User Says Thank You to Uncle Gizmo For This Useful Post:
AlexN (09-08-2019)
Old 09-08-2019, 02:42 AM   #11
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,275
Thanks: 528
Thanked 927 Times in 879 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Proper event for loading subfrm procedure

Just a thought maybe it's not an event that is filtering the subform? maybe it uses queries? need a bit more information really!
__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
The Following User Says Thank You to Uncle Gizmo For This Useful Post:
AlexN (09-08-2019)
Old 09-08-2019, 03:19 AM   #12
AlexN
Newly Registered User
 
AlexN's Avatar
 
Join Date: Nov 2014
Location: Athens, Greece
Posts: 217
Thanks: 117
Thanked 14 Times in 14 Posts
AlexN is on a distinguished road
Re: Proper event for loading subfrm procedure

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
AlexN is offline   Reply With Quote
Old 09-08-2019, 03:41 AM   #13
moke123
Me.Dirty=True
 
moke123's Avatar
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 745
Thanks: 2
Thanked 229 Times in 215 Posts
moke123 will become famous soon enough
Re: Proper event for loading subfrm procedure

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

"select top 30 .... order by whatever criteria."
moke123 is offline   Reply With Quote
The Following User Says Thank You to moke123 For This Useful Post:
AlexN (09-08-2019)
Old 09-08-2019, 04:08 AM   #14
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,275
Thanks: 528
Thanked 927 Times in 879 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Proper event for loading subfrm procedure

Quote:
Originally Posted by AlexN View Post
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
__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
The Following User Says Thank You to Uncle Gizmo For This Useful Post:
AlexN (09-08-2019)
Old 09-08-2019, 04:14 AM   #15
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,683
Thanks: 50
Thanked 1,077 Times in 1,058 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Proper event for loading subfrm procedure

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...

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
AlexN (09-08-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
requery a comboBox at main form from subfrm then return back to the Subfrm Control GeneH Forms 2 06-05-2019 07:14 PM
procedure declaration does not match description of event or procedure having the sam aziz rasul Modules & VBA 4 11-10-2016 09:21 AM
[SOLVED] Error - Procedure declaration does not match description of event or procedure ANP Modules & VBA 5 07-31-2014 06:35 AM
procedure declaration does not match description of event or procedure having the sam liamfitz Forms 6 02-13-2013 12:34 PM
Procedure declaration does not match description of event or procedure having same na liamfitz Modules & VBA 0 01-22-2013 06:41 AM




All times are GMT -8. The time now is 06:25 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World