Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-27-2018, 02:21 AM   #1
Tieval
Still Clueless
 
Join Date: Jun 2015
Location: UK
Posts: 429
Thanks: 57
Thanked 42 Times in 42 Posts
Tieval is on a distinguished road
Requery Method

Hi,

I have a sub-form with a load event as follows:
Code:
Private Sub Form_Load()
testresults
DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acPrevious, 14
End Sub
This runs a complex query of data (in a public module) and loads the sub-form as a continuous form with the last fifteen records displayed.

My problem is that on the main-form I have instances where I requery the data and cannot get it to reflect the above.

Code:
Private Sub tBlade_AfterUpdate()
Me.subform.Requery
End Sub
This re-queries the data but doesn't move to the correct record, so I tried:

Code:
Private Sub tBlade_AfterUpdate()
testresults
End Sub
This still re-queries the data but doesn't move to the correct record, however adding the GoToRecord falls over as it cannot reference the correct form.

Any suggestions would be most helpful.

Tieval is offline   Reply With Quote
Old 11-27-2018, 02:44 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,376
Thanks: 0
Thanked 746 Times in 731 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Requery Method

why are you Requerying?

the data is live on your form. The only reason to requery is if you did a major action query behind the form. Requery will reset the cursor to rec #1.
Ranman256 is offline   Reply With Quote
Old 11-27-2018, 03:00 AM   #3
Tieval
Still Clueless
 
Join Date: Jun 2015
Location: UK
Posts: 429
Thanks: 57
Thanked 42 Times in 42 Posts
Tieval is on a distinguished road
Re: Requery Method

Yes, that is the point. I am doing a major action query and the requery method does this perfectly and as you say resets it to record one, I would like to then move it to the fifteenth from last as per my load event.

Tieval is offline   Reply With Quote
Old 11-27-2018, 03:14 AM   #4
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,308
Thanks: 115
Thanked 3,095 Times in 2,813 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 isladogs is a splendid one to behold
Re: Requery Method

Depending on what your 'major action query' does, try using Recalc instead of Requery
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
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.


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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-27-2018, 03:15 AM   #5
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,717
Thanks: 105
Thanked 1,511 Times in 1,424 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Requery Method

You need record a key for the record you want to return to and then move to it subsequent to the requery.
Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
Tieval (11-27-2018)
Old 11-27-2018, 04:14 AM   #6
Tieval
Still Clueless
 
Join Date: Jun 2015
Location: UK
Posts: 429
Thanks: 57
Thanked 42 Times in 42 Posts
Tieval is on a distinguished road
Re: Requery Method

I am trying to open a continuous sub-form from a query that interacts with items on the main-form, hence when I change something on the main-form I need to re-load the sub-form. This may seem painful and inefficient but is necessary for the overall function.

I am setting a record key effectively as my initial sub-form load moves it to the last record and then goes back fourteen, this makes the form display the last fifteen records on load (the quantity of records keeps changing so fourteen from last is the best key that can be got).

My complex query works perfectly on load and on re-query, what I am looking for is a method to apply the gotorecord to the requery event.

The load event of the form works perfectly as it is inside the sub-form.
The requery cannot have the gotorecord applied to it.
If I call the query again from the main-form I cannot call the gotorecord from the main-form.
Tieval is offline   Reply With Quote
Old 11-27-2018, 04:27 AM   #7
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,308
Thanks: 115
Thanked 3,095 Times in 2,813 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 isladogs is a splendid one to behold
Re: Requery Method

Understood... so have you tried my suggestion or that of Galaxiom

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
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.


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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Tieval (11-27-2018)
Old 11-27-2018, 04:42 AM   #8
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,531
Thanks: 441
Thanked 843 Times in 814 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Requery Method

So do you have more than 15 records after the requery.?
__________________
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
Old 11-27-2018, 04:57 AM   #9
Tieval
Still Clueless
 
Join Date: Jun 2015
Location: UK
Posts: 429
Thanks: 57
Thanked 42 Times in 42 Posts
Tieval is on a distinguished road
Re: Requery Method

