I want my form to automatically add today's date and time... 2

[JonWolgamuth]

to a table once the process is complete. I don't want people to have to input the date and time when a process is complete, but would rather use the system's calendar and clock.

The records can be viewed sometimes 3 or 4 times before they are completed. I want the user to declare that this item is complete, then hit the button, and have the record update. (A pop-up box indicating that the table has been updated would be cool too, but I think I can figure that one out! ;-D )

I'm assuming I need to use code or a macro utilizing the Now() function, but I'm sort of stuck at this point.

Thanks in advance!

 

[ghubbell]

Hi Jon,
In your forms After Update event:
(event procedure) then "..." to Visual Basic, add like this:

Me![NameOfAFieldOnYourFormThatStoresTheDateTime] = Now()

This should place the current time in to the selected field when ever the record is...updated!

I would add a field Yes/No that would acknowledge that the record is complete then, Lock the record so no one else can modify it:

In your forms On Current Event (as above to VB):

If Me![AFieldNameForCompleteYes/No] = True Then
Me.AllowEdits = False
Else
Me.AllowEdits = True
End if

Now as you move through the records, any record checked off as completed is un-editable. Gord
ghubbell@total.net

 

[JaneInMA]

This is very simple especially if you want it linked to a button. Add a button and for the onclick event add code like this

Me!UserField = CurrentUser()
' use this line if you want the Access loginID
Me!DateTimeStamp = Now

I have two fields one to log the user who does the completing one to log the time.
If you want them to be told it is complete just add a Msgbox

MsgBox ("This record is now completed.&quot

You may also want to consider locking the record at this time to prevent data corruption.
Jane

 

[JonWolgamuth]

Thanks for your response, however, I don't want the date entered upon update, I want the date updated on completion (which is decided by the user, and which likely should be triggered using a button or a check box.)

Any other suggestions?

 

[JonWolgamuth]

Whoops. I read the first post twice. Dang email! Anyway, Jane, your information IS more of what I was looking for.

Thank you very much!

Jon

 

[JaneInMA]

No problem, Gord and I seemed to post simultaneously with very similar ideas. I think his idea of adding a field to say if the report is closed is also good. Title it Status and have it autoentered as in progress when the record is created then label the button "close record". you can then also change the status to closed as you note the time.
Simply add me.fieldname = "Closed"

 

[JonWolgamuth]

Is there any other way, other than code (such as a macro) to get this done? I'm illiterate with VB (although I'm getting my feet wet).

 

[JaneInMA]

try using the code. Access makes it very simple for you.
For the button go to the properties and type in "[" Access will finish it as [event procedure] and then when you click the three dots take you to the proper place to start typing.
then type in (or copy and paste)

Me!DateTimeStamp = Now
MsgBox ("This record is now completed.&quot
Me.Status = "Closed"

Change "DateTimeStamp" for the name of the field you want your time stamp in and "status" for the name of the closure field if you want it and you will be all set. VBA can be scary in concept but it is really fun when you start to use it. Try it and if it fails come back and we will try and walk you through more. Avoid macros they will trip you up far more than code.

 

[ghubbell]

Right on Jane! I'll toss in one little tip (from Ugggh! experience):

Me.Status = "Closed" Change to Me![Status] = "Closed"

Status as best as I recall is one of those "funny words" like naming a field "Date" or "Name"...and I do recall some issue with it somewhere. ;-) Gord
ghubbell@total.net

 

[JonWolgamuth]

Okay, this is what I have so far, but I can't get the second popup box to only display if the the user chooses Yes. What gives?

Private Sub Command6_Click()
MsgBox ("Mark Record as Completed?&quot, (vbYesNoCancel), ("Work Completed?&quot
If vbYes Then Me!CompletedBy = Now()
MsgBox ("This record is now completed.&quot, (vbOKOnly), ("Work Completed&quot
If vbNo Or vbCancel Then End


End Sub

 

[ghubbell]

Try this Jon:

Private Sub Command6_Click()
If MsgBox("Are you sure you would like to mark this record as completed?", vbYesNo + vbQuestion, "Complete record confirmation...&quot = vbNo Then Exit Sub

Me.CompletedBy = Now()

MsgBox "This record is now completed.",vbInformation,"Work completed..."

End Sub
Gord
ghubbell@total.net

 

[JonWolgamuth]

Gord and Jane, you guys rock!! Gord, that code works perfectly.

Thank you both very much for helping me out with this. I think I see VB Access Programming in my future.

Later,

Jon W.