Edit existing record

dark11984

Registered User.
Local time
Today, 21:59
Joined
Mar 3, 2008
Messages
129
Hi,
I'm trying to edit an existing record in a table with a date/time stamp once i check my check box on my form. I've been reading tonnes of threads trying to figure out where i'm gonig wrong but i'm yet to have any luck :mad:. I think i'm prettyclose though. Can anyone help?

Code:
Dim TaskHistory As DAO.Recordset
Dim FrmID As String
Set TaskHistory = CurrentDb.OpenRecordset("TblTasks")
FrmID = Me.ID
TaskHistory.FindFirst ("ID = '" & FrmID & "'")
    
TaskHistory.Edit
TaskHistory("CompleteDate") = Date
TaskHistory("CompleteTime") = Now()
TaskHistory("CompleteUser") = GetUserName()
TaskHistory.Update

Thanks
 
I don't know what i was thinking... i didn't even need an update statement. You know sometimes when you think to hard about something you easily overlook the simple solutions.

I ended up just using an if statement.

Code:
Private Sub Completed_Click()
If Me.Completed = -1 Then
    Me.TxtCompleteDate.Value = Date
    Me.TxtCompleteTime.Value = Now()
    Me.TxtCompleteUser.Value = GetUserName()
Else
    Me.TxtCompleteDate.Value = ""
    Me.TxtCompleteTime.Value = ""
    Me.TxtCompleteUser.Value = ""
End If
Me.Form.Requery
End Sub
 
An even simpler version:
Code:
    dim intCompleted as integer

    intcompleted = nz(me.completed,0)

    Me.TxtCompleteDate.Value = iif(intcompleted = -1, Date, "")
    Me.TxtCompleteTime.Value = iif(intcompleted = -1, Now(), "")
    Me.TxtCompleteUser.Value = iif(intcompleted = -1, GetUserName(), "")
However, I would need to bring a couple of things to your attention:

1. Why are you storing both Date and Date/Time? Date() returns a date and Now() returns a date AND a time. You can save ONLY Now() if you want both parts.

2. Are the controls disabled/locked?

3. Are they bound controls?
 
1. i wanted to have the date and time in seperate fields but now have realised i can just use the saem field and format it differently depending on the report i use it in.

2. no not locked
3. yes they are bound.
 
is this working?

out of interest, you have

somefield = date
anotherfield = now()

-----------
i would have thought the compiler would add () after the word date.
now, it won't do that if you have a field/control called date - but if you have that then it may not work as you expect - ie it will use that value, rather than the system date
 
Thanks for your responses.

VBAInet - Yes I want the user to be able to change the date fields, the reminder date is the date they want to be reminded that the task is close to being due. The due date is the date that they want the task to be due.

Gemma - I don't have a field called date. when i type in date() it automatically changed to date. But it still has the same result as date().
 
VBAInet - Yes I want the user to be able to change the date fields, the reminder date is the date they want to be reminded that the task is close to being due. The due date is the date that they want the task to be due.
Alright. I was just making sure you were clear on your objectives.

Good luck with the rest of your db.
 

Users who are viewing this thread

Back
Top Bottom