Programmatically change a controls format property

Posted on 2011-10-10
Last Modified: 2013-11-27
I have a search form that has 2 unbound controls a combo box (cboSearchField) and a text box (txtSearchString) the combo box is used to select the search criteria and the Text box is the search string, one of the criteria is a date (CountDate). What I want to do is programmatically change the controls format property to “Short Date” when the combo box contains CountDate, so that the calendar displays, and reset it back for non-date searches. BTW this is Access 2007.
Question by:skull52
    LVL 44

    Expert Comment

    What other search criteria do you have - numbers, boolean, strings?
    LVL 12

    Accepted Solution

    In the AfterUpdate event procedure for the cboSearchField control, put

    if cboSearchField = "CountDate" then
        txtSearchString.format = "Short Date"
        txtSearchString.format = ""
    end if

    Open in new window

    You may also need to put this code in your form OnCurrent event procedure. You can create a subroutine that contains the above code, and execute it from both the control AfterUpdate and form OnCurrent event procedures.

    I don't think the calendar will appear unless the date field is a bound control.

    Author Comment

    Text, Yes/No

    Author Comment

    The calender does appear, with unbound controls, I will test your code.

    Author Comment

    I tried putting the code where you suggested in the AfterUpdate and OnCurrent events but it didn't work, so I added it to the GotFocus event of txtSearchString and added .Value to cboSearchField  of your code and that seemed to work. Thanks for the help.
    Private Sub txtSearchString_GotFocus()
    If cboSearchField.Value = "CountDate" Then
        txtSearchString.Format = "Short Date"
        txtSearchString.Format = ""
    End If
    End Sub

    Open in new window


    Author Closing Comment

    See my last post for further instructions

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now