One field dependant upon another?

[Jay]

I have a table which includes two fields 'Status' and 'Date Resolved'.
'Status' can be either 'Ongoing' or 'Resolved', and is controlled by a
combo box on the form.

I want the 'Date Resolved' field to be automatically populated with the
current date when the user changes the 'Status' to 'Resolved', to save
the user having to type the date in.

Can anyone advise how this can be done, bearing in mind I'm a bit of a
novice.

Many thanks,

Jason

 

[Rick Brandt]

I have a table which includes two fields 'Status' and 'Date Resolved'.
'Status' can be either 'Ongoing' or 'Resolved', and is controlled by a
combo box on the form.

I want the 'Date Resolved' field to be automatically populated with
the current date when the user changes the 'Status' to 'Resolved',
to save the user having to type the date in.

Can anyone advise how this can be done, bearing in mind I'm a bit of a
novice.

At the table level you shoudl really just eliminate the field [Status] as
it's redundant. If you have a no value for [Date Resolved] then status is
Ongoing and if you have a vlaue for [Date Resolved] then status is Resolved.

I would eliminate that field from the table and just add a calculated field
to your queries, forms, and reports, for display purposes only that uses the
expression...

=IIf([Date Resolved] Is Null, "Ongoing", "Resolved")

 

[Guest]

Rick is correct - in a relational database, you should NEVER have fields in a
record that depend on each other, because that can lead to contradictions. If
you have both Status and DateResolved fields, what does it mean if
Status=Ongoing and DateResolved is nonNull? Or Status=Resolved and no
DateResolved?

 

[dbahooker]

if you were using an Access Data Project; then you could set this up as
a trigger and it would be easy to do without programming.

TSQL is much more powerful than silly MDB files

-Aaron
ADP Nationalist

 

[Jay]

I see what you're saying. OK, so is there any way I can have a user
enter a date resolved without having to type the date into the field?
Some form of control/button? The date resolved will always be the
current date at the time

Cheers

Jay

 

[Rick Brandt]

I see what you're saying. OK, so is there any way I can have a user
enter a date resolved without having to type the date into the field?
Some form of control/button? The date resolved will always be the
current date at the time

You could have them double click on it or provide a button and use code...

Me![Date Resolved] = Date()

 

[Jay]

I see what you're saying. OK, so is there any way I can have a user
enter a date resolved without having to type the date into the field?
Some form of control/button? The date resolved will always be the
current date at the time

You could have them double click on it or provide a button and use code...

Me![Date Resolved] = Date()

I'm useless at code Rick. Would I just type the above into the OnClick
property of the button?

I really appreciate your help with this.

Regards

Jay

 

[Jay]

I see what you're saying. OK, so is there any way I can have a user
enter a date resolved without having to type the date into the field?
Some form of control/button? The date resolved will always be the
current date at the time

You could have them double click on it or provide a button and use
code...

Me![Date Resolved] = Date()

I'm useless at code Rick. Would I just type the above into the OnClick
property of the button?

I really appreciate your help with this.

Regards

Hi Rick,

I managed to get the Me! code to work. However, it is adding a time to the
date, even though the 'Date Resolved' field is formatted as Medium Date in
its properties?

Could you advise how I can get the code to set the date (as it is doing) but
without the time.

Many, many thanks,

Jason

P.S I've deleted the Status field and used the IIf statement in a text box
on my form to display the status - thanks for the advice.

 

[Rick Brandt]

I managed to get the Me! code to work. However, it is adding a time
to the date, even though the 'Date Resolved' field is formatted as
Medium Date in its properties?

Could you advise how I can get the code to set the date (as it is
doing) but without the time.

Formatting does not matter. That only affects how dates are *displayed* not
what is *stored*. However; if you use Date() then you should get a zero
time (midnight). You would only get the current time inserted if you used
Now(). Which did you use?

 

[Rick Brandt]

I used Date() but when I went back to look at the VBA the () had gone?

Normal. You need the () in queries and ControlSource expressions, but not
in VBA code.

I managed to get rid of the time by using the following in the Me!
code:
Format(Date,"dd/mm/yy")

I didn't expect it to work but it did? Fluke!

As long as you were using Date you should NOT have been getting any time.