Issue Changing Value on Subform - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Issue Changing Value on Subform

Jerry Fowler

3 years ago

Having issues with changing a value in a field on a Form from a Subform.

I have a form (VoucherF) that has a Subform (VoucherDetailF).  The Subform has a date field (VoucherDate) and the form has a field (Active)

When the VoucherDate has a date value in it I want the Active field to be True and if no date to be False.  This is working fine currently using VBA on the afterupdate event on VoucherDate.

Private Sub VoucherDate_AfterUpdate()

On Error Resume Next

    If IsNull(Me!VoucherDate) Then
        MsgBox "There is no date"
        Forms!VoucherF.Active = True
    Else
        MsgBox "There is a date"
        Forms!VoucherF.Active = False
    End If

End Sub

Now the trouble came in when I placed the form VoucherF onto a main form (MainMenuF).  So I tried multiple variations of the following VBA code

Private Sub VoucherDate_AfterUpdate()

On Error Resume Next

    If IsNull(Me!VoucherDate) Then
        MsgBox "There is no date"
        Forms!MainMenuF!Form!VoucherF.Active = True
    Else
        MsgBox "There is a date"
        Forms!MainMenuF!Form!VoucherF.Active = False
    End If

End Sub

And it stopped working.  I'm sure I've gone wrong somewhere but not sure where.  I've watched the videos on subforms and changing values from a subform, then just lastly the Bang v Dot video

Thanks

Jerry Fowler

	Jerry Fowler OP @Reply 3 years ago
Meant to say the 2 MsgBox lines were just for testing

	Kevin Robertson @Reply 3 years ago
Try this: `If IsNull(VoucherDate) Then Forms!MainMenuF!VoucherF.Form!Active = True Else Forms!MainMenuF!VoucherF.Form!Active = False End If`

	Jerry Fowler OP @Reply 3 years ago
Kevin I tried that this morning and sorry it did not seem to make a difference.

	Kevin Robertson @Reply 3 years ago
Going to need to see how your form is set up. Can you post a screenshot please?

Kevin Yip @Reply

3 years ago

Hi Jerry, try this code:

  Private Sub VoucherDate_AfterUpdate()
    Parent!Active = IsNull(Me!VoucherDate)
  End Sub

"Parent" is an Access keyword.  It refers to the parent form of the current form.  Since VoucherF is always the parent form of VoucherDetailF, Parent!Active will always point to the right place no matter where you put VoucherF.  If you use the code above, you don't need to change the code when you move VoucherF to inside MainMenuF -- because "Parent" will always refer correctly to VoucherF.

	Kevin Yip @Reply 3 years ago
And if that doesn't work, remove "On Error Resume Next" to see if there is any error message.

	Jerry Fowler OP @Reply 3 years ago
Kevin Yip that worked great!!! Just so I got this, it works because Active is a True/False field and the IsNull returns a True/False value, correct? Then to requery the parent form would I use Parent.Requery or refresh use Parent.Refresh? Thanks for the help.

	Kevin Yip @Reply 3 years ago
That's correct on both counts. Glad it's working for you.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View