Error with DoCmd.RUNSQL Update code

[desjardins]

I am trying to update a date field (enddate) in a table (tbl_employees) with the value of a text box (TerminationDate) in a form. The format of the text box is set to short date.

Private Sub TerminationDate_AfterUpdate()
Dim termdate As Date
termdate = Me.TerminationDate.Value
If Not IsNull(termdate) Then
DoCmd.RunSQL "UPDATE tbl_employees SET [enddate]=[termdate]" _
& " WHERE [tbl_employees].[PersonID]= me.personID;"
End If
End Sub

I get an "enter parameter value" msgbox for termdate. I have also tried "dim termdate as string".

If the table already has a date in it, I don't want to overwrite with a null value (hence the IF statement). The text box is already bound to another table (tbl_terminations), is this the problem? How do I get the one text box to update both tables?

 

[boblarson]

You have things inside the quotes which should be outside of them. Change it to this:

DoCmd.RunSQL "UPDATE tbl_employees SET [enddate]=[COLOR=red][B]#" &[/B][/COLOR] termdate [B][COLOR=red]& "#[/COLOR][/B]" _
& " WHERE [tbl_employees].[PersonID]= [COLOR=red][B]" & [/B][/COLOR]me.personID;"

 

[desjardins]

Works great, thanks!

 

[kaveman]

Hello,

I'm having a similar issue. Are you able to show me where I have my quotes incorrectly placed?


DoCmd.RunSQL "UPDATE [Timers] Set [Worker_Time] =" & Me.TotalTime01 & "WHERE [Timers].[Worker_Code] =" & 1

Thank you very much in advance.

 

[desjardins]

What is the & 1 for? Are you trying to increment the time by one? or the worker code by one?

 

[kaveman]

This is worker number 1. I am trying to update the total time spent on task for worker number 1.

My table is set up like this:

Worker_Code - this is the workers employee ID number
Worker_Time - this is the total amount of time spent on task

Each worker will clock in and then a timer will start tallying the total amount of time they are on task. I am then hoping to use the SQL statement to pass this amount of time to my table field Worker_Time at set intervals so I can gauge output per hour.

Hope this helps...I've been stuck on this one for some time now so I truly do appreciate your willingness to help me out.

 

[desjardins]

I think you are missing some spaces. Also, I'm not sure if SET is case sensitive in SQL. Try this:

DoCmd.RunSQL "UPDATE [Timers] SET [Worker_Time] = " & Me.TotalTime01 & " WHERE [Timers].[Worker_Code] = " & 1

 

[kaveman]

I'm still missing something. The changes don't produce the error I was receiving but they also aren't updating the table. I have attached what I have so far. It is pretty basic...for the most part I have my form that runs the clock and calculates total time. I would like this total to then be passed on to the table. My end goal is that I will have multiple clocks running for each worker and then I can sum the total time for all workers to calculate productivity.

Thanks again for all you help...as you can see I am fairly new to VBA.

 

[desjardins]

I will try to take a look at this tomorrow. I don't know where you are but it's Friday night here

 

[kaveman]

Yeah, it was Friday night here too...you caught me working a little late. Thanks for the reminder to get out and have some fun.