Requery 2nd subform when different record highlighted on first subform

[Rik_StHelens]

Hi all,

I have two sub forms on a main form.

Subform 1 displays information which comes from a query, filtered using two combo boxes on the same form.

The active record on the data sheet has two field values output to hidden text boxes on the form.

These text boxes then provide the values used for the query displayed on the 2nd sub form.

When I highlight a new record in subform 1, the text box values change, but the values in sub form 2 do no.

Which would be the best method to use to get this to requery?

 

[CJ_London]

in the on current event of subform1 put the following code

parent.subform2.requery

 

[Rik_StHelens]

Hi there,

After posting I managed to find the following:

http://bytes.com/topic/access/answers/609424-how-do-i-requery-subform-another-subform

So I tried the following which didn't work:

Private Sub Form_Click()

Me.Parent![qryFindTyres subform].Form.Requery

End Sub

I also tried as you suggested but no luck.

I populate the hidden text boxes with '=[Forms]![frmSearch]![frmSize]("Front Tyre Size")'. Is it possible that the VBA module runs before that value updates int he text box as this isn't done in VBA?

 

[Rik_StHelens]

AhA!

It works, but not when I click on the RECORDS in the grid view, only when I click on the subform elsewhere.

Any ideas?

 

[CJ_London]

you should have the code in your subform current event - it looks like you have it in the click event

 

[Rik_StHelens]

No luck there I'm afraid.

As I said, could it be that when the record is changed, the values in the hidden text boxes are outside of VBA, and therefore changed after the VBA has been run?

 

[CJ_London]

Without seeing your form design it is difficult to advise further. My only other thought is are you using a split form? - I don't use them myself but that might be having an effect

 

[Rik_StHelens]

Nope, just a standard form with two sub forms.

 

[CJ_London]

Sorry, can't suggest anything else

 

[spikepl]

It works, but not when I click on the RECORDS in the grid view, only when I click on the subform elsewhere.

means that the code works just fine, but you did not follow CJ's suggestion exactly and put it in the subform's OnCurrent event.

BTW: stuff like "no luck", "no avail", "doesn't work" is fluff and useless for people who want to help you. Always state what you intended, what you did, what the code did, and any error messages and location.

 

[Rik_StHelens]

means that the code works just fine, but you did not follow CJ's suggestion exactly and put it in the subform's OnCurrent event.

BTW: stuff like "no luck", "no avail", "doesn't work" is fluff and useless for people who want to help you. Always state what you intended, what you did, what the code did, and any error messages and location.

I did place the code in the sub form's OnCurrent event as advised.

Unfortunately I didn't get an error message, and nothing changed on the form.

As CJ said it could be design specific in regards to how i've constructed the form, and I appreciate the help offered.

As there are a mass of linked tables it might take some time to create a slimmed down version i can upload if i want someone to see the form design, so I'll keep digging for now.