Populating date based on criteria

[Iamrickdeans]

Hi Guys

I have a form in which I want to capture today's date when another field on that form is changed from active to Closed. In excel I would write an IF statement but can't get my head round what I would do in Access to achieve the same result.

basically it's if (field name 'status')=(criteria 'closed') then populate field (date closed with 'today's date')

Any help will be most appreciated!

Regards

R

 

[bob fitz]

Try this code in the After Update event of "Date Closed"

If Me.ActiveControl = "Closed" then
  Me.[Date Closed] = Date()
Else
  Me.[Date Closed] = Null
End If

 

[Iamrickdeans]

Appologies for only just returning to this issue, Thanks for the advice I have tried to apply this to my database sadly the field is not updating...

Is there anything that could be preventing this it is driving me crazy!

Regards

Richard

 

[missinglinq]

Bob just mistyped; his code should go in the AfterUpdate event of the 'status' Control, not the AfterUpdate event of the 'date closed' Control.

If Me.StatusControl = "Closed" then
  Me.[Date Closed] = Date()
Else
  Me.[Date Closed] = Null
End If

Linq ;0)>

 

[Iamrickdeans]

O.k Well I am now getting there, I have update a mixture of the code supplied by both of you and have the form now populating the time time the record was closed, the date the record was closed and the user id of the person who closed the record... I really appreciate the help thus far and if anyone wants a copy of the coding I have used I would be more than happy to supply this as it may help someone else?

However I am now hitting the next brickwall of confusion, this information is populating into my form however it is not writing that data back to the database table can you guys throw any light on this at all? It has been so many years since I have needed to use a database I have forgotten what little I knew.

Most appreciated again!

 

[missinglinq]

The Textboxes holding this data each have to be Bound to the corresponding Field in the underlying Table.

In Design View, first Right-click on one of these Textboxes, click on Properties, go to the Data Tab and see what's in the Control Source Property; it should be the name of the appropriate Field in the Table.

Linq ;0)>

 

[Iamrickdeans]

Thank you so much!

As soon as I read your reply I realised how foolish I had been, can't thank you enough!

 

[missinglinq]

Glad Bob and I could help!

Linq ;0)>