none
Unbound text box and DatePicker

    Question

  • I have a relatively simple Main Form with a Sub Form.  On the main form I have a total of 3 controls, a Combo Box sourced from a table, an unbound text box with the date picker and a command button to refresh the data in my sub form.  On my combo box's On Change event, I have a function to refresh the sub form, and this works well.  It checks if the text box is null, and if it isn't then it refreshes the sub form.

    I want to do this for the unbound text box/date picker control as well, but the event doesn't seem to fire consistently.  When it does fire, it seems a bit delayed, as the data in the sub form belongs to a previous selection.  It also doesn't seem to fire each time, so I'm not sure, what is going on.

    I found a great article about using the Date Picker and a text box bound to a date field in a table, and there are supposed to be 2 sets of properties, 1 for the text box and 1 for the date picker itself, but I don't have access to the Date Picker's properties, just my unbound text box's properties.  The example in this article was using the DateChanged event from the Date Picker control.

    Any guidance would be greatly appreciated.  I'm running Access 2010 x64 on Windows 7 x64.

    thanks.

    Saturday, June 25, 2011 11:34 PM

Answers

  • The example(s) you are referring to likely that of past (older) code that uses the calendar control.

    That control been dropped for 2010. (and worse, I note that you using the 64 bit version of access – it probably not the best choice at this point in time).

    Regardless, a good approach here is to use the after update event of both controls. And the following code could/would work for your buttion if you do not want the filter to occur when the value in the control is changed.

    And for your combo box, you want to use the after update event, and NOT the on change event. On change will fire every time, including as the user types and has NOT yet selected a value. So, correct event here is after update, not on change.

    Worse, on change would normally require you to use the controls "text" properoty which is ONLY legal when the control has the focus. The "value" is NOT undated until after update fires (only once). So use .value or default of the control.

    What I would do is setup a common routine to both controls. (or place the filter code I have behind the ONE filter button you have – your choice).

    The code I have is:

    Private Sub cboColor_AfterUpdate()

       Call MyFilter
      
    End Sub

    Private Sub txtDate_AfterUpdate()

       Call MyFilter
      
    End Sub


    Sub MyFilter()

       Dim strSql        As String
      
       'combo box
       If IsNull(Me.cboColor) = False Then
          strSql = strSql & "colorID = " & Me.cboColor
       End If
      
       ' now check for date
       If IsNull(Me.txtDate) = False Then
          If strSql <> "" Then strSql = strSql & " and "
          strSql = strSql & BuildCriteria("[SomeDateColumn]", dbDate, Me.txtDate)
       End If
      
       If strSql <> "" Then strSql = " where " & strSql
      
       strSql = "select * from tblCustomers " & strSql
      
       Me.custChild.Form.RecordSource = strSql
      
      
    End Sub

    So, above has the filter running when the date changes or the combo box is changed, but as noted, you could drop that, and just place the filter behind your filter button. Also,, note close how the code is setup that you can have 1, 2 or 10 controls for filter - it builds up the critieria for as many controls as you have. This means the sub-form query will not have any parmeters.

     

    • Marked as answer by Chris Fo Sunday, June 26, 2011 4:32 AM
    Sunday, June 26, 2011 4:27 AM

All replies

  • The example(s) you are referring to likely that of past (older) code that uses the calendar control.

    That control been dropped for 2010. (and worse, I note that you using the 64 bit version of access – it probably not the best choice at this point in time).

    Regardless, a good approach here is to use the after update event of both controls. And the following code could/would work for your buttion if you do not want the filter to occur when the value in the control is changed.

    And for your combo box, you want to use the after update event, and NOT the on change event. On change will fire every time, including as the user types and has NOT yet selected a value. So, correct event here is after update, not on change.

    Worse, on change would normally require you to use the controls "text" properoty which is ONLY legal when the control has the focus. The "value" is NOT undated until after update fires (only once). So use .value or default of the control.

    What I would do is setup a common routine to both controls. (or place the filter code I have behind the ONE filter button you have – your choice).

    The code I have is:

    Private Sub cboColor_AfterUpdate()

       Call MyFilter
      
    End Sub

    Private Sub txtDate_AfterUpdate()

       Call MyFilter
      
    End Sub


    Sub MyFilter()

       Dim strSql        As String
      
       'combo box
       If IsNull(Me.cboColor) = False Then
          strSql = strSql & "colorID = " & Me.cboColor
       End If
      
       ' now check for date
       If IsNull(Me.txtDate) = False Then
          If strSql <> "" Then strSql = strSql & " and "
          strSql = strSql & BuildCriteria("[SomeDateColumn]", dbDate, Me.txtDate)
       End If
      
       If strSql <> "" Then strSql = " where " & strSql
      
       strSql = "select * from tblCustomers " & strSql
      
       Me.custChild.Form.RecordSource = strSql
      
      
    End Sub

    So, above has the filter running when the date changes or the combo box is changed, but as noted, you could drop that, and just place the filter behind your filter button. Also,, note close how the code is setup that you can have 1, 2 or 10 controls for filter - it builds up the critieria for as many controls as you have. This means the sub-form query will not have any parmeters.

     

    • Marked as answer by Chris Fo Sunday, June 26, 2011 4:32 AM
    Sunday, June 26, 2011 4:27 AM
  • Thank you for the post and informative explanation.  I will work on incorporating it into my form.

    After posting, I actually decided to remove the unbound text box and use a series of combo boxes for filtering, and I think the code you provided will work well with this scenario.

    Sunday, June 26, 2011 4:32 AM