Too Many sql UPDATE ?

[DocWomet]

Trying to update a table with 16 potential textbox controls containing Short Times to show a patient's location. I'm getting no error messages and the UPDATE code seems to work, but only for certain controls, even though they are all formatted the exact same and the code's exactly the same for each UPDATE clause...

Thought maybe I'm trying to run too many UPDATE's at the same time, so I tried running just a few of them with no different result. Also tried running with Long Time formats but no change there either.

Any ideas why some fields would update but others wouldn't? (Or is there maybe a more elegant solution than what I've come up with to make this moot?)

Here's the code, I tagged which ones work and which don't... any ideas greatly appreciated...!


DoCmd.RunSQL " UPDATE MASTERShell " _
& " SET Location = ' TRW ' WHERE Time = #" & Me.txtTRW.Value & "# And PtNum = " & Me.Pt.Value & ";"
'works
DoCmd.RunSQL " UPDATE MASTERShell " _
& " SET Location = ' TR ' WHERE Time = #" & Me.txtTR.Value & "# And PtNum = " & Me.Pt.Value & ";"
'works
DoCmd.RunSQL " UPDATE MASTERShell " _
& " SET Location = ' AMB ' WHERE Time = #" & Me.txtAMB.Value & "# And PtNum = " & Me.Pt.Value & ";"
'works
DoCmd.RunSQL " UPDATE MASTERShell " _
& " SET Location = ' HOSP ' WHERE Time = #" & Me.txtHOSP.Value & "# And PtNum = " & Me.Pt.Value & ";"
'doesn't work
DoCmd.RunSQL " UPDATE MASTERShell " _
& " SET Location = ' TxW ' WHERE Time = #" & Me.txtTxW.Value & "# And PtNum = " & Me.Pt.Value & ";"
'doesn't work
DoCmd.RunSQL " UPDATE MASTERShell " _
& " SET Location = ' Tx ' WHERE Time = #" & Me.txtTx.Value & "# And PtNum = " & Me.Pt.Value & ";"
'doesn't work
DoCmd.RunSQL " UPDATE MASTERShell " _
& " SET Location = ' XRW ' WHERE Time = #" & Me.txtXRW.Value & "# And PtNum = " & Me.Pt.Value & ";"
'doesn't work
DoCmd.RunSQL " UPDATE MASTERShell " _
& " SET Location = ' XR ' WHERE Time = #" & Me.txtXR.Value & "# And PtNum = " & Me.Pt.Value & ";"
'doesn't work
DoCmd.RunSQL " UPDATE MASTERShell " _
& " SET Location = ' CTW ' WHERE Time = #" & Me.txtCTW.Value & "# And PtNum = " & Me.Pt.Value & ";"
'doesn't work
DoCmd.RunSQL " UPDATE MASTERShell " _
& " SET Location = ' CT ' WHERE Time = #" & Me.txtCT.Value & "# And PtNum = " & Me.Pt.Value & ";"
'doesn't work
DoCmd.RunSQL " UPDATE MASTERShell " _
& " SET Location = ' ORW ' WHERE Time = #" & Me.txtORW.Value & "# And PtNum = " & Me.Pt.Value & ";"
'works
DoCmd.RunSQL " UPDATE MASTERShell " _
& " SET Location = ' OR ' WHERE Time = #" & Me.txtOR.Value & "# And PtNum = " & Me.Pt.Value & ";"
'doesn't work
DoCmd.RunSQL " UPDATE MASTERShell " _
& " SET Location = ' HA ' WHERE Time = #" & Me.txtHA.Value & "# And PtNum = " & Me.Pt.Value & ";"
'works

DoCmd.RunSQL " UPDATE MASTERShell " _
& " SET Location = ' Civ ' WHERE Time = #" & Me.txtCiv.Value & "# And PtNum = " & Me.Pt.Value & ";"
'works
DoCmd.RunSQL " UPDATE MASTERShell " _
& " SET Location = ' Exp ' WHERE Time = #" & Me.txtExp.Value & "# And PtNum = " & Me.Pt.Value & ";"
'works

 

[Mihail]

I see only a bit of bad practice:
You have a field named "Time".
Time is a reserved word for Access and should be not used as name for anything.

And something that can cause problems:
If the value of Me.Pt can contain spaces (or other characters than letters, numbers and underscore) should be enclosed between simple or double quotes
... And PtNum = '" & Me.Pt.Value & "' ;"

But still this don't explain why some of the queries work and others not.

If you wish, upload the DB (Access 2003 or 2007) and I'll take a look.
ZIP the DB before to upload.

 

[DocWomet]

Mihail, thanks very much. I corrected the Time use and, coincidentally, accidentally deleted the entire form I was using. Thank God I pasted most of the code here... anyhow, I'm still having the same problem, in case that's of any consolation value.

I put default values in so if you could check out "InputTrackingData" and destination table MASTERShell that's where I'm having the problem...

Thanks again
Nate

 

[DocWomet]

P.S. Don't know why I didn't think of this earlier, but I changed what times I was using to test the UPDATEs. When I use 08:30, 08:31, etc. instead of 08:01, 08:02, etc. I get different results... but still only about 50% of the fields get updated.

 

[DocWomet]

UPDATE: I re-looked at all my formatting and tables and think I fixed it by tightening down my Short Time formats...!!! Though I'm still baffled as to how some of them were working in the first place... I think I've dodged this bullet. Thanks for reaching out Mihail!

Time for another cold shower...