none
Filtering out current records in a subform for futher actions RRS feed

  • Question

  • Say, I have a main form frmMain and there is a subform subForm in it. Source for frmMain is Table1 and source for a subform is Table2. The forms are linked by the field JointID. In Table1 JointID is PK and in Table2 JointID is FK. How would VBA code look like to filter out only currently entered records in the subform? In other words, out of all records in Table2 filter only those records that a user has just entered and can see them in the subform at the moment so that you can do some actions with these records next? 

    • Edited by Oleg.bv Tuesday, November 1, 2016 1:42 PM
    Tuesday, November 1, 2016 1:39 PM

Answers

  • I think you are missing me. LinkMasterFields and LinkChildField were set to JoinID when the form was created in the first place. Everything works fine. The question is not about that.

    There is a need to requery the subform (Table2) in order to pass the value from the main form field EnteryDate to the field LogDate in the subform but ONLY for CURRENTLY ENTERED records in the subform.

    Something like:

    ----------------------------

    Dim strSQL As String

    strSQL = "UPDATE Table2 SET LogDate=#" & Format(Me.EntryDate, "yyyy-mm-dd") & "# WHERE JointID=" & Me.JointID
    CurrentDb.Execute strSQL, dbFailOnError

    Me.subform.Requery

    --------------------------

    Problem with this code is that new EntryDate is assigned to LogDate of ALL records in Table2. I need it to be assigned only to the records that the user can see on the screen (just entered).


    • Edited by Oleg.bv Tuesday, November 1, 2016 2:21 PM
    • Marked as answer by Oleg.bv Tuesday, November 1, 2016 7:08 PM
    Tuesday, November 1, 2016 2:20 PM

All replies

  • No code needed. This is one of Access' strong suits. Check the LinkMasterFields and LinkChildField property of the subform control.

    And don't name your subform "subForm": it is a reserved word.


    -Tom. Microsoft Access MVP

    Tuesday, November 1, 2016 1:56 PM
  • Sorry, I do not understand your answer.

    In general, there is a code before to trigger some actions and the code after. This filtering code should be between.

    My form and subform names are different. This is just for simplicity. 

    Tuesday, November 1, 2016 2:01 PM
  • Do me a favor and look up the properties I mentioned in the Help file and try it out by setting both to JointID.

    Alternatively, with the control wizard on, drop the subform on the form and answer the wizard's question about related fields. This question may only come up if you have a relationship between these two tables, per best practices.


    -Tom. Microsoft Access MVP

    Tuesday, November 1, 2016 2:05 PM
  • I think you are missing me. LinkMasterFields and LinkChildField were set to JoinID when the form was created in the first place. Everything works fine. The question is not about that.

    There is a need to requery the subform (Table2) in order to pass the value from the main form field EnteryDate to the field LogDate in the subform but ONLY for CURRENTLY ENTERED records in the subform.

    Something like:

    ----------------------------

    Dim strSQL As String

    strSQL = "UPDATE Table2 SET LogDate=#" & Format(Me.EntryDate, "yyyy-mm-dd") & "# WHERE JointID=" & Me.JointID
    CurrentDb.Execute strSQL, dbFailOnError

    Me.subform.Requery

    --------------------------

    Problem with this code is that new EntryDate is assigned to LogDate of ALL records in Table2. I need it to be assigned only to the records that the user can see on the screen (just entered).


    • Edited by Oleg.bv Tuesday, November 1, 2016 2:21 PM
    • Marked as answer by Oleg.bv Tuesday, November 1, 2016 7:08 PM
    Tuesday, November 1, 2016 2:20 PM