Change Event vs AfterUpdate

[ShredDude]

Hi! I'm new to this forum, and new to Access.

I'm attemtpting to convert an application I developed in Excel in order to capture the power Access provides. Given the nature of my data, a relational DB is where I need to be as opposed to the way I've prototyped things with Excel.

I'm hitting a wall though on manipulating Access Forms. In Excel, I make extensive use of UserForms that I manipulate with VBA. Often, I take advantage of the _Change Event, for a Field(eg. TextBox) on the Userform to trigger changes in related fields.

Although the Access Help file refers to a Change Event, it doesn't appear it really exists in the context I'm attempting to use it, thus I've resorted to the AfterUpdate event. However, I'm finding that this event does not fire off when the Value of a Field changes via VBA code; it only seems to fire off through user interaction via the GUI.

Here's what I'm trying to accomplish...as a newbie to Access I'm most interested in any recommendations as to how to achieve my task. Perhaps I've been going about things in a fundamentally wrong manner??

I have three primary tables to define my relationships. Clients, Properties, and Contracts. Each client can own multiple Properties, each property can have multiple associated Contracts.

I've created a Form with a Tab control. In the Form's Header I display the Client Name and Phone number for the record being viewed. The first page of the Tab Conrol contains other fields from the Clients table for the current Record. That all works fine.

It's the second Page of the Tab control where I'm having issues. I've created two Listboxes for this page. Listbox1 to contain all the properties associated with the current client, and Listbox2 to contain all the Contracts associated with the selection in Listbox1. (I don't need to see all of a Client's contracts here, just the one's associated with a given property.)

ListBox1 gets populated with all the Properties associated with the current Client during the Form_Current event by doing a .Requery for the Listbox. After hacking my way through creating Queries, I've got that working fine. From there, I attempt to do a .requery on a Subform that contains various data associated with the Property selected in the Listbox1. This is where I'm having timing issues. It's not until I click Litbox1 with the mouse that I am able to successfully populate the subform. I do this through the Listbox1_AfterUpdate event, it works fine. however, I'm used to having a _Change event to handle this scenario...the value of the Listbox1 changes (via code), and then other things happen (eg. Requery the subform) I would like the Subform's data to be updated with the associated data to Listbox1's first record, automatically, without having to click the Listbox.

I've disocvered that I can't set the .listindex property of the Listbox via VBA, this was odd to me, coming from Excel. I discovered the .Selected property, and have used this to indirectly set the listindex of the Listbox by using .Selected(0) in the code, thinking this might trigger the AfterUpdate event and thus I could Requery the subform there. AterUpdate doesn't fire until I click the Listbox with my mouse however.

I have more related issues, like populating Listbox2 without having to first manually click a record in Litbox1, but instead have it done during the Form_Current event by using the first record in Listbox1, but I'll leave those for now.

In summary, I need to browse the clients, maybe with the navigation bar at the bottom for starters, and have the new client's first property data, and the data associated with the first contract for the first porperty, appear in the appropiate subforms without having to click the form anywhere. I know this can be done, and I know how to do it with Excel Userforms but I'm a little lost in Access.

I feel that I'm close, but maybe I'm fundamentally off here. Any insight would be much appreciated.

Thanks in Advance!

 

[Banana]

Just three things to note.

1) Normally, we would use BeforeUpdate event to validate data, and AfterUpdate to perform actions based on the values selected/entered. Textboxes (and Combobox?) does have OnChange event (?) which fires for *every* keystroke. It's appropriate only if you want to do some kind of filtering (say that combobox has too much stuff in its list to be meaningfully display- OnChange would be good event for checking the length of value entered so far and displaying only records matching the part of that value entered so far).

2) Anything you do in code, you must do in code also. So, if you changed listbox's selection via code, you also have to requery the cascading listbox as well. There's two ways that you can work around this. First, you can coerece a default selection for your master listbox by doing something like:

Me.MyMasterListBoxName = Me.MyMasterListBoxName(0)

This is off the top of my head and I think I'm missing something like Index or RowIndex. This also only work if this *isn't* a multiple selection listbox (which requires different techniques). Alternatively, you can just create a custom procedure and have both your AfterUpdate event (for user interaction) and your code changing the value refer the same code to update the listbox, so you only have one block of code that can be referenced from different events/code blocks.

3) Finally, if you search forums and internet on "Cascading Combobox" you should find several hints and demos how to accomplish this. Combobox can be converted to Listbox so for most parts, this will work with listboxes as well.

HTH.

 

[stopher]

Welcome to the forum.

What is it that causes your Listbox1 to be updated? Is it requeried when you navigate to a record? If so, then you could do the Listbox1.selection(0) in the forms On Current event I think.

Chris

 

[ShredDude]

Here's how I use the Form_Current event now...

Private Sub Form_Current()
'MsgBox "Form_Current() -fired"

'Refresh the Property Listbox
With Me.lboxProperty2
    .Requery
    .Height = .ListCount * 230 + 230
    
    If .ListCount > 2 Then
        lblChooseProperty.Caption = "Choose Property:"
    Else
        lblChooseProperty.Caption = "Only One Property:"
    End If
    
    .Selected(1) = True ' Selects first row, establishing listindex as 0
   
        ' MsgBox .ListIndex & " Is the Listindex"
   
     .Value = .Column(1, .ListIndex)
