CODEWITHSUNDEEP
excelms-accessvbams-access-2010
LuckyLuke82
LuckyLuke82

Reputation: 606

Access VBA - Trigger combobox change event

I have been playing around with form designs and now I have constructed a form, which is almost complete, just one problem left. Let me explain first:

Form is bound to a "Join Table", which has only 2 fields - ID from "Table1" and ID from "Table2". Based on those two fields I have added fields from "Table1" & "Table2", on same form. Then I have added 2 option buttons, 1 Combobox and 2 Subforms.

This allows me to watch records from two different tables that are joined, from each point of view - "Table1" or "Table2" view. I am selecting this views with Option buttons, which changes Combobox rowsource, so you can navigate to records from Combobox.

Here is code :

Private Sub OptButton0_Click()
        If Me.OptButtonO.Value = True Then

        Me.OptButton1.Value = False

       Me.Cmbox.RowSource = "SELECT [Table1].[Field1], [Table1].[Field2], [Table1].[Field3] FROM Table1 ORDER BY [Field1];"

      Me.Cmbox.SetFocus
      Me.Cmbox = Me.Cmbox.ItemData(0)

        End If

 End Sub

 Private Sub Cmbox_AfterUpdate()   
   If Me.OptButton0.Value = True Then

    If IsNull(Me!Cmbox) Then Exit Sub

    With Me.RecordsetClone
      .FindFirst "[Field1] = " & Me!Cmbox
      If Not .NoMatch Then
         If Me.Dirty Then Me.Dirty = False
         Me.MySubform.Width = 8280
         Me.MySubform.SourceObject = "MySubform"
         Me.Bookmark = .Bookmark
      Else
         Me.MySubform.Width = 8000
         Me.MySubform.SourceObject = ""
      End If

    End With

Me.Cmbox.SetFocus
DoCmd.Requery

End If

End Sub

This posted code is only for one Option button, second one is same, just opposite. Now what is problem ?

Problem is that when I navigate through record via Combobox, click second Option button for another view AND THEN RETURN to same view, my subform results stays same as they were when I clicked another Option button, although Combobox listIndex is 0. If I select combobox Listindex from Combobox, code works again.

SO BASICALLY - I NEED CODE THAT WILL TRIGGER COMBOBOX CHANGE WHEN OPTION BUTTONS ARE CLICKED. It works when you're clicking in Combobox, but not when clicking in Option button.

I know It's complicated to understand, please take a look at code, and ask anything. Any help appreciated.

Upvotes: 3

Views: 6810

Answers (3)

Thomas Rowan
Thomas Rowan

Reputation: 21

Another option: I had a similar issue: with an unbound combobox. In the '*_Change' event in a combobox, if I pick a value from the dropdown, the value is there; but if I delete the existing value, the prior value still shows up.

...

If Me.series_filter > "" Then
    lstrMetric = lstrMetric & "and X.series_name = '" & Me.series_filter & "' "
End If

...

Me.Filter = Mid(lstrMetric, 5)

...

I have a dropdown for a filter on the form: picking a value is meant to set (or clear) the filter. It worked when I picked a value, but would not clear if I delete it.

I added a line to the start of the code to commit updates:

Me.dirty = false

The code now recognizes the null value when the combobox is cleared. This works in my case - of course it would be a problem if you didn't want any updated fields written to the database.

Upvotes: 2

CWilson
CWilson

Reputation: 435

In Access, controls can be funny. I mean, when you change the option, you see it change, it changes on the screen... but the control doesn't always change its .value right away... meaning it doesn't Update. For a simpler illustration of this principle, create a textbox, type some stuff into it, and look at the textbox.value in the immediate window. Then, with the cursor still in the textbox, backspace some of it. Check the value again. Do that a few more times. Start including textbox.text in your tests. There are multiple ways around this, but it is most important to understand when controls actually update, so you can understand better when and which work arounds to use.

Now, in this situation, you typed 'click' every time when you referenced selecting an option. I think I will hold you to that. Personally, I use Tab and the arrow keys sometimes, which would be a slightly more complicated answer, but one you will be better equipped to solve yourself, after understanding the textbox example above. But, if you change AfterUpdate above to Click, you should be good.

That said, there are events besides mouse clicks that can activate the Click event. Usually not a problem, but since you are changing the look of your form, specifically the width, you may want to be aware that the subform width may flash back and forth sometimes while using your tool. Not often, probably won't be too much of an annoyance, but I might rethink why I needed to change width at all, or if there might be better triggers for it.

Upvotes: 0

John Bingham
John Bingham

Reputation: 2006

call the the combobox afterupdate event in the option button click event:

private sub optbutton0_click()
    ...
    cmbox_afterupdate()
end sub

PS: Rather than having events for option buttons directly, you should put them in a frame, (even if you then have to make the frame transparent to stop it from appearing) and use the afterupdate or click events of the frame, whereby you can get the selected option button by option value:

private sub frame0_click()
    select case frame0
        case 0 'option button 0 is selected
            ...
        case 1 
        ...
    end select
end sub

Upvotes: 1

Related Questions