Subform moving to first record after parent Listbox requery

[neelsfer]

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.

[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?

[zmbd]

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.

[PPelle]

Try this if the listbox is loosing its value:

Dim vTemp as Variant ' May change to the datatype of the bound field
 
vTemp=[Forms]![orderf].Form![sfOrderAvgAllQ1]
[Forms]![orderf].Form![sfOrderAvgAllQ1].Requery
[Forms]![orderf].Form![sfOrderAvgAllQ1]=vTemp
 

Or this for bound forms:

Dim strBookmark as String
 
strBookmark=Me.Bookmark
Me.Requery
Me.Bookmark=strBookmark 
 

[zmbd]

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.

[PPelle]

Yes, you're right, I had to make a guess. :) Sorry about that, I didn't see your reply before a made mine.

[NeoPa]

Down to you now Neels ;-)

[neelsfer]

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?

[zmbd]

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

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

Option Compare Database
Option Explicit
 
'RecPos
'lngCurrentRec reflects the position of the selected record within the
'              recordset.
'lngScroll     reflects the position of the selected record within the currently
'              visible window.
Public Type RecPos
    lngCurrentRec As Long
    lngScroll As Long
End Type
 
'SaveRP() Saves the current record and scroll position of a form.
Public Sub SaveRP(ByRef rpVar As RecPos, ByRef frmMe As Form)
    Dim intSection As Integer
    Dim ctlVar As Control
 
    With frmMe
        If Not .Visible Then
            With rpVar
                .lngCurrentRec = -1
                .lngScroll = -1
            End With
            Exit Sub
        End If
        rpVar.lngCurrentRec = .SelTop
        If .ActiveControl.Section = acDetail Then
            rpVar.lngScroll = CalcScroll(frmMe)
        Else
            .Painting = False
            Set ctlVar = .ActiveControl
            Call .Detail.Controls(0).SetFocus
            rpVar.lngScroll = CalcScroll(frmMe)
            Call ctlVar.SetFocus
            .Painting = True
        End If
    End With
End Sub
 
'CalcScroll() calculates the number of lines visible above the current record.
Private Function CalcScroll(ByRef frmMe As Form) As Long
    Dim lngPos As Long
 
    With frmMe
        lngPos = .CurrentSectionTop _
               - IIf(.FormHeader.Visible, .FormHeader.Height, 0)
        CalcScroll = Round(lngPos / .Detail.Height, 0)
    End With
End Function
 
'LoadRP() Refreshes/requeries and repositions the form using the values saved
'  during SaveRP().
Public Sub LoadRP(ByRef rpVar As RecPos, _
                  ByRef frmMe As Form, _
                  strControl As String, _
                  Optional ByVal blnUpdate As Boolean = True, _
                  Optional ByVal strType As String = "Refresh")
    Dim lngTop As Long, lngSel As Long
    Dim blnNewRec As Boolean
    Dim ctlVar As Control
 
    With frmMe
        If rpVar.lngCurrentRec = -1 Then Exit Sub
        .Painting = False
        If OnSubform(frmMe) Then
            For Each ctlVar In .Parent.Controls
                With ctlVar
                    If .ControlType = acSubform Then _
                        If .Form Is frmMe Then Exit For
                End With
            Next ctlVar
            Call ctlVar.SetFocus
        End If
        Select Case strType
        Case "Refresh"
            Call .Refresh
        Case "Requery"
            Call .Requery
        End Select
        blnNewRec = (.AllowAdditions) And (.Recordset.Updatable)
        Call DoCmd.GoToRecord(Record:=IIf(blnNewRec, acNewRec, acLast))
        With rpVar
            lngTop = .lngCurrentRec - .lngScroll
            lngSel = .lngCurrentRec + IIf(blnUpdate, 1, 0)
        End With
        If (Not blnNewRec) And (lngSel > .Recordset.RecordCount) Then _
            lngSel = .Recordset.RecordCount
        .SelTop = lngTop
        .SelTop = lngSel
        .Painting = True
        Call .Controls(strControl).SetFocus
    End With
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 ;-)

'OnSubform() returns True if frmVar is open within a subform on another form.
Public Function OnSubform(frmVar As Form) As Boolean
    OnSubform = True
    On Error Resume Next    'Then statement is executed on error
    If Not TypeOf frmVar.Parent Is Form Then OnSubform = False
End Function

[ADezii]

FYI: Requerying a Form invalidates any Bookmarks set on Records in the Form, whereas Refresh will not.

[neelsfer]

Thx everybody for the info. Will attempt these "fixes" now.

[monty327]

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.

[neelsfer]

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:

Forms![Popupform].[subform].Form.Requery

[NeoPa]

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