FormSubforms - Microsoft Access Forum - Computer Learning Zone


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

FormSubforms

Kim Mittet

2 years ago

I have a form X which contains a subform Y.
Form X is based on table Z and contains the field Z1.
On the form i have a dropdown list with the values from field Z1, when choosing from the list the chosen post is displayed in the form - so far so good.
In the subform is a number of post, one for each year, which shows the values for the displayed year, based on the value in form X. The displayed post is always the first available year.
But i want to display the post i subform Y for the current year.
In short when i choose a post in form X i want show the corresponding post, for the current year, in subform Y.

Anyone have a solution for that ?

Kevin Yip @Reply

2 years ago

You usually use the "link master fields" and "child master fields" of the subform Y's property sheet to display the subform's records based on the main form's record.  If that is not possible, you need to change subform Y's filter and/or record source based on values from form X.  You need VBA to do this.  Add an AfterUpdate event of what you will change on form X.  Add VBA code to that event to change subform Y's filter and/or record source.  For example:

Private Sub ValueFromFormX_AfterUpdate()
    ' Change subform Y's filter
    Me.SubformY.Form.Filter = "[Field1] = " & Me.ValueFromFormX
    Me.SubformY.Form.FilterOn = True

    ' Change subform Y's record source
    Me.SubformY.Form.RecordSource = " SELECT ... WHERE [Field1] = " & Me.ValueFromFormX
End Sub

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

Next Unseen

New Feature: Comment Live View