Access ComboBoxes; Access Form Activation

[MWE]

I am running Access2K and WinXP. My first problem is not a VBA issue, but my workaround created some VBA and led to a VBA question ...

1) I have a 2 col by 8 row combo box on form "A", The combo box allows me to select from a group of dates, each date (col1) has a corresponding label (col2) . One of dates needs to be "today", the rest are static. I initially tried to use the Date() function in the RowSource property for the combo box. Since Date() works for other properties, it seemed like a good choice. But Date() did not work. Any suggestions how I might accomplish this?

2) As a workaround to the above, I wrote a VBA proc to change the first element of the RowSource Property to today's date. This approach works fine as long as the VBA proc executes when the form is displayed the first time; reexecuting the proc each time the form is displayed is OK. This brings me to my next question: Is there something in Access equivalent to the UserForm_Activate procedure in MS Forms?

3) The default value for the combo box is today's date. In the Default Value property field I entered =Date() This works for text boxes and did not generate an error. However, when I create a new record, the value in the combo box is #Name? indicating that Access either does not understand =Date() as a default value or does not know where to put it. Text values in quotes for the default value, e.g., "ABC", generate a different error, i.e., #Error
Text values in quotes for other combo boxes (all 1 col) in this form work fine. Clearly I am missing something ...

Thanks for any help.

[XLGibbs]

1. How is the combo box being populated? You might just add today's date using the add item method.

cbobox1.Additem date

2.hmm...not exactly. You might have to tie the procedure to another event that would fire..maybe on click or on exit of another part of the form?

3. This error is common with unbound objects that may depend on another form. And is also possible then when the source data is defined as a list or an array of text items, that it will assume the =date() is also meant as text.

I would build the combo box details using .additem and that should solve all the woes.

Incidentally...cross post with responses

http://forums.techguy.org/business-applications/662348-access2k-questions.html

[MWE]

thanks for the reply

I still have not found a solution to #1. The combo box was initially populated using the Wizard and then some manual tweaking of the RowSource property. I have about given up on trying to solve #1 elegantly because I was able to get #2 to work correctly.

I was trying to figure out the equivalent of UserForm_Activate from the VBA perspective, i.e., using the VBA editor. Anne Troy asked about the OnFocus event and that got me thinking about how Access "thinks" about things and that the Properties table has a zillion "on" events and one would surely be what I wanted. As soon as I linked an appropriate on event (I used On Open) to an event procedure and put my combo box updating code into that proc, that portion of the problem is now solved.

A solution to #3 still eludes me. Any text manually entered into the Default Value property generates an error with a new record. I also tried adding a line of code to the proc that executes on open:
NewDate = Format(Date, "dd-mmm-yyyy")
.
.
.
Form_PeopleInfo.comboDateModified.DefaultValue = NewDate
That generates an error with a new record as well. I created a new form, a single multi-col combo box and manually entered a default value. Seems to work OK, so there is something fishy about the combo box that started this thread.

EDIT: Jimr381 on TechSupportGuys came up with a soluton for the Default Value problem; see this post (http://forums.techguy.org/business-applications/662348-access2k-questions.html#post5427122)

[n8Mills]

Not sure if you're still watching this thread, but the following syntax has resolved some issues that I had.


Forms![PeopleInfo].comboDateModified.DefaultValue = NewDate