Access  Requery on change not alwats working

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

This doesn't always work. Is there anything I can add or change?

Private Sub txtSearchActionType_Change()
    Me.ActionType_Combo.Requery
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ASP.net/VBA/VSTO Developer
Commented:
I found that using the OnLostFocus event in tandem worked
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
When you use the Change event, you must use the .text property of the control in order to identify the change you just made.  Otherwise, a reference to the control that looks like [Forms]![YourFormName].txtSearchActionType will return the "value" of that control, which does not get changed until the control loses the focus.  You might be able to do this by changing the RowSource of the combo to:
SELECT ID, SomeColumn FROM yourTable
WHERE SomeColumn Like '*" & [Forms]![YourFormName].txtFilter.Text & "*'"

Open in new window

But I've never really tried that (using .text in a query).  You may actually be better off changing the Change event to:
Private Sub txtSearchActionType_Change()

    Dim strSQL as string
    strSQL = "SELECT ID, SomeColumn FROM yourTable " _
         & "WHERE SomeColumn Like '*" & me.txtFilter.Text & "*'"
    Me.ActionType_Combo.rowsource = strsql

End Sub 

Open in new window

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks Dale but I found that the .Text with Change event didn't always work. That was my first approach. The Lost Focus and change events both calling a requery seemed yo be best
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Try the function.

.Text does work within the change event, that is actually the only place it will work, is when the control has the focus.  As soon as the control loses the focus, the default property (Value) is changed to reflect what is in the .text property.  And once the control has lost the focus, attempting to use the .Text property of the control will fail.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial