Unbound control does not refresh when the form's recordset is empty

[Yarik]

Hi,

This feels really weird... like a bug in Access or something.

I have a bound continuous form and a bunch of unbound controls in the
header section to let the user quickly filter the records shown on the
form.

This is the code simplified by stripping of all the details that I
believe are not important (no validation of user input, no error
handling, etc.):


' User is supposed to press the Refresh command button
' after specifying the desired filtering criterion
'
--------------------------------------------------------------------------------
Private Sub cmdRefresh_Click()

' Based on filtering criterion specified by unbound controls
' we construct an SQL query and ask the form to show its
results.
' For example, something along these lines:
...
' Get ID of a customer currently selected in unbound combobox:
sqlCriteria = sqlCriteria & "CustomerID = " &
Me.comboCustomers
' Get date specified in unbound text control:
sqlCriteria = sqlCriteria & "ReceivedOn > " & Me.textMinDate
....

Me.RecordSource = "select * from Orders where " & sqlCriteria

End Sub

' This is a button that user can press to quickly specify that
' only today's orders are to be shown.
' (NB: User still has to press Refresh button afterwards
' for the new filter to be applied.)
Private Sub cmdSetMinDateToNow()
Me.textMinDate = Now()
End Sub


That's it. It works like a charm most of the time. However, the
problem occurs whenever the SQL query does not return any records. The
simple code of the cmdSetMinDateToNow_Click event handler stops
working properly: the unbound text control textMinDate keeps showing
its old value after its value is changed by

Me.textMinDate = Now()

line. It really looks like some problem with refreshing of the text
control: in debugger, I can see that the value has been actually
changed, but the control does not show the change to the user.
Interestingly enough, the control does get refreshed if user does some
woodoo like

-- hovering a mouse over the stubborn unbound control (sometimes does
not help)...

-- clicking the stubborn control first (to set focus to it) and *then*
pressing the cmdSetMinDateToToday button again...

-- trying to enter some value into another unbound control...

-- etc.

Is this a known bug in MS Access?

The only workaround that we've found so far is using the unbound
control's Requery() method to "kick" it right after changing its
value. For example:

Private Sub cmdSetMinDateToNow()
Me.textMinDate = Now()
Call Me.textMinDate.Requery() ' Kick it...
End Sub

It works, but it's ugly. By any chance, does anybody know any
alternative solution?

FWIW: This happens with Access 2003, in ADP project (not MDB). The
backend is SQL Server 2000.

Thank you!

 

[Dale Fye]

Unbound controls never refresh unless you change their values manually or via
code. You can go from record to record, or have no records displayed and
these controls will never change their values unless you do it.

1. You don't need to change the forms recordsource, you can just set the
Filter property of the form. I assume you left out all of the " AND " stuff
as you built your sqlCriteria string on purpose.

2. How are you firing your sub cmdSetMinDateToNow()? Since this is not a
Click event subroutine.

Dale

 

[Yarik]

1. You don't need to change the forms recordsource, you can just set the
Filter property of the form.

Yes, I could. But I don't think it would make any difference, would
it?

I assume you left out all of the " AND " stuff
as you built your sqlCriteria string on purpose.

I just forgot to include "AND" operators into the sample code, sorry.
Of course, the actual query is constructed properly and runs without
problems.

2. How are you firing your sub cmdSetMinDateToNow()? Since this is not a
Click event subroutine.

Oops, another mistake. I meant to write

Private Sub cmdSetMinDateToNow_Click()
...

- i.e. it is an event handler.

So the question remains: when the recordset does not contain any
records, why is it not enough to just set the value of the unbound
control by

Me.textMinDate = Now()

Why does the subsequent call to Me.textMinDate.Requery help, and is
there any solution better than "kicking" the control by Requery?

 

[Dale Fye]

Yarik,

I've never had any problem assinging the value with me.txtMinDate = Now().

I don't think there is anychance that that control has the focus is there?
If it does, that might have somthing to do with it.

You might want to try me.txt_MinDate.Value = Now().

Dale

 

[Yarik]

I've never had any problem assinging the value with me.txtMinDate = Now().

I don't think there is anychance that that control has the focus is there?
If it does, that might have somthing to do with it.

No, the control does not have focus at the time of assignment.

You might want to try me.txt_MinDate.Value = Now().

Nope, does not make any difference.

As I mentioned before, the debugger says that the value does get
changed. It's just that control fails to reflect the change. It's
definitely some kind of repainting problem (otherwise why would this
control suddenly show the correct value when I hover mouse over it or,
sometimes, even over another control on this form?).

If this problem cannot be eliminated (e.g. if it's a bug in MS
Access), it would be nice at least to know a better way to force the
control to repaint itself (a better one than calling its Requery
method)...

Thanks,
Yarik.