Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: subform to subform date reference in VBA

    I have two similar scenarios where a m/f2 with s/f2 is opening and creating a s/f2 new record. There is no data entry in this s/f2 - it is opened, filled with data from other controls and closed - on it's own.

    The part where I am lost and looking for assistance is the control reference in my date field on s/f1. The data comes from another open m/f1 s/f1. So I open this m/f1 and make a new entry in s/f1 then click a button to open either scenario1 m/f2 s/f2 or scenario2 m/f2 s/f2.

    I've had this running for about a year, and the only way I can get the s/f2's REGDATE to fill in is with Now() for today's date which is inaccurate. I can't get it to "find" the date I entered in s/f1. I have attached a picture for reference.

    SCENARIO1 s/f2:
    Private Sub Form_Current()
    Me.STATLU = [Forms]![RequestResp2611]![STATLU]
    Me.REGDATE = [Forms]![RequestResp2611]![RQTsfApp2611].Form!MGRDATE
    Me.SLU = [Forms]![RequestResp2611]![SLU]
    Me.SLU2 = [Forms]![RequestResp2611]![SLU2]
    Me.PTLU = [Forms]![RequestResp2611]![PTLU]
    End Sub

    SCENARIO2 s/f2:
    Private Sub Form_Current()
    Me.STATLU = [Forms]![RequestResp2611]![STATLU]
    Me.REGDATE.Value = [Forms]![RequestResp2611]![RQTsfApp2611].[Form]![MGRDATE]
    Me.SLU = [Forms]![RequestResp2611]![RSLMNRQT]
    Me.SLU2 = [Forms]![RequestResp2611]![RSLMNRQT2]
    End Sub

    Im not sure if its because its on a s/f looking to another s/f and I wrote it wrong, or I wrote it wrong because its a date data type. Now that I've attempted this code, it has stopped the entire thing from triggering....so I'm guessing I have to put date somewhere else so that the other controls can get their info from m/f1 and REGDATE can get it's from s/f1?

    Your wisdom is appreciated!
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Without knowing how the form names in your sample code relate to all the 'm/f's and 's/f's in your problem description, it's a little hard to diagnose the problem. (Also, check out the CODE tags - they make code samples easier to read.)

    That said, this could be a simple case of form event order mismatches. This link should point you in the right direction - the important point is that the subform's data is loaded and its event are fired before the parent form's, so referring to the parent form in these subform events might not yield the desired results.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jun 2010
    Posts
    186
    this is excellent information - THANK YOU!
    stuff I thought I knew LOL

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Happy to help!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •