Requery Method (1 Viewer)

Tieval

Still Clueless
Local time
Today, 22:09
Joined
Jun 26, 2015
Messages
475
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.
 

Ranman256

Well-known member
Local time
Today, 18:09
Joined
Apr 9, 2015
Messages
4,339
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.
 

Tieval

Still Clueless
Local time
Today, 22:09
Joined
Jun 26, 2015
Messages
475
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.
 

isladogs

MVP / VIP
Local time
Today, 22:09
Joined
Jan 14, 2017
Messages
18,186
Depending on what your 'major action query' does, try using Recalc instead of Requery
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 09:09
Joined
Jan 20, 2009
Messages
12,849
You need record a key for the record you want to return to and then move to it subsequent to the requery.
 

Tieval

Still Clueless
Local time
Today, 22:09
Joined
Jun 26, 2015
Messages
475
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.
 

isladogs

MVP / VIP
Local time
Today, 22:09
Joined
Jan 14, 2017
Messages
18,186
Understood... so have you tried my suggestion or that of Galaxiom
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:09
Joined
Sep 21, 2011
Messages
14,047
So do you have more than 15 records after the requery.?
 

Tieval

Still Clueless
Local time
Today, 22:09
Joined
Jun 26, 2015
Messages
475
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.

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

Still Clueless
Local time
Today, 22:09
Joined
Jun 26, 2015
Messages
475
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.
 

isladogs

MVP / VIP
Local time
Today, 22:09
Joined
Jan 14, 2017
Messages
18,186
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!
 

Tieval

Still Clueless
Local time
Today, 22:09
Joined
Jun 26, 2015
Messages
475
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:D
 

isladogs

MVP / VIP
Local time
Today, 22:09
Joined
Jan 14, 2017
Messages
18,186
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.
 

Tieval

Still Clueless
Local time
Today, 22:09
Joined
Jun 26, 2015
Messages
475
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:09
Joined
Sep 21, 2011
Messages
14,047
Test the value of Me.CurrentRecord ?
That appears to show the number of records in one of my forms.?
 

Tieval

Still Clueless
Local time
Today, 22:09
Joined
Jun 26, 2015
Messages
475
I have been playing with the record number but cannot get a value anyway, any clues?

I am a little scared of using Dcount unless I can use it on the form as the query is too complicated and would probably double my processing time.
 

isladogs

MVP / VIP
Local time
Today, 22:09
Joined
Jan 14, 2017
Messages
18,186
Yes you can use DCount on the form and base it on the query.
Unless you have millions of records I doubt the time needed would be significant.
Alternatively, and not tested, but perhaps you could have a SELECT TOP 16 query based on one indexed field from your query and then do a DCount of that...but it would possibly take longer?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:09
Joined
Feb 28, 2001
Messages
27,001
When you did that DoCmd.GoToRecord , , acLast and your previous action had reset your position to record #1, you "traversed" the entire recordset. At that point, you MIGHT try checking Me.Recordset.RecordCount before taking that next step to move backwards 14 records. The record count is available after traversing a recordset from beginning to end.

https://docs.microsoft.com/en-us/of...-reference/recordset-recordcount-property-dao
 

Tieval

Still Clueless
Local time
Today, 22:09
Joined
Jun 26, 2015
Messages
475
Code:
Private Sub tBlade_AfterUpdate()
Me.test.SetFocus
scanresults
DoCmd.GoToRecord , , acLast
If Me.Recordset.RecordCount > 14 Then DoCmd.GoToRecord , , acPrevious, 14
End Sub
Well this falls over with error 91
 

Users who are viewing this thread

Top Bottom