Quote:
Originally Posted by isladogs View Post
Understood... so have you tried my suggestion or that of Galaxiom
I cannot get VBA to recognise the recalc method, but logically this will only recalculate fields, I am loading rows of data and my change to the main-form may bring in more entries which recalc cannot do?

I am not sure how to define a specific record and then reference how to go to it, I seem to be doing this already by going to the last and then back fourteen.

Quote:
So do you have more than 15 records after the requery.?
There are always loads of records, I am just trying to load a sub-form with room for fifteen rows of records with the last fifteen rows of records from the query. I can always scroll up to earlier records (they are in date order).

My original call to testresults is very complicated and abbreviated below (I have removed all the query stuff, it just sets the record source of the form:
Code:
Public Function testresults()
 Forms!frmMain!test.Form.RecordSource = scandata & " And " & SearchStg & " And " & BladesOnly & " Order By " & OrderBy
End Function
frmMain and test are the main and sub-forms.
Tieval is offline   Reply With Quote
Old 11-27-2018, 05:47 AM   #10
Tieval
Still Clueless
 
Join Date: Jun 2015
Location: UK
Posts: 429
Thanks: 57
Thanked 42 Times in 42 Posts
Tieval is on a distinguished road
Re: Requery Method

Rather embarrassingly, the following works perfectly:
Code:
Private Sub tBlade_AfterUpdate()
Me.test.SetFocus
testresults
DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acPrevious, 14
End Sub
Many thanks for all the suggestions.
Tieval is offline   Reply With Quote
Old 11-27-2018, 05:55 AM   #11
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,308
Thanks: 115
Thanked 3,095 Times in 2,813 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 isladogs is a splendid one to behold
Re: Requery Method

With the benefit of hindsight that was obvious.
Somehow I thought you were doing that already but have just re-read your original post again ...properly this time!
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
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.


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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Tieval (11-27-2018)
Old 11-27-2018, 06:02 AM   #12
Tieval
Still Clueless
 
Join Date: Jun 2015
Location: UK
Posts: 429
Thanks: 57
Thanked 42 Times in 42 Posts
Tieval is on a distinguished road
Re: Requery Method

Quote:
Originally Posted by isladogs View Post
With the benefit of hindsight that was obvious.
Somehow I thought you were doing that already but have just re-read your original post again ...properly this time!
Thanks Colin, hope it's nice in Somerset, belting down here in Gloucestershire
Tieval is offline   Reply With Quote
Old 11-27-2018, 06:08 AM   #13
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,308
Thanks: 115
Thanked 3,095 Times in 2,813 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 isladogs is a splendid one to behold
Re: Requery Method

It's also foul weather here. Tomorrow's worse.
Didn't realise you were so close. I'm just on the edge of the Mendip Hills AONB.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
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.


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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-27-2018, 06:25 AM   #14
Tieval
Still Clueless
 
Join Date: Jun 2015
Location: UK
Posts: 429
Thanks: 57
Thanked 42 Times in 42 Posts
Tieval is on a distinguished road
Re: Requery Method

Now for the problem.

Code:
Private Sub tBlade_AfterUpdate()
Me.test.SetFocus
testresults
DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acPrevious, 14
End Sub
I just found a way to have less than 15 records, are there any suggestions for avoiding this. Basically I want to check if testresults returns more than 15 records before going to the record, if not don't do the gotorecord.
Tieval is offline   Reply With Quote
Old 11-27-2018, 06:33 AM   #15
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,531
Thanks: 441
Thanked 843 Times in 814 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Requery Method

Test the value of Me.CurrentRecord ?
That appears to show the number of records in one of my forms.?

__________________
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
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Error "Method or data member not found" on requery peter2012 Forms 2 09-25-2014 12:18 AM
Method - TRANSFERSPREADSHEET is a hidden method Bilbo_Baggins_Esq Modules & VBA 2 08-14-2013 11:21 AM
Cause of 'object can't support this method' error using find method? Margarita Modules & VBA 2 04-11-2012 04:51 AM
where to put call to combo boxes requery method garethl Forms 3 06-25-2007 05:30 AM
Method not found - requery Minkey Forms 2 08-10-2004 11:31 AM




All times are GMT -8. The time now is 10:44 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