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

Discussion in 'Microsoft Access Form Coding' started by Yarik, Sep 21, 2007.

  1. Yarik

    Yarik Guest

    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!
     
    Yarik, Sep 21, 2007
    #1
    1. Advertisements

  2. Yarik

    Guest Guest

    RE: Unbound control does not refresh when the form's recordset is empt

    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
    --
    Email address is not valid.
    Please reply to newsgroup only.


    "Yarik" wrote:

    > 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!
    >
    >
     
    Guest, Sep 21, 2007
    #2
    1. Advertisements

  3. Yarik

    Yarik Guest

    Re: Unbound control does not refresh when the form's recordset is empt

    On Sep 21, 9:36 am, Dale Fye <> wrote:

    > 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?
     
    Yarik, Sep 22, 2007
    #3
  4. Yarik

    Dale Fye Guest

    Re: Unbound control does not refresh when the form's recordset is empt

    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" <> wrote in message
    news:...
    > On Sep 21, 9:36 am, Dale Fye <> wrote:
    >
    >> 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, Sep 22, 2007
    #4
  5. Yarik

    Yarik Guest

    Re: Unbound control does not refresh when the form's recordset is empt

    On Sep 22, 10:03 am, "Dale Fye" <> wrote:

    > 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.
     
    Yarik, Sep 23, 2007
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. 1

    Default Value for unbound text box and unbound form:

    1, Sep 3, 2003, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    436
  2. Craig Buchanan
    Replies:
    0
    Views:
    498
    Craig Buchanan
    Aug 2, 2005
  3. Guest

    Unbound form/unbound control calculation

    Guest, Sep 30, 2005, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    321
    Guest
    Sep 30, 2005
  4. Guest

    Populate an Unbound Listbox based on an Unbound Combo box

    Guest, May 24, 2007, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    378
    Guest
    May 24, 2007
  5. NKK

    Unbound forms and dao recordset question

    NKK, Dec 22, 2008, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    365
    Albert D. Kallal
    Dec 23, 2008
Loading...

Share This Page