Combo-box not refreshing 1

[1DMF]

Hi,

I have a parent form and then a subform, the subform has a dropdown list based on a particular field value on the parent form.

When the parent current record changes the linked data in the subform correctly changes, except the drop down list doesn't and it still shows the original content from when it was first populated.

On the parent 'on current' event I have had to put a "me.subform.form.refresh"

Why don't dropdown lists refresh themselves when all other bound form fields do.

And if you have a limit to list combo and the previous selection doesn't contain the new value, how does that even work?

Is this a known bug or have I done something wrong?

Thanks,
1DMF


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[dhookom]

Have you attempted to Requery the subform rather than Refresh?

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

yes, requery doesn't work

Me.SubQualifications.Requery

refresh does, but I had to stop errors...

On Error Resume Next
Me.SubQualifications.Form.Refresh

This is because the subform is dynamically loaded only when the relevant tab(page) is clicked so if not on that tab, the sub form doesn't exist to refresh.

I tried to do a requery on the parent Me.Requery on the 'form_current' event but that just hung access.

I don't understand why bound combo boxes on linked subforms don't refresh/requery themselves, all the other data that is bound on the subform correctly refreshes automatically when the parent record changes, so why not the combo?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[dhookom]

I would try:

Me.SubQualifications.Form.Requery

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

Doesn't work, original values are left in the dropdown

Only : Me.SubQualifications.Form.Refresh

Works



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[dhookom]

How many combo boxes do you have in the subform? What are their Row Sources?

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

One combo who's row source is a bound form query.

The query has a selection criteria with a where clause based on the parent form's data.

SELECT Scope.Scope, Scope.Description
FROM Scope
WHERE (((Scope.Usage)="A") AND (([Forms]![Contacts].[MembershipLevel])="Trainee" Or ([Forms]![Contacts].[MembershipLevel])="Adviser")) OR (((Scope.Usage)="A" Or (Scope.Usage)="P") AND (([Forms]![Contacts].[MembershipLevel])="Appointed Rep" Or ([Forms]![Contacts].[MembershipLevel])="Controller"))
GROUP BY Scope.Scope, Scope.Description
ORDER BY Scope.Scope;

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[dhookom]

You may need to specifically requery the combo box.

You can also simplify your query by replacing:

([Forms]![Contacts].[MembershipLevel]="Trainee" Or [Forms]![Contacts].[MembershipLevel]="Adviser")

with

([Forms]![Contacts].[MembershipLevel] IN ("Trainee", "Adviser"))

SELECT Scope.Scope, Scope.Description
FROM Scope
WHERE (Usage="A" AND [Forms]![Contacts].[MembershipLevel] IN ("Trainee", "Adviser")) OR 
(Usage IN ("A", "P") AND ([Forms]![Contacts].[MembershipLevel] IN ("Appointed Rep","Controller")
GROUP BY Scope.Scope, Scope.Description
ORDER BY Scope.Scope;

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

Hi Duane,

Bingo!

On Error Resume Next
Me.SubQualifications.Form.Scope.Requery

Not sure why requerying the form doesn't include the combo drop down list?

Or why I even need to requery anything anyway, if I change record on the parent, I would expect all bound subforms to requery themselves to ensure the correctly linked data is shown.

Oh well, got there in the end correctly requerying the combo instead of refreshing the form which technically is updating the DB, which isn't what I was trying to do!

With regard to the combo query, remember I didn't write it, that's what you get when you click SQL view in the query designer window when using a bound query. I guess I could have created an external query written by hand and bound that to the row source, or typed the SQL myself directly into the row source, is there any benefit to doing that rather than just using the access GUI?

Thanks for the help, appreciated.
1DMF.





"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[dhookom]

Regarding the SQL, you can easily type the IN ( ) clause in the criteria rather than using multiple ORs

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

ah right , my bad, got ya!

Thanks.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

SELECT Scope.Scope, Scope.Description
FROM Scope
WHERE (((Scope.Usage)="A") AND (([Forms]![Contacts].[MembershipLevel]) In ("Trainee","Adviser"))) OR (((Scope.Usage) In ("A","P")) AND (([Forms]![Contacts].[MembershipLevel]) In ("Appointed Rep","Controller")))
GROUP BY Scope.Scope, Scope.Description
ORDER BY Scope.Scope;

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music