By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,140 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Subform moving to first record after parent Listbox requery

100+
P: 547
It has been 2 solid evenings now of starring at the focus going to the first record in subform "sfOrderICN" on requering a listbox on the parent form.

It worked fine when i used a subform instead of a listbox "sfOrderAvgAllQ1", on the parent form but I am trying to speed up the form when capturing.(will it help)
Somehow all the records get requeried and lose focus i think, when doing this, and then the focus goes to the first record in the subform after adding data to fields.
Expand|Select|Wrap|Line Numbers
  1. [Forms]![orderf].Form.[sfOrderAvgAllQ1].Requery  'after update event of combobox called "item_lookup" in subform called "sfOrderICN" 
Any suggested plan of action to get Access to listen to me?
Dec 13 '13 #1

✓ answered by NeoPa

I use subroutines from the module below (modRecPos) to help with this. Call SaveRP() from the Form_BeforeUpdate() event procedure when you know the update will go ahead, then call LoadRP() from the Form_AfterUpdate() event procedure to return the recordset to how you had it before as closely as possible.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. 'RecPos
  5. 'lngCurrentRec reflects the position of the selected record within the
  6. '              recordset.
  7. 'lngScroll     reflects the position of the selected record within the currently
  8. '              visible window.
  9. Public Type RecPos
  10.     lngCurrentRec As Long
  11.     lngScroll As Long
  12. End Type
  13.  
  14. 'SaveRP() Saves the current record and scroll position of a form.
  15. Public Sub SaveRP(ByRef rpVar As RecPos, ByRef frmMe As Form)
  16.     Dim intSection As Integer
  17.     Dim ctlVar As Control
  18.  
  19.     With frmMe
  20.         If Not .Visible Then
  21.             With rpVar
  22.                 .lngCurrentRec = -1
  23.                 .lngScroll = -1
  24.             End With
  25.             Exit Sub
  26.         End If
  27.         rpVar.lngCurrentRec = .SelTop
  28.         If .ActiveControl.Section = acDetail Then
  29.             rpVar.lngScroll = CalcScroll(frmMe)
  30.         Else
  31.             .Painting = False
  32.             Set ctlVar = .ActiveControl
  33.             Call .Detail.Controls(0).SetFocus
  34.             rpVar.lngScroll = CalcScroll(frmMe)
  35.             Call ctlVar.SetFocus
  36.             .Painting = True
  37.         End If
  38.     End With
  39. End Sub
  40.  
  41. 'CalcScroll() calculates the number of lines visible above the current record.
  42. Private Function CalcScroll(ByRef frmMe As Form) As Long
  43.     Dim lngPos As Long
  44.  
  45.     With frmMe
  46.         lngPos = .CurrentSectionTop _
  47.                - IIf(.FormHeader.Visible, .FormHeader.Height, 0)
  48.         CalcScroll = Round(lngPos / .Detail.Height, 0)
  49.     End With
  50. End Function
  51.  
  52. 'LoadRP() Refreshes/requeries and repositions the form using the values saved
  53. '  during SaveRP().
  54. Public Sub LoadRP(ByRef rpVar As RecPos, _
  55.                   ByRef frmMe As Form, _
  56.                   strControl As String, _
  57.                   Optional ByVal blnUpdate As Boolean = True, _
  58.                   Optional ByVal strType As String = "Refresh")
  59.     Dim lngTop As Long, lngSel As Long
  60.     Dim blnNewRec As Boolean
  61.     Dim ctlVar As Control
  62.  
  63.     With frmMe
  64.         If rpVar.lngCurrentRec = -1 Then Exit Sub
  65.         .Painting = False
  66.         If OnSubform(frmMe) Then
  67.             For Each ctlVar In .Parent.Controls
  68.                 With ctlVar
  69.                     If .ControlType = acSubform Then _
  70.                         If .Form Is frmMe Then Exit For
  71.                 End With
  72.             Next ctlVar
  73.             Call ctlVar.SetFocus
  74.         End If
  75.         Select Case strType
  76.         Case "Refresh"
  77.             Call .Refresh
  78.         Case "Requery"
  79.             Call .Requery
  80.         End Select
  81.         blnNewRec = (.AllowAdditions) And (.Recordset.Updatable)
  82.         Call DoCmd.GoToRecord(Record:=IIf(blnNewRec, acNewRec, acLast))
  83.         With rpVar
  84.             lngTop = .lngCurrentRec - .lngScroll
  85.             lngSel = .lngCurrentRec + IIf(blnUpdate, 1, 0)
  86.         End With
  87.         If (Not blnNewRec) And (lngSel > .Recordset.RecordCount) Then _
  88.             lngSel = .Recordset.RecordCount
  89.         .SelTop = lngTop
  90.         .SelTop = lngSel
  91.         .Painting = True
  92.         Call .Controls(strControl).SetFocus
  93.     End With
  94. End Sub
