none
requery correlated subforms : goto recordnr RRS feed

  • Question

  • I have 2 subfiles

    in the first subfile on the left , there is a total (query)   that I want to be refreshed if 1 palet is added in the right 2nd subfile

    if I requery, in the left subfile the current record becomes 1 and I need to stay on the record I was busy with ?

    how do you do that ?

    Friday, April 28, 2017 10:00 AM

Answers

  • Hi tekoko10,

    you can try to use queries and then try to re query the subform on exit event of another sub form.

    like below.

    code:

    Private Sub Child2_Exit(Cancel As Integer)
    Me.Child0.Requery
    End Sub
      

    you can also put the same code on button.

    so when ever you click button to add record it will requery and update the total count.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by tekoko10 Wednesday, May 3, 2017 8:18 AM
    Monday, May 1, 2017 8:08 AM
    Moderator
  • You'll need to store the current record's primary key value and then perform your requery and then return to that record by using the Bookmark property.  See if https://www.devhut.net/2012/10/19/ms-access-vba-requery-a-form-while-remaining-on-the-same-record/ doesn't offer some insight.

    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Friday, April 28, 2017 1:54 PM
  • Hi tekoko10,

    try something like below.

    Code:

    Private Sub Command12_Click()
    Dim sSQL As String
    sSQL = "INSERT INTO tblCountry1 (Country) VALUES ('" & Me.Text10 & "');"
    DoCmd.RunSQL sSQL
    Me.Child0.Requery
    Me.Child2.Requery
    Call FindLastRecordAdded
    End Sub
    
    
    
    Private Sub FindLastRecordAdded()
        Dim rst As DAO.Recordset
        Dim strCriteria As String
    
        strCriteria = "Country='" & Me.Text10 & "'"
        Set rst = Me.Child2.Form.RecordsetClone
        rst.FindFirst strCriteria
        If rst.NoMatch Then
            MsgBox "Oops. This shouldn't happen.", vbInformation
        Else
            Me.Child2.Form.Bookmark = rst.Bookmark
        End If
    
        Set rst = Nothing
    End Sub

    Note: this example is just for demo. you need to modify it as per your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, May 3, 2017 7:14 AM
    Moderator

All replies

  • You'll need to store the current record's primary key value and then perform your requery and then return to that record by using the Bookmark property.  See if https://www.devhut.net/2012/10/19/ms-access-vba-requery-a-form-while-remaining-on-the-same-record/ doesn't offer some insight.

    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Friday, April 28, 2017 1:54 PM
  • Hi tekoko10,

    you can try to use queries and then try to re query the subform on exit event of another sub form.

    like below.

    code:

    Private Sub Child2_Exit(Cancel As Integer)
    Me.Child0.Requery
    End Sub
      

    you can also put the same code on button.

    so when ever you click button to add record it will requery and update the total count.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by tekoko10 Wednesday, May 3, 2017 8:18 AM
    Monday, May 1, 2017 8:08 AM
    Moderator
  • Hello Mr, Deepak,

    that works, but I want that  the right record stays  highlighted in the left subfile

    Wednesday, May 3, 2017 1:37 AM
  • Hi tekoko10,

    try something like below.

    Code:

    Private Sub Command12_Click()
    Dim sSQL As String
    sSQL = "INSERT INTO tblCountry1 (Country) VALUES ('" & Me.Text10 & "');"
    DoCmd.RunSQL sSQL
    Me.Child0.Requery
    Me.Child2.Requery
    Call FindLastRecordAdded
    End Sub
    
    
    
    Private Sub FindLastRecordAdded()
        Dim rst As DAO.Recordset
        Dim strCriteria As String
    
        strCriteria = "Country='" & Me.Text10 & "'"
        Set rst = Me.Child2.Form.RecordsetClone
        rst.FindFirst strCriteria
        If rst.NoMatch Then
            MsgBox "Oops. This shouldn't happen.", vbInformation
        Else
            Me.Child2.Form.Bookmark = rst.Bookmark
        End If
    
        Set rst = Nothing
    End Sub

    Note: this example is just for demo. you need to modify it as per your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, May 3, 2017 7:14 AM
    Moderator
  • Hi Deepak

    May I ask what recorder you are using? Seems great.


    Best // Peter Forss Stockholm GMT +1.00

    Sunday, May 7, 2017 12:19 PM
  • Peter,

    There are a number of animated gif creation tools, but her​e is a decent free open-source one, see: http://www.screentogif.com


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Sunday, May 7, 2017 1:07 PM
  • Thanks Daniel!

    Best // Peter Forss Stockholm GMT +1.00

    Sunday, May 7, 2017 1:13 PM
  • Re recording: you could consider MSFT's own psr.exe. Try it. It's on pretty much any modern OS. A hidden gem.

    -Tom. Microsoft Access MVP

    Monday, May 8, 2017 1:25 AM
  • Thanks Deepak

    Best // Peter Forss Stockholm GMT +1.00

    Monday, May 8, 2017 4:02 AM
  • Thanks Tom

    Best // Peter Forss Stockholm GMT +1.00

    Monday, May 8, 2017 4:10 AM