Dynamic update of combobox via AfterUpdate and Me.Requery (1 Viewer)

BumpyRide

New member
Joined
May 14, 2024
Messages
5
Hey AccessWorld Community,

I'm currently tinkering with an Access app for practice and fun, but I've hit a snag and could use some help (I've attached the form with the issue to this post for reference)

Here's the situation:

In my app, I've set up a combobox "member_selection_C" to select user identifiers from a table "MEMBRES".
After selecting a value in "member_selection_C", an AfterUpdate event triggers, dynamically updating in VBA the SQL of a query named "AfterUpdateIssueDemo_Q".

Then "term_selection_C" is another combobox meant to display values based on the dynamically updated query.
Despite verifying that the SQL updates correctly and the AfterUpdate event fires as expected, "term_selection_C" doesn't update its content automatically (I've made sure to use Me.term_selection_C.Requery just in case...)

At the moment, "term_selection_C" only updates when I manually refresh the form.
Any suggestions on what might be causing this hiccup or how I can troubleshoot it? I'm all ears for your insights and advice!

Thanks a bunch!
 

Attachments

Yes, Ive tried that too... (Me.Refresh)
 
Code:
Private Sub term_selection_C_Enter()
  Dim strSql As String
  strSql = "SELECT TERMS.ID_TERM, TERMS.TERM, PAYMENTS_.ID_MEMBER, PAYMENTS_.ID_TERM_ " & _
             "FROM TERMS left JOIN (SELECT ID_MEMBER, ID_TERM as ID_TERM_ " & _
             "FROM PAYMENTS " & _
             "WHERE ID_MEMBER = " & Me.member_selection_C.Value & ") AS PAYMENTS_ ON TERMS.ID_TERM = PAYMENTS_.ID_TERM_ " & _
             "WHERE ID_TERM_ IS NULL ORDER BY TERMS.ID_TERM"
  Me.QueryString = strSql
  Me.term_selection_C.RowSource = strSql
End Sub
 
Works for me!
Thanks MajP :)
(Im still curious tho, do you have an idea on why what I did does not work??)

Thanks again Accessworld fam!!
 
In truth I do not know why it does not work. I would have expected this to work.
In your code if you replace the combo requery with
Code:
Me.term_selection_C.RowSource = "AfterUpdateIssueDemo_Q"
It works.

However, the reason that works is whenever you replace the rowsource that forces a requery, so you would think the requery alone should work.

Your code is fundamentally correct. I like to use the enter event, finding that more fool proof to do the requery right when I need it. My code is simpler in that you do not have to work with the QDF. However, there is nothing wrong with that because there are times you have to or want to work with the QDF.
 
I think it is your data?
Try running that code in the QBE window.
Nothing seems to change for me?
I get the same data for 2 & 3 as I do for 5
 
That is correct. Only 1 is different from the others. You can add some data to the payments table for other members.
PAYMENTS PAYMENTS

ID_PAYMENTID_MEMBERID_TERM
1386​
1​
1​
1387​
1​
2​
 
So could it be the O/P has not closed the qdf and hence saved the altered sql string?

Code:
qdf.Close

I do see 1 is diferent with the above in place?
 
I do see 1 is diferent with the above in place?
I do not. Only when I swap these lines. Did you try the close with just the requery?
Code:
     Me.term_selection_C.RowSource = "AfterUpdateIssueDemo_Q"
   '  Me.term_selection_C.Requery
 
I believe I did. 🤔
In Aldi car park atm with a passenger.
I will check when I get home.

Yes it does, or so I believe?
However I put a breakpoint on the Close and ran the query manually and that had the new value?

Does a requery just run what is in memory? and so would be the old value?
 

Attachments

Last edited:
Huge shoutout to everyone for their dedication and willingness to share. I'm learning so much from both of you right now!

(Stay safe on the road Gasman :LOL:)
 

Users who are viewing this thread

Top Bottom