This code is used from the main modRecPos module, but I have it located elsewhere as it's more generally useful. You can include it wherever makes best sense to you ;-)
Expand|Select|Wrap|Line Numbers
  1. 'OnSubform() returns True if frmVar is open within a subform on another form.
  2. Public Function OnSubform(frmVar As Form) As Boolean
  3.     OnSubform = True
  4.     On Error Resume Next    'Then statement is executed on error
  5.     If Not TypeOf frmVar.Parent Is Form Then OnSubform = False
  6. End Function

Share this Question
Share on Google+
13 Replies


zmbd
Expert Mod 5K+
P: 5,286
Yes, a requery on a form will force to return to the control with tab-order(0) and the record pointers to the first record, including the related subform and its controls.

Any suggested plan of action to get Access to listen to me?
Well, I know that you have NOT told us what you want; thus, I suspect you haven't told access what is you want either.

More details.
Start with OS and Office version.
Tell us what you want it to do.
Tell us what it is doing.
Be as precise as possible.
Dec 14 '13 #2

P: 17
Try this if the listbox is loosing its value:

Expand|Select|Wrap|Line Numbers
  1. Dim vTemp as Variant ' May change to the datatype of the bound field
  2.  
  3. vTemp=[Forms]![orderf].Form![sfOrderAvgAllQ1]
  4. [Forms]![orderf].Form![sfOrderAvgAllQ1].Requery
  5. [Forms]![orderf].Form![sfOrderAvgAllQ1]=vTemp
  6.  
Or this for bound forms:

Expand|Select|Wrap|Line Numbers
  1. Dim strBookmark as String
  2.  
  3. strBookmark=Me.Bookmark
  4. Me.Requery
  5. Me.Bookmark=strBookmark 
  6.  
Dec 14 '13 #3

zmbd
Expert Mod 5K+
P: 5,286
PPelle
Please let us see what OP is actually wanting to do before we start offering guesses and code? You might actually be correct; however, quite often guessing just muddles the thread.

Neelsfer is normally pretty good at the details which is why I didn't delete the thread for not enough information to start with, so let him help us help him first.
Dec 14 '13 #4

P: 17
Yes, you're right, I had to make a guess. :) Sorry about that, I didn't see your reply before a made mine.
Dec 14 '13 #5

NeoPa
Expert Mod 15k+
P: 31,186
Down to you now Neels ;-)
Dec 14 '13 #6

100+
P: 547
thx guys. I use Windows 7 and office 2007 with sp's installed.
It does the requery action but i want it to return to the last record i was busy with in the subform.
This happens as soon as i select the item and it requery the listbox.
Can i force it back to the last record row before/after it loses focus?

Attached Images
File Type: jpg requery action.jpg (40.7 KB, 1545 views)
Dec 14 '13 #7

zmbd
Expert Mod 5K+
P: 5,286
Requery might alter/destroy the bookmark.
In a DAO.Recordset, I'd store the current record's PK, requery, and them perform a obj.recordset.findfirst on the PK unless you just want to move to the last record in which case a obj.recordset.movelast

