Linked Table Question

[Stang02GT]

Hello,

In the final stages of this Access project(That i've been badgering people with questions about) the last modification that is to be made is another one that i have no idea if its possible to do.

My Access app is linked with Excel and the tabs in Excel are tables in Access. Part of this app i have created ( with all of your help!) updates the status of current projects that employees are working on. Once the status is changed in Access it is updated in Excel. I have coded Excel to move anything with a status of "Completed" to a COMPLETED tab.

Now here is where im stuck(again lol). The users would like to see a date attached to a record that has been changed to "Completed". They would like this date to be attached as soon as the status is changed and they would like to see it changed automatically.

I know the easy solution would just be to add a "Date" field and have the users add the date to the record when they change it, but they want it to be automated.

Is this something i can do in Access or should i do this in Excel?



Thanks to everyone who has helped me i greatly appreciate it!!!

[ADezii]

Hello,

In the final stages of this Access project(That i've been badgering people with questions about) the last modification that is to be made is another one that i have no idea if its possible to do.

My Access app is linked with Excel and the tabs in Excel are tables in Access. Part of this app i have created ( with all of your help!) updates the status of current projects that employees are working on. Once the status is changed in Access it is updated in Excel. I have coded Excel to move anything with a status of "Completed" to a COMPLETED tab.

Now here is where im stuck(again lol). The users would like to see a date attached to a record that has been changed to "Completed". They would like this date to be attached as soon as the status is changed and they would like to see it changed automatically.

I know the easy solution would just be to add a "Date" field and have the users add the date to the record when they change it, but they want it to be automated.

Is this something i can do in Access or should i do this in Excel?



Thanks to everyone who has helped me i greatly appreciate it!!!

You did not state how the [Status] Field is updated on the Form, so I'll assume that it is via a Combo Box named cboStatus.

1. Create a Date/Time Field in the underlying Table called Date_Completed.
2. Create a Text Box on your Form bound to the [Date_Completed] Field and name it txtDateCompleted.
3. Set the Visible Property of this Text Box to No.
4. Place this code in the AfterUpdate() Event of cboStatus.
   

   Private Sub cboStatus_AfterUpdate()
   If Me![cboStatus] = "Completed" Then
     Me![txtDateCompleted] = Format(Now(), "mm/dd/yyyy")
   Else
     Me![txtDateCompleted] = Null
   End If
   End Sub