Subform not updating 1

[TrekBiker]

This is a reporting form for comments and scores at an event.

The bottom subforms get their criteria from the ones above, and the left hand set works properly, showing the scores for each Judge when stepping through all 9 of them, 3 judges for each of 3 companies in this Category.

The top right subform displays everything properly for the opening record, which just happens not to have a comment but that's not an issue. The scores for this Company and Judge appear properly. This is the source query for the current record.

SELECT [Scores Presentation].EntryID, [Scores Presentation].Judge, [Scores Presentation].Score, [Scores Presentation].Criterion
FROM [Scores Presentation]
WHERE ((([Scores Presentation].EntryID)=[forms]![frmWrittenEntries].[sfmPresentationComments].[Form].[EntryID]) AND (([Scores Presentation].Judge)=[forms]![frmWrittenEntries].[sfmPresentationComments].[Form].[Judge]));

The problem is that going to the next records in the top subform doesn't update the scores for each Judge, as below. The inset is the subform's source query, opened after changing to this new data Judge.

The problem seems to be about being able to do a requery on the Scores subform but all my attempts have failed, eg doing AfterUpdate on the Comments subform - the procedure isn't fired by the navigation buttons.

Any thoughts much appreciated.

 

[dhookom]

Can't you use the Link Master/Child properties? If you have a main form with a subform's records filtered by a field value in another subform you can:
In the On Current of the master/parent subform write the link field value to a hidden text box on the main form with code like:

Private Sub Form_Current()
    Me.Parent.Form.txtHiddenJudge = Me.Judge
End Sub

You can then use the Link Master/Child with txtHiddenJudge and Judge.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Thanks for response.

The top two subforms use Link Master/Child for Category. There are a number of companies in each Category, and each company has the Comments provided by the Judges in the panel for that Category. So in the example there are 3 companies in the Category Best B2B Customer Experience, and 3 Judges commenting and scoring them.

The bottom two subforms show the scores given for each EntryID/Judge combination so I suppose they could be embedded in the top subforms then use EntryID/Judge as Link fields. Instead I kept them as separate subforms of the main form, with no link but picking up the EntryID and Judge from the subform above and using them as criteria for the bottom subforms' source queries.

This works for the left-hand set, and I tried to replicate its setup in the right-hand one. But for some reason it doesn't adjust to a new EntryID/Judge combination. As I said, going directly to the source query for the combination properly shows the adjusted scores.

 

[dhookom]

Again, I would the link master child even if your master and child both are subforms on a main form. Use the On Current event of the master subform to place a value in a text box on the main form. Use this text box on the main form as the master of your linking with the child subform.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Ah great, thanks Duane, that works.

I'd done something similar, putting a 'Next' navigation button into the subform, with a line in its procedure to update the Scores subform. Your suggestion is neater.

I'm left wondering why the left hand pair works properly without doing this but the right one doesn't. Anyway, a solution!

 

[dhookom]

You stated "AfterUpdate on the Comments subform - the procedure isn't fired by the navigation buttons" I think you need to use VBA to requery the child subform.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Yes, agree for the right hand set.

The left hand set worked so made me think I could replicate the process, but no. I'm going to use your solution there too, and this has been a great learning experience so thanks again. Another thing I found is that you have to build a procedure for adding a 'Next Record' button to a subform, as it doesn't offer one, unlike for a main form.

 

[dhookom]

I would expect the navigation bar in subforms would be exactly the same as the navigation bar in a main form.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

You're right, it is for the Navigation Bar. What I meant was that if you add a button say to go to Next Record on a subform it doesn't pop up options for doing this or other operations for you. Instead you have to do the VBA procedure yourself.

 

[dhookom]

If the button is on the subform, I would expect it to work. Are you don't see these options in the button wizard in a subform?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Yes, the Command Button Wizard screen doesn't appear when in a subform.

 

[dhookom]

Are you viewing the design of the subform while in the main form or the subform opened in design by itself?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

In the main form. Ah, see what you mean, add the button in Design View of the subform opened separately.