UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Forcing List Box Afterupdate In Vba, Access 2016    
 
   
LagoDavid
post May 4 2019, 10:10 AM
Post#1



Posts: 556
Joined: 12-October 03
From: Texas


I have an unbound form with 3 cascading list boxes and I am wanting all three list boxes to populate when the form is opened (the form is a subform in a Navigation form). I am able to make the second list box correctly populate, but the same process does not make the third list box populate. In the form load event, I am setting the value of the first list box in VBA using Me.lstOne.Value = Me.lstOne.ItemData(1). When I do this it fires the AfterUpdate Event for list one as I hoped. In the AfterUpdate event for list one, I set the value of list two, again using Me.lstTwo.Value = Me.lstTwo.ItemData(1). However, this is not causing the AfterUpdate event for list two to fire. I am having to add the statement "Call list2_AfterUpdate". List2 AfterUpdate is where the SQL for list3 is built.

What am I doing wrong that is making this seemingly inconsistent result? My actual code is below.
List One = lstLIMSProjects
List Two = lstSamples
List Three = lstSampleResults

CODE
Private Sub Form_Load()
   Debug.Print "When frmLIMSProjectReportSetup opens Corp, Site, User, Role = " & TempVars!tvCorporationID & ", " & TempVars!tvSiteID & ", " & TempVars!tvUserID & ", " & TempVars!tvRoleID
   If IsNull(TempVars!tvSiteID) Then
      Me.cboSite.Value = 1
      Me.cboSite.SetFocus
      Me.cboSite.Dropdown
   Else
      Me.cboSite.Value = TempVars!tvSiteID
   End If

   Me.lstLIMSProjects.Requery
   Me.lstLIMSProjects.Selected(1) = True
   Me.lstLIMSProjects.Value = Me.lstLIMSProjects.ItemData(1)   'sets the value of the list and fires the AfterUpdate event for the list
   Debug.Print "just returned from the lstLIMSProjects AfterUpdate event, which also causes lstLIMSSamples AfterUpdate to fire"
   Me.lstSampleResults.Requery
End Sub

Private Sub lstLIMSProjects_AfterUpdate()
   Debug.Print "just entered the AfterUpdate EVent of lstLIMSProjects"
   Me.lstSamples.Requery
   Me.lstSamples.Selected(1) = True  'this only highlights the first row; it does not set the value of the list box
   Me.lstSamples.Value = Me.lstSamples.ItemData(1) 'Sets value but did not fire AfterUpdate; value is in row 1, not row zero since Row 0 is the column name in a zero based list with column heads

End Sub


Private Sub lstSamples_AfterUpdate()
   Debug.Print "After updating lstSamples, the value is: " & lstSamples.Value
