VBA date question

[cbnewman]

Why isn't this working?

I have a form which has a text box for an attribute of type DATE. This
attribute, foo, is normally null when the form is loaded. I tried
setting the default value for the text box to "Date()" but it doesn't
show up (i'm thinking it's because I'm actually running a join query
to generate the record for the form so the form never gets the
opportunity to display the default value for foo since it thinks it's
supposed to be null, the result from the query)

So I added some code like this:

Private Sub Form_Open(Cancel As Integer)
If (IsNull(Me.foo)) Then
Me.foo = Date()
End If

End Sub

This generates a run-time error complaining that I am not allowed to
assign a value to Me.foo.

What am I doing wrong?

Thanks,
--b

 

[cbnewman]

Moving it to Form_Load() worked.. It must be the case that between
Form_Open() and Form_Load() the controls are populated with values
from the database.

Did I get it right about why the default value trick didn't work?

--b

 

[Ofer Cohen]

1. A default value is assigned to new records only, not to existing records
when the value in the field is Null

2. The reason that you can't assign a value to this field is probably
because of the query.
Run the query seperatly without the form and try to edit some of the fields,
it's usually happen when you join few tables together, and most chances that
they are not linked by the key fields.

 

[Ken Snell \(MVP\)]

A form's dataset often is not loaded yet when the form's Open event occurs.
If you wish to manipulate / interact with data or controls on the form, it's
best to use the Load event, as you've discovered.

See Ofer's reply regarding the default value.

 

[Vladimír Cvajniga]

I'd recommend you to set DefaultValue on a database level. This should work
independently, ie. if you add a new record from within a VBA code you should
find all default values in a new record.

Vlado

 

[cbnewman]

That's an interesting concept, but I don't think it will work in my
case.

The date that the user invokes a given form is the date I want
populating the date field. When you admit a patient to the hospital,
the admission date should be today. when you discharge them from the
hospital, it should be that day. When you create the record, there is
no way of knowing what the day of discharge will be.

thanks for your input, though.
--b