I have a database where I record historical information, parent has the stuff for the equipment, subform has the information from the history table. When I add a record I have to use an unbound form, when the record is added, I requery and obj.recordset.movelast (I'll also due a "move -3;move 3" so that the last 3 or 4 records show too)

-z
Dec 14 '13 #8

NeoPa
Expert Mod 15k+
P: 31,186
I use subroutines from the module below (modRecPos) to help with this. Call SaveRP() from the Form_BeforeUpdate() event procedure when you know the update will go ahead, then call LoadRP() from the Form_AfterUpdate() event procedure to return the recordset to how you had it before as closely as possible.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. 'RecPos
  5. 'lngCurrentRec reflects the position of the selected record within the
  6. '              recordset.
  7. 'lngScroll     reflects the position of the selected record within the currently
  8. '              visible window.
  9. Public Type RecPos
  10.     lngCurrentRec As Long
  11.     lngScroll As Long
  12. End Type
  13.  
  14. 'SaveRP() Saves the current record and scroll position of a form.
  15. Public Sub SaveRP(ByRef rpVar As RecPos, ByRef frmMe As Form)
  16.     Dim intSection As Integer
  17.     Dim ctlVar As Control
  18.  
  19.     With frmMe
  20.         If Not .Visible Then
  21.             With rpVar
  22.                 .lngCurrentRec = -1
  23.                 .lngScroll = -1
  24.             End With
  25.             Exit Sub
  26.         End If
  27.         rpVar.lngCurrentRec = .SelTop
  28.         If .ActiveControl.Section = acDetail Then
  29.             rpVar.lngScroll = CalcScroll(frmMe)
  30.         Else
  31.             .Painting = False
  32.             Set ctlVar = .ActiveControl
  33.             Call .Detail.Controls(0).SetFocus
  34.             rpVar.lngScroll = CalcScroll(frmMe)
  35.             Call ctlVar.SetFocus
  36.             .Painting = True
  37.         End If
  38.     End With
  39. End Sub
  40.  
  41. 'CalcScroll() calculates the number of lines visible above the current record.
  42. Private Function CalcScroll(ByRef frmMe As Form) As Long
  43.     Dim lngPos As Long
  44.  
  45.     With frmMe
  46.         lngPos = .CurrentSectionTop _
  47.                - IIf(.FormHeader.Visible, .FormHeader.Height, 0)
  48.         CalcScroll = Round(lngPos / .Detail.Height, 0)
  49.     End With
  50. End Function
  51.  
  52. 'LoadRP() Refreshes/requeries and repositions the form using the values saved
  53. '  during SaveRP().
  54. Public Sub LoadRP(ByRef rpVar As RecPos, _
  55.                   ByRef frmMe As Form, _
  56.                   strControl As String, _
  57.                   Optional ByVal blnUpdate As Boolean = True, _
  58.                   Optional ByVal strType As String = "Refresh")
  59.     Dim lngTop As Long, lngSel As Long
  60.     Dim blnNewRec As Boolean
  61.     Dim ctlVar As Control
  62.  
  63.     With frmMe
  64.         If rpVar.lngCurrentRec = -1 Then Exit Sub
  65.         .Painting = False
  66.         If OnSubform(frmMe) Then
  67.             For Each ctlVar In .Parent.Controls
  68.                 With ctlVar
  69.                     If .ControlType = acSubform Then _
  70.                         If .Form Is frmMe Then Exit For
  71.                 End With
  72.             Next ctlVar
  73.             Call ctlVar.SetFocus
  74.         End If
  75.         Select Case strType
  76.         Case "Refresh"
  77.             Call .Refresh
  78.         Case "Requery"
  79.             Call .Requery
  80.         End Select
  81.         blnNewRec = (.AllowAdditions) And (.Recordset.Updatable)
  82.         Call DoCmd.GoToRecord(Record:=IIf(blnNewRec, acNewRec, acLast))
  83.         With rpVar
  84.             lngTop = .lngCurrentRec - .lngScroll
  85.             lngSel = .lngCurrentRec + IIf(blnUpdate, 1, 0)
  86.         End With
  87.         If (Not blnNewRec) And (lngSel > .Recordset.RecordCount) Then _
  88.             lngSel = .Recordset.RecordCount
  89.         .SelTop = lngTop
  90.         .SelTop = lngSel
  91.         .Painting = True
  92.         Call .Controls(strControl).SetFocus
  93.     End With
  94. End Sub
This code is used from the main modRecPos module, but I have it located elsewhere as it's more generally useful. You can include it wherever makes best sense to you ;-)
Expand|Select|Wrap|Line Numbers
  1. 'OnSubform() returns True if frmVar is open within a subform on another form.
  2. Public Function OnSubform(frmVar As Form) As Boolean
  3.     OnSubform = True
  4.     On Error Resume Next    'Then statement is executed on error
  5.     If Not TypeOf frmVar.Parent Is Form Then OnSubform = False
  6. End Function
Dec 14 '13 #9

ADezii
Expert 5K+
P: 8,597
FYI: Requerying a Form invalidates any Bookmarks set on Records in the Form, whereas Refresh will not.
Dec 14 '13 #10

100+
P: 547
Thx everybody for the info. Will attempt these "fixes" now.
Dec 15 '13 #11

P: 1
NeoPa, I have the exact dilemma and I tried using your code. Can you show me an example of calling it. I was assuming I could just use them in the BeforeUpdate and the AfterUpdate, but LoadRP requires the parameters. I'm uncertain how to provide them.

For example, I have a subform that requires a requery in order to return a message to a label on the parent form. Unless I run the requery, the label lags behind. But now that I requery, everytime I edit the field, it jumps to the first record.

Your tools sound like they would work perfectly.
Dec 15 '14 #12

100+
P: 547
I struggled for days as my subform combobox uses numerous "if then" statements and validations and eventually, i started to a requery a popup form from the combobox "after update event" (popup=yes/modal=no)that contains the relevant subform. It works fine now and also does not affect performance.
Requery code:
Expand|Select|Wrap|Line Numbers
  1. Forms![Popupform].[subform].Form.Requery
Dec 15 '14 #13

NeoPa
Expert Mod 15k+
P: 31,186
@Monty.
Feel free to post a question thread and link to this one. Also PM me if you'd like me to assist personally.

However, please ensure the question as posted makes good sense and follows the guidance of how to post questions that can be answered. Vague and general questions asking for specifics but without explaining the scenario adequately are likely to get you very little in the way of helpful responses.

I would also suggest that you read what has already been posted in here before formulating your question. Repeating answers because the OP hasn't yet read what has already been posted feels like time wasted and should be avoidable.
Dec 19 '14 #14

Post your reply

Sign in to post your reply or Sign up for a free account.