Make a Date Field Autopopulate

[Becky N]

Hello,

I am working on a Form in Access 2003. I would like when I make one field
equal to a particular value it autopopulates a second field with the current
date.

Basically, I have two fields for this issue. One is a combo box that will
either populate with "active" or "inactive". When I select "inactive" I want
another field called Date Closed to populate with the current date. I do not
want that date to change, so if the Date Closed field is blank and inactive
is chosen I want the current date to populate in the Date Closed field.

My form is based on a query that then saves to a table. I tried to do this
using formulas but I kept coming up with the current day's date each day (it
would change every day, wouldn't stay the original date the record was
closed).

Is there a way to accomplish my goal either using a formula or vba code?

Thanks for your help!

 

[Maarkr]

When I select "inactive" I want another field called Date Closed to populate
with the current date. I do not want that date to change, so if the Date
Closed field is blank and inactive is chosen I want the current date to
populate in the Date Closed field.
Open the properties for the combo.
In the After Update Event of the combo, click the arrow and select
[EventProcedure], and in the sub insert something like:

Me.DateClosed = Date()

where DateClosed is the field for your closing date. It should only change
when yo select an item in the combo. I didn't understand part of what you
were saying, but if you only want it to insert a date only if the field is
null, then use:

If IsNull(Me.DateClosed) Then
Me.DateClosed = Date()
End If

remove any previous references you had to make the field insert today's
date, including NOT having a default value of today in the table or form

 

[Al Campagna]

Becky,
Use the AfterUpdate eveny of your combo (ex. name cboStatus)

Private Sub cboStatus_AfterUpdate()
If cboStatus = "Inactive" Then
DateClosed = Date()
Else
DateClosed = Null
End If
End Sub

Actually, only a DateClosed value would be needed to designate that a record
is "Inactive." The combo Inactive/Active selection is a bit redundant
No big deal... for sure... just a suggestion.
I'd skip the combo, and just set up the DateClosed Dbl-Click event to update
DateClosed with the current date.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Becky N]

Thank you for your reply. I used the second code

If IsNull(Me.DateClosed) Then
Me.DateClosed = Date()
End If

This populated the field, but it does not save it. In other words, I select
inactive, today's date populates in the DateClosed field, but if I close the
form and go back in there is no date populated. Also, if I select inactive
the date populates in the dateclosed field, but then if I scroll through
every other date in the dateclosed field is now today's date.

Is there any way to prevent this?

When I select "inactive" I want another field called Date Closed to populate

with the current date. I do not want that date to change, so if the Date
Closed field is blank and inactive is chosen I want the current date to
populate in the Date Closed field.
Open the properties for the combo.
In the After Update Event of the combo, click the arrow and select
[EventProcedure], and in the sub insert something like:

Me.DateClosed = Date()

where DateClosed is the field for your closing date. It should only change
when yo select an item in the combo. I didn't understand part of what you
were saying, but if you only want it to insert a date only if the field is
null, then use:

If IsNull(Me.DateClosed) Then
Me.DateClosed = Date()
End If

remove any previous references you had to make the field insert today's
date, including NOT having a default value of today in the table or form

 

[Maarkr]

Look at Al's suggestion...I like that best...don't use the combo. I'm
confused on why the date is not saved... you do have DateClosed as a field in
the table that the form is linked, don't you? Is there other code in like
the Form_Open event?

Thank you for your reply. I used the second code

If IsNull(Me.DateClosed) Then
Me.DateClosed = Date()
End If

This populated the field, but it does not save it. In other words, I select
inactive, today's date populates in the DateClosed field, but if I close the
form and go back in there is no date populated. Also, if I select inactive
the date populates in the dateclosed field, but then if I scroll through
every other date in the dateclosed field is now today's date.

Is there any way to prevent this?

When I select "inactive" I want another field called Date Closed to populate

with the current date. I do not want that date to change, so if the Date
Closed field is blank and inactive is chosen I want the current date to
populate in the Date Closed field.
Open the properties for the combo.
In the After Update Event of the combo, click the arrow and select
[EventProcedure], and in the sub insert something like:

Me.DateClosed = Date()

where DateClosed is the field for your closing date. It should only change
when yo select an item in the combo. I didn't understand part of what you
were saying, but if you only want it to insert a date only if the field is
null, then use:

If IsNull(Me.DateClosed) Then
Me.DateClosed = Date()
End If

remove any previous references you had to make the field insert today's
date, including NOT having a default value of today in the table or form

 

[Ken Sheridan]

DateClosed is apparently an unbound control on your form. With an
unbound control the value will be the same as you scroll through the
form because its really just different instances of the same control.
You need to bind it to the column (field) in the underlying table by
setting its ControlSource property to the column name.

Ken Sheridan
Stafford, England

Thank you for your reply. I used the second code

If IsNull(Me.DateClosed) Then
Me.DateClosed = Date()
End If

This populated the field, but it does not save it. In other words, I select
inactive, today's date populates in the DateClosed field, but if I close the
form and go back in there is no date populated. Also, if I select inactive
the date populates in the dateclosed field, but then if I scroll through
every other date in the dateclosed field is now today's date.

Is there any way to prevent this?

When I select "inactive" I want another field called Date Closed to populate

with the current date. I do not want that date to change, so if the Date
Closed field is blank and inactive is chosen I want the current date to
populate in the Date Closed field.
Open the properties for the combo.
In the After Update Event of the combo, click the arrow and select
[EventProcedure], and in the sub insert something like:

Me.DateClosed = Date()

where DateClosed is the field for your closing date. It should only change
when yo select an item in the combo. I didn't understand part of what you
were saying, but if you only want it to insert a date only if the field is
null, then use:

If IsNull(Me.DateClosed) Then
Me.DateClosed = Date()
End If

remove any previous references you had to make the field insert today's
date, including NOT having a default value of today in the table or form

 

[Al Campagna]

Just do a Refresh after the Me.DateClosed = Date()
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thank you for your reply. I used the second code

If IsNull(Me.DateClosed) Then
Me.DateClosed = Date()
End If

This populated the field, but it does not save it. In other words, I
select
inactive, today's date populates in the DateClosed field, but if I close
the
form and go back in there is no date populated. Also, if I select inactive
the date populates in the dateclosed field, but then if I scroll through
every other date in the dateclosed field is now today's date.

Is there any way to prevent this?

When I select "inactive" I want another field called Date Closed to
populate

with the current date. I do not want that date to change, so if the Date
Closed field is blank and inactive is chosen I want the current date to
populate in the Date Closed field.
Open the properties for the combo.
In the After Update Event of the combo, click the arrow and select
[EventProcedure], and in the sub insert something like:

Me.DateClosed = Date()

where DateClosed is the field for your closing date. It should only
change
when yo select an item in the combo. I didn't understand part of what
you
were saying, but if you only want it to insert a date only if the field
is
null, then use:

If IsNull(Me.DateClosed) Then
Me.DateClosed = Date()
End If

remove any previous references you had to make the field insert today's
date, including NOT having a default value of today in the table or form