'build the query statement for the lstSampleResults BASED ON THE SAMPLE SELECTED IN THE LIST OF SAMPLES
   strSelect = _
     "SELECT " & _
        "(BetzmanParameterName & ', ' & Units) AS [Analysis], " & _
        "IIF (tblSampleResultsLIMS.Comparator ='=', tblSampleResultsLIMS.Result, " & _
        "(tblSampleResultsLIMS.Comparator & ' ' & tblSampleResultsLIMS.Result)) AS [Results] "
          
   strFrom = _
        "FROM (tblSampleResultsLIMS " & _
         "INNER JOIN tblParametersBetzmanLIMS ON tblSampleResultsLIMS.ParameterBetzmanLIMSID = tblParametersBetzmanLIMS.ParameterBetzmanLIMSID) " & _
         "LEFT JOIN lu_tblAmineN ON tblParametersBetzmanLIMS.ParameterBetzmanLIMSID = lu_tblAmineN.ParameterBetzmanLIMSID   "
    
    Select Case True
      Case TempVars!tvHideLessThan = -1 And TempVars!tvHideInsignificant = 0 'hide less than data but show all other
         strWhere = "WHERE tblSampleResultsLIMS.Comparator ='=' And tblSampleResultsLIMS.SampleLIMSID = " & Me.lstSamples & " "
      Case TempVars!tvHideLessThan = 0 And TempVars!tvHideInsignificant = 0 ' show all data
         strWhere = "WHERE tblSampleResultsLIMS.SampleLIMSID = " & Me.lstSamples & " "
      Case TempVars!tvHideLessThan = 0 And TempVars!tvHideInsignificant = -1 ' hide insignificant but show all others including less than
         strWhere = "WHERE tblParametersBetzmanLIMS.Insignificant = False and tblSampleResultsLIMS.SampleLIMSID = " & Me.lstSamples & " "
      Case TempVars!tvHideLessThan = -1 And TempVars!tvHideInsignificant = -1  'hide all less than and all insignificant
         strWhere = "WHERE tblParametersBetzmanLIMS.Insignificant = False and tblSampleResultsLIMS.Comparator = '=' and tblSampleResultsLIMS.SampleLIMSID = " & Me.lstSamples & " "
      Case Else
         MsgBox "Error in Select Case Statement", vbOK + vbExclamation, "Error"
   End Select
    
    strOrderBy = _
        "ORDER BY " & _
        "tblParametersBetzmanLIMS.ParameterDefaultSort"
        
    strSQL = strSelect & strFrom & strWhere & strOrderBy


    Debug.Print "Just completed after update event on lstSamples"
    Debug.Print strSQL
    Me.lstSampleResults.RowSource = strSQL

End Sub
Go to the top of the page
 
theDBguy
post May 4 2019, 11:08 AM
Post#2


UA Moderator
Posts: 77,727
Joined: 19-June 07
From: SunnySandyEggo


Hi. I am not sure I understand. When you modify a control’s value using code, the AfterUpdate shouldn’t fire. So, it would be nice to verify what you’re saying if you could post a demo version of your db.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
LagoDavid
post May 4 2019, 02:28 PM
Post#3



Posts: 556
Joined: 12-October 03
From: Texas


working on a demo now, starting from scratch because this is something I need to understand. It will take me a while as I am not proficient at this. From my searching I was under the impression same as you that setting the value of a list box using VBA would not fire the AfterUpdate Event. But now I am seeing something different. Or at least I think I am.

I am using O365. Can Access have been changed recently so setting the value of a list box using VBA and ItemData property would now cause the After Update to fire?
Go to the top of the page
 
theDBguy
post May 4 2019, 02:45 PM
Post#4


UA Moderator
Posts: 77,727
Joined: 19-June 07
From: SunnySandyEggo


QUOTE
I am using O365. Can Access have been changed recently so setting the value of a list box using VBA and ItemData property would now cause the After Update to fire?
I can't say for sure but based on history, I highly doubt the Access Team would implement a behavior change that could potentially affect all other existing applications that may already be relying on the old behavior. Besides, I really believe it's by design, so that any other way a control value is changed, it wouldn't cause the application to run some code it's not supposed to and potentially interfere with other operations that's going on at the same time.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
LagoDavid
post May 4 2019, 02:58 PM
Post#5



Posts: 556
Joined: 12-October 03
From: Texas


check this out and see if you agree this happens:
In the Form Load event, when I select a row in the first list box by saying
Me.lstOne.Selected(0) = True
will cause the after update event for lstOne to fire

However, nothing I can do in the AfterUpdate event of lstOne will force the AfterUpdate Event for lstTwo and etc.
Go to the top of the page
 
theDBguy
post May 4 2019, 03:06 PM
Post#6


UA Moderator
Posts: 77,727
Joined: 19-June 07
From: SunnySandyEggo


Okay, I just tried it. Created a listbox on a form. Added the code in the Load event like you showed. Added an AfterUpdate event on the Listbox to display a MsgBox. Launched the form and nothing happened. But, clicking on the other items on the list will show the MsgBox.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
pere_de_chipstic...
post May 4 2019, 03:40 PM
Post#7