End With

'Refresh the Subject Property Subform
With Me.tblSubjectProperty_subform
    .Top = Me.lboxProperty2.Top + Me.lboxProperty2.Height + 0.5
    .Left = Me.lboxProperty2.Left
    .Requery
End With

'Refresh the Contract List Box
With Me.lboxcontractData
        .Requery
        .Selected(0) = True
End With

'Refresh the Contract Sub Form

    'Testing the timing of this...
    Me.ContractDataSubForm.Requery
    


End Sub

So, when I navigate to a new client via the Navigation Bar, the Property Listbox updates accordingly. However, I'm not getting the Property SubForm or the second Listbox (lboxContractData) to update until I actually click a value in the Property List box with the mouse. This kicks off the Property Listbox's Afterupdate event within which are requery commands that refresh the Contract Listbox, and the subform. That works fine, and I need that feature for when a user elects to change the property they are viewing within that client.

However, I'd like for the Contract Data Listbox, and it's associated subform to automatically refresh upon a new client without me having to click in the Property Listbox.

 

[rapsr59]

Hi ShredDude!

As you said you are having no problem when you re-query the list boxes.
And you are having no problem with populating the first list box that shows the properties data.

I believe you are trying to automatically select the first property that shows in the properties list box and automatically set and display the resulting records in the contract list box for the selected property.

In the Forms Current event, and after you re-query the contracts list box you can add the code to select the first row of the contracts list box. The code would be…

[FONT=Times New Roman][SIZE=3]lstCtl.Selected(0) = True    'Use the name of your list box as lstCtl[/SIZE][/FONT]

This should select the first row of the properties list box.

Then re-query the list box for the contracts information and the list box will populate.

If you want to select the first contract, use the same code on the contracts list box.

Hope this helps.

If you need to de-select the selected items in the two list boxes when selecting a new client use...

Dim ctlList As Control
    Dim lnglistcount As Integer
 
    Set ctlList = Me.lstSelectOrder 'Change to name of your control(s)
 
    'If items are selected, de-select them
    If ctlList.ItemsSelected.Count > 0 Then
        For lnglistcount = 0 To ctlList.ListCount - 1
            ctlList.Selected(lnglistcount) = False
        Next lnglistcount
        ctlList.Requery
    End If

Richard

 

[ShredDude]

SOLVED Change Event vs AfterUpdate

The key turned out to be setting the Value property in the code. Just setting the Selected Property, did not change the Value Property. Since the query for the subsequent Listbox was based on the VALUE of the first Listbox nothing was happening. When clicking the row in the listbox, the VALUE property is changed, thus explaining why the AfterUpdate event was working correctly all along.

Here's a sample of the form_Current code now working...

Private Sub Form_Current()
'MsgBox "Form_Current() -fired"

'Refresh the Property Listbox
With Me.lboxProperty2
    .Requery
    .Height = .ListCount * 230 + 230
    
    If .ListCount > 2 Then
        lblChooseSubject.Caption = "Choose Subject Property:"
    Else
        lblChooseSubject.Caption = "Only One Subject Property:"
    End If
    .ColumnHeads = True
    .Selected(1) = True ' Selects first row, establishing listindex as 0
   
         'MsgBox .ListIndex & " Is the Listindex" & vbCrLf & _
                .Value & " is the Value before the .value Code Line."
    
    
    '************ HAD TO SET VALUE FOR OTHER LISTBOX TO BE ABLE TO REQUERY ******
    .Value = .Column(0, .ListIndex + 1)
    
          'MsgBox .ListIndex & " Is the Listindex" & vbCrLf & _
                .Value & " is the Value after the .value Code Line."


End With

'Refresh the Property Subform
With Me.tblSubjectProperty_subform
    .Top = Me.lboxProperty2.Top + Me.lboxProperty2.Height + 0.5
    .Left = Me.lboxProperty2.Left
    .Requery
End With

'Refresh the Contract List Box
With Me.lboxContractData
        .Requery
        .Selected(1) = True
        .Height = .ListCount * 230 + 230
End With

'Refresh the Contract Details Sub Form

    Me.ContractDataSubForm.Requery


End Sub

Good Lesson learned for me. This behavior is different from similar activities in Excel VBA. I guess I can see some merit to it however. There could be circumstances where you'd want to "Select" an item, affecting the appearance on the From with dashed lines around that row for example, without actually going ahead and changing the Value for the control.

Thanks for everyone's input. I'm sure I'll be back soon!

Shred

 

[ShredDude]

Re: SOLVED Change Event vs AfterUpdate

I had to add the same line of Code to set the .Value for the lboxContractData in order for the contract SubForm to refresh correctly. Became a problem when a property was selected with more than one contract associated to it.

 

[jazaddict]

'************ HAD TO SET VALUE FOR OTHER LISTBOX TO BE ABLE TO REQUERY ******
.Value = .Column(0, .ListIndex + 1)
OMG!!!!!!!
Thank you Thank you Thank you!!!!!!!!!!!!!!!!!
This has been a MAJOR PAIN IN MY 4$$ for MONTHS!!!!!!!!
Unbelieveable.

I'm SO appreciative of your publicized effort that I registered here JUST TO SAY THANK YOU.

Carry on..... :grin: