Programmatically change a controls format property

Posted on 2011-10-10
Medium Priority
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
  • 4
LVL 44

Expert Comment

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

Accepted Solution

pdebaets earned 2000 total points
ID: 36945320
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

ID: 36945336
Text, Yes/No
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments


Author Comment

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

Author Comment

ID: 36948830
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

ID: 36962362
See my last post for further instructions

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

850 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