UtterAccess Editor
Posts: 10,594
Joined: 8-November 07
From: South coast, England


PMFJI

You can trigger the after update event directly in code, without needing to set the focus to that control ie

CODE
Me.ListBoxName = 123
ListBoxName_AfterUpdate


hth

--------------------
Warm regards
Bernie
Go to the top of the page
 
LagoDavid
post May 4 2019, 05:38 PM
Post#8



Posts: 556
Joined: 12-October 03
From: Texas


Yes, thank you. However, this was more a question of understanding how Access works rather than a question of how to get a list box to update. I knew that I could call the After_Update code, but I am seeing Access do this without that call in my form load code. DBG does not get the same results.

DBG, here is my complete set of code for the form load and the AfterUpdate for the first list box. My AfterUpdate fires with the code line in FormLoad which says Me.lstCorp.Selected(1) = True (lstCorp is List Box One). My MsgBox which is in the AfterUpdate event pops up, and yours does not. I do not understand why that is.
My particular list box names in order of hiearchy are:
lstCorp
lstSites
lstAreas
lstUnits


CODE
Private Sub Form_Load()
   Debug.Print "frmTestCascadeListUpdate2 just loaded"
   Debug.Print "lstCorp, lstSite, lstArea, lstUnit = " & lstCorp.Value & ", " & lstSites.Value & ", " & lstAreas.Value & ", " & lstUnits  'just shows that when the form initially loads all the list box values are empty
   Debug.Print "about to requery lstCorp"
   Me.lstCorp.Requery  ' this did not result in the AfterUpdate event firing
   Debug.Print "requeried lstCorp and now about to Select row 1"
   Me.lstCorp.Selected(1) = True 'this causes the AfterUpdate event to fire
   Debug.Print "About to set value of lstCorp using ItemData statement and see if AfterUpdate fires"
   Me.lstCorp.Value = Me.lstCorp.ItemData(1)   'sets the value of the list to the second row
   Debug.Print "returned to Form Load event after setting value of first list box, Corp"
End Sub

Private Sub lstCorp_AfterUpdate()  'this is list box 1
   MsgBox "lstCorp AfterUpdate event just fired"
   Debug.Print "lstCorp After Update just fired"
   Debug.Print "lstCorp, lstSite, lstArea, lstUnit = " & lstCorp.Value & ", " & lstSites.Value & ", " & lstAreas.Value & ", " & lstUnits
   Me.txtCorpID = Me.lstCorp.Value
   Me.lstSites.Requery
   Me.lstSites.Selected(0) = True
   Me.lstSites = Me.lstSites.ItemData(0)  'as expected, none of these three lstSites statements will cause the lstSites AfterUpdate to fire, but the correct data does show in lstSites

   Debug.Print "lstCorp, lstSite, lstArea, lstUnit = " & lstCorp.Value & ", " & lstSites.Value & ", " & lstAreas.Value & ", " & lstUnits

End Sub
Go to the top of the page
 
theDBguy
post May 4 2019, 07:50 PM
Post#9


UA Moderator
Posts: 77,727
Joined: 19-June 07
From: SunnySandyEggo


Hi. As soon as you're able to post a demo showing this unusual behavior, we can verify if something's changed in Access or if there's something else going on. Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
LagoDavid
post May 4 2019, 11:18 PM
Post#10



Posts: 556
Joined: 12-October 03
From: Texas


will do tomorrow
Go to the top of the page
 
moke123
post May 5 2019, 03:52 AM
Post#11



Posts: 1,380
Joined: 26-December 12
From: Berkshire Mtns.


Heres your code without all the extra debugs,etc (easier to read)

CODE
Private Sub Form_Load()

    Me.lstCorp.Requery  ' this did not result in the AfterUpdate event firing

    Me.lstCorp.Selected(1) = True    'this causes the AfterUpdate event to fire

    Me.lstCorp.Value = Me.lstCorp.ItemData(1)   'sets the value of the list to the second row

End Sub

Private Sub lstCorp_AfterUpdate()  'this is list box 1

    Me.txtCorpID = Me.lstCorp.Value

    Me.lstSites.Requery

    Me.lstSites.Selected(0) = True

    Me.lstSites = Me.lstSites.ItemData(0)  'as expected, none of these three lstSites statements will cause the lstSites AfterUpdate to fire, but the correct data does show in lstSites

End Sub


these 2 lines are essentially the same but I do see the after update event firing with the .selected code.
CODE
    Me.lstCorp.Selected(1) = True    'this causes the AfterUpdate event to fire

    Me.lstCorp.Value = Me.lstCorp.ItemData(1)   'sets the value of the list to the second row


heres what I did...
Created a form with a list box and added both those methods of selecting from the list to the on load event.
Added a msgbox to the after update event of the list.
Commented out the .selected code.
Close and open the form and the after update does not fire.
I then reversed it and commented out the .itemdata() code and un-commented the .selected code.
Close and open the form and the after update event does in fact fire.

Go to the top of the page
 
moke123
post May 5 2019, 10:26 AM
Post#12



Posts: 1,380
Joined: 26-December 12
From: Berkshire Mtns.


I've been looking around but haven't found an answer to why the .selected method fires the after update event. I don't recall ever seeing a situation where setting a control with code fires an event for that control. At least none come to mind. I have never used .selected with a regular list box. I've only used it with multi-select list boxes.

Here's an example with 3 methods - .Selected, listbox = , and .itemdata().

You'll notice that using the .selected method the after update (listbox.requery) is fired without being specifically called.
With the other 2 methods the after update does not fire unless specifically called.

I also tried it with a multi-select list box but the .selected does not fire the event.

Attached File  DotSelected.zip ( 37.33K )Number of downloads: 5
Go to the top of the page
 
missinglinq
post May 5 2019, 12:33 PM
Post#13



Posts: 4,678
Joined: 11-November 02



QUOTE
...I don't recall ever seeing a situation where setting a control with code fires an event for that control...


It's not of any practical use, really, as it still requires two lines of code, but

ControlName.SetFocus
Me.ControlName.Text = "Whatever"


will cause the AfterUpdate event...and I assume other events...to fire.

Linq ;0)>
This post has been edited by missinglinq: May 5 2019, 12:34 PM

--------------------
BTW...The Monkey is laughing at me...not at any other poster!

The problem with making anything foolproof...is that fools are so darn ingenious!

All posts/responses based on Access 2003/2007
Go to the top of the page
 
LagoDavid
post May 5 2019, 02:33 PM
Post#14



Posts: 556
Joined: 12-October 03
From: Texas


Attached is my demo DB. Please keep in mind I am a hobbyist at this and not well skilled.

I got into this b/c I have several forms which are nothing but 2-4 list boxes to show related data. I wanted all the list boxes to correctly populate when the form loaded and also change appropriately when a parent list box was changed. So I started trying to figure how to make the After_Update events fire with VBA using .Selected and .ItemData, and soon through reading learned that you had to actually call the After_Update event for the list box. Then I accidentally found the After_Event being called from my Form Load code.

Here is what I think I have learned from my Demo and Moke123:
1. The After_update event will fire from the Form Load event if you use the .Selected property.
2. The ItemData property will not fire the After_Update event, even though it does in fact load the control with the correct value
3. If you use the ItemData property in the Form Load event before you use the .Selected property, the .Selected property WILL NOT FIRE the AFTER UPDATE event. Go figure. Moke123 said SELECTED and ITEMDATA do the same thing. I was falsely under the impression that .Selected just highlighted the selected row and did not change the value of the control, and that you had to use ItemData to actually change the value.
4. The only place the .Selected property will cause the AfterUpdate event to fire is in Form Load (maybe Form Open; I have not tried it). It will not work in another AfterUpdate sub. See in the demo where I used the .Selected property in the AfterUpdate event of list one, hoping to fire the AfterUpdate event of List two.

Thanks for the feedback everyone.

I think I am going to be safe and just always call the after update event.
Attached File(s)
Attached File  Cascade4ListBoxes.zip ( 66.67K )Number of downloads: 12
 
Go to the top of the page
 
LagoDavid
post May 5 2019, 05:57 PM
Post#15



Posts: 556
Joined: 12-October 03
From: Texas


I need to correct #3 above. The .Selected statement did not fire the AfterUpdate I assume because my Select statement did not change the value. When I went back and changed the code so that ItemData referred to row 1 and then changed the Select statement to refer to row 2, the Select statement did fire the AfterUpdate event.
Go to the top of the page
 
theDBguy
post May 6 2019, 01:14 PM
Post#16


UA Moderator
Posts: 77,727
Joined: 19-June 07
From: SunnySandyEggo


Hi. Thank you all for posting some demos. This clarifies it for me now. The AfterUpdate event "should" fire only if the value is changed in the control. Now, I've never run into any situation where modifying the control's value using VBA triggered the AfterUpdate, so this is news to me. Using the last demo, since the listbox is unbound, switching between design view and form view retains the previous value. So, when the form first loads, the value is changed and the event fires. Switching to design view and then back to form view, the value is retained from the previous change, so changing it again through the Load event doesn't really change it and therefore the event does not fire. This can be checked by opening the form, clicking on a different row to highlight it, switch to design view and then back to form view, and the event will fire consistently because the value was actually changed. This is probably why you said you had to exit out of Access to make sure everything was reset.

Now, as far as the Access Team possibly changing a behavior in the latest updates to Access, I ran the same experiment using Access 2010 and see the same behavior. So, if someone would like to try it out on an even earlier version, please let us know. However, I am pretty sure what I said about MS changing something that could affect existing apps (as long as it's not something totally wrong and really needs to be changed), it is not something they will likely do.

Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
pere_de_chipstic...
post May 6 2019, 03:17 PM
Post#17


UtterAccess Editor
Posts: 10,594
Joined: 8-November 07
From: South coast, England


Just to clarify, the After Update Event will fire provided a. that the relevant control has the focus and b. the .text method is used ( as missinglinq quotes)

This works for Text boxes and Combo boxes but not for list boxes as there is no .Text method available for a listbox. Hence I always use "ControlName_AfterUpdate" as this is consistent for all controls - the only proviso is that you must make the after update event a Public rather than a Private sub if you want to run the VBA from any other code other than code on the associated form.

hth

--------------------
Warm regards
Bernie
Go to the top of the page
 
theDBguy
post May 6 2019, 03:45 PM
Post#18


UA Moderator
Posts: 77,727
Joined: 19-June 07
From: SunnySandyEggo


Hi Bernie. Thanks for the additional info. This is probably a question for the experts but why would modifying the Text property be considered an update? I thought all along the AfterUpdate event only fires when the Value of the control is changed.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
pere_de_chipstic...
post May 6 2019, 04:36 PM
Post#19


UtterAccess Editor
Posts: 10,594
Joined: 8-November 07
From: South coast, England


Hi DBguy

I've imagined the .Text method to be the equivalent of typing the string into the control or, if you like, a more consistent alternative to 'SendKeys', which would explain(?) why there isn't a .Text method for a list box as the AfterUpdate fires for the first character typed into a list box (provided it is a valid first character).

Cheers

--------------------
Warm regards
Bernie
Go to the top of the page
 
theDBguy
post May 6 2019, 04:53 PM
Post#20


UA Moderator
Posts: 77,727
Joined: 19-June 07
From: SunnySandyEggo


Maybe... But simply typing characters into a Textbox doesn't fire the AfterUpdate event until you tab out of it, right? At which point, I imagine the Value has changed.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    5th April 2020 - 12:47 AM