Is it possible to return back to the record on screen instead of the first record after a requery has been performed? I 've a frmOrderListing, from this form I open another form to edit and after
completion I do a reuery. When return back to the first from the record goes back to record one. I want to go back to the active record. Please help.
Full Version: After Requery Return Back To Active Record
Yes, first try using a Refresh rather than a Requery. If that doesn't work, then capture the Key value of current record before the Requery and use code like:
CODE
Dim rs As Recordset
Set rs = Me.Recordsetclone
rs.FindFirst "[keyfield] = " & currentkey
Me.Bookmark = rs.Bookmark
Set rs = Me.Recordsetclone
rs.FindFirst "[keyfield] = " & currentkey
Me.Bookmark = rs.Bookmark
Thanks Scott. [Read your book ]. I used your codes but I got this message Compile Error: Variable not defined. "Current Key" . What key is this? Please advise.
Hi Foxtrojan,
Try this - copy the code below into a Module and save it. Then in your form (which I assume is a Continuous type form) call the routine like this :-
FormRequery Me
in place of the Requery command that you have at the moment.
HTH
Peter Hibbs.
Try this - copy the code below into a Module and save it. Then in your form (which I assume is a Continuous type form) call the routine like this :-
FormRequery Me
in place of the Requery command that you have at the moment.
CODE
Public Sub FormRequery(frm As Form)
'Requery a Continuous type form and restore record positions to original values
'Entry (frm) = Reference to Continuous type form
'Exit Form requeried and visible records reset to original positions
Dim vTopRow As Long, vRowNo As Long, vSectionTop As Long
vTopRow = frm.CurrentRecord 'save current record number of top row
vSectionTop = frm.CurrentSectionTop 'and save distance in Twips from top of form
frm.Painting = False 'turn screen updates off temporarily
frm.Requery 'and requery sub-form
If frm.Section(acHeader).Visible = True Then 'if form header visible then
vRowNo = (vSectionTop - frm.Section(acHeader).Height) / frm.Section(acDetail).Height 'calc row number - header
Else 'if form header NOT visible then
vRowNo = vSectionTop / frm.Section(acDetail).Height 'calc row number
End If
frm.SelTop = frm.RecordsetClone.RecordCount 'set top row to last row first
frm.SelTop = vTopRow - vRowNo 'calc what top row record was and restore
frm.Painting = True 'turn screen updates back on
End Sub
'Requery a Continuous type form and restore record positions to original values
'Entry (frm) = Reference to Continuous type form
'Exit Form requeried and visible records reset to original positions
Dim vTopRow As Long, vRowNo As Long, vSectionTop As Long
vTopRow = frm.CurrentRecord 'save current record number of top row
vSectionTop = frm.CurrentSectionTop 'and save distance in Twips from top of form
frm.Painting = False 'turn screen updates off temporarily
frm.Requery 'and requery sub-form
If frm.Section(acHeader).Visible = True Then 'if form header visible then
vRowNo = (vSectionTop - frm.Section(acHeader).Height) / frm.Section(acDetail).Height 'calc row number - header
Else 'if form header NOT visible then
vRowNo = vSectionTop / frm.Section(acDetail).Height 'calc row number
End If
frm.SelTop = frm.RecordsetClone.RecordCount 'set top row to last row first
frm.SelTop = vTopRow - vRowNo 'calc what top row record was and restore
frm.Painting = True 'turn screen updates back on
End Sub
HTH
Peter Hibbs.
Keyfield and currentkey are objects you need to supply. I don't know what your Primary key field is named. You need to substitute your PK fieldname for keyfield.
Same thing for the control bound to your keyfield. So currentkey should be replaced with Me.controlname (using your controlname)
However, if you are using a continuous form. Peter's code may work better.
Same thing for the control bound to your keyfield. So currentkey should be replaced with Me.controlname (using your controlname)
However, if you are using a continuous form. Peter's code may work better.
Thanks guys. Will work on your advice.
thanks again for the code Peter
Just one question...where do you put the FormRequery Me?
Thanks
Larry
Just one question...where do you put the FormRequery Me?
Thanks
Larry
QUOTE (lkbree51 @ May 31 2012, 02:04 PM) 
thanks again for the code Peter
Just one question...where do you put the FormRequery Me?
Thanks
Larry
Just one question...where do you put the FormRequery Me?
Thanks
Larry
Wherever you want to call the function from.
QUOTE (ScottGem @ May 31 2012, 01:43 PM)
Wherever you want to call the function from.
Thanks Scott
I was asking the below...where to the FormRequery Me to call the Module?
Try this - copy the code below into a Module and save it. Then in your form (which I assume is a Continuous type form) call the routine like this :-
FormRequery Me
in place of the Requery command that you have at the moment.
Thanks
Larry
Hi Larry,
As I said above, you would replace the Me.Query command that you use to requery the Continuous form with that line of code. If that is not clear then perhaps you could post the code you are using in the form.
HTH
Peter Hibbs.
As I said above, you would replace the Me.Query command that you use to requery the Continuous form with that line of code. If that is not clear then perhaps you could post the code you are using in the form.
HTH
Peter Hibbs.
QUOTE (lkbree51 @ May 31 2012, 02:52 PM)
Thanks Scott
I was asking the below...where to the FormRequery Me to call the Module?
Thanks
Larry
I was asking the below...where to the FormRequery Me to call the Module?
Thanks
Larry
Larry,
The problem is where to put it is up to you. As Peter said you would use it in place of the Me.Requery you may be using now.
Anytime you are giving code to create a Sub or Function, you would call them the same way you would call any Sub or Function where user created or built in.
Thanks any ways...still can't get it to work... 
Been working on this for 2 days now...
Larry
Been working on this for 2 days now...
Larry
Larry,
Perhaps if you can upload the database or post the code for the form, we may be able to see the problem.
Peter Hibbs.
Perhaps if you can upload the database or post the code for the form, we may be able to see the problem.
Peter Hibbs.
QUOTE (Peter Hibbs @ May 31 2012, 03:22 PM)
Larry,
Perhaps if you can upload the database or post the code for the form, we may be able to see the problem.
Peter Hibbs.
Perhaps if you can upload the database or post the code for the form, we may be able to see the problem.
Peter Hibbs.
Thanks for the reply Peter
There is a copy of it in my prior post Refresh Or Requery A Subform, Office 2010 ... been trying to resolve this for a a couple of days now and I saw your code on this thread.
I just can't figure out where to put the code to fire the Module.
Thanks
Larry
Hi Larry,
Sorry, I can't see any zip files or code in that thread! Perhaps you can upload it again.
Peter.
Sorry, I can't see any zip files or code in that thread! Perhaps you can upload it again.
Peter.
Thanks Peter
I gave you the wrong one...sorry
I will upload it again.
Thanks
Larry
I gave you the wrong one...sorry
I will upload it again.
Thanks
Larry
Hi Larry,
OK, I have got your DB but what exactly is the problem. What form are you opening from what other form that needs a requery? I see loads of Continuous type forms but I don't see anywhere that you are opening a pop up form and requerying the Continuous form. Please explain the exact problem and what happens or doesn't happen that should happen or not as the case may be.
Peter.
OK, I have got your DB but what exactly is the problem. What form are you opening from what other form that needs a requery? I see loads of Continuous type forms but I don't see anywhere that you are opening a pop up form and requerying the Continuous form. Please explain the exact problem and what happens or doesn't happen that should happen or not as the case may be.
Peter.
Thanks for reply Peter
I am sorry...it is the Customer Billing (Current)...the top one on the menu.
Larry
I am sorry...it is the Customer Billing (Current)...the top one on the menu.
Larry
Hi Larry,
OK, I see that but what about it? It looks OK to me!
Peter.
OK, I see that but what about it? It looks OK to me!
Peter.
Thanks for the reply Peter
The totals at the bottom of the subform are not recalculating when you add a new entry to it
Larry
The totals at the bottom of the subform are not recalculating when you add a new entry to it
Larry
Hi Larry,
OK, I can see why there has been some confusion with your question since this problem has nothing to do with the original question.
Anyway, the sums of the columns do add up correctly (as far as I can tell).
I suspect that what you are referring to is that all the totals field show #Error whenever you add a new record to the list and I believe this is due to the Tonnage calculation in the query returning an error because the Weight column defaults to Null on a new record. If this is the problem then one way you can fix it is to change the Tonnage calculation in the query to look like this :-
Tonnage: Round((CCur(Nz([Weight],0))/2000),2)
That is, change the Weight variable in the calculation to zero if it is Null. Alternatively you make sure the Weight field always defaults to zero instead of Null. If it is something more complicated then I would suggest you start a new thread so that more people can see it.
HTH
Peter Hibbs.
OK, I can see why there has been some confusion with your question since this problem has nothing to do with the original question.
Anyway, the sums of the columns do add up correctly (as far as I can tell).
I suspect that what you are referring to is that all the totals field show #Error whenever you add a new record to the list and I believe this is due to the Tonnage calculation in the query returning an error because the Weight column defaults to Null on a new record. If this is the problem then one way you can fix it is to change the Tonnage calculation in the query to look like this :-
Tonnage: Round((CCur(Nz([Weight],0))/2000),2)
That is, change the Weight variable in the calculation to zero if it is Null. Alternatively you make sure the Weight field always defaults to zero instead of Null. If it is something more complicated then I would suggest you start a new thread so that more people can see it.
HTH
Peter Hibbs.
Thanks for the reply Peter
That is was not the problem...I had already fixed that by making it place a Default zero in the field.
It is the totals in the footer of the subform...they are not recalculating when a new entry is made as I stated earlier...need to Requery it somehow...
Thanks any way
Larry
That is was not the problem...I had already fixed that by making it place a Default zero in the field.
It is the totals in the footer of the subform...they are not recalculating when a new entry is made as I stated earlier...need to Requery it somehow...
Thanks any way
Larry
Hi Peter. I have this error message "Compile Error: Argument not optional". Where to trouble shoot this?
Hi,
Well, that is a bit difficult to say without knowing what you have done. Show me the code that you are using in your Continuous form (I assume you have added the code I supplied to a Cod Module).
Peter Hibbs.
Well, that is a bit difficult to say without knowing what you have done. Show me the code that you are using in your Continuous form (I assume you have added the code I supplied to a Cod Module).
Peter Hibbs.
Thanks Peter, I think I get tou, I may have placed the FormRequery.Me in between that caused this confusion. I'll do some home-work and get back to you. Thanks alot for your help.
Hi,
Yes, you need :-
FormRequery Me
not
FormRequery.Me
Good luck,
Peter Hibbs.
Yes, you need :-
FormRequery Me
not
FormRequery.Me
Good luck,
Peter Hibbs.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.