SQL Update query

[Mason]

I have a buch of fields that I'd like to update on a form at once, but
I'm having a problem with allowing fields to be blank. If any of the
fields in the SQL statement are blank, the update doesn't work.

The way I see it, for example, SET ContactLastName='' (two single
quotes) is somehow invalidating the update, although I can't seem to
find documentation on that. If this is the case, it looks like I'd
have to do some major tweaking to the code listed below. Any input
would be appreciated.

Here is the code I'm using. I had split up the SQL string assignment
because access has a limit to the number of line continuations you can
use.

Private Sub SQLUpdate()
Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "UPDATE tblSystem " & _
"SET ContactLastName='" & tboContactLast & "'" & _
",ContactFirstName='" & tboContactFirst & "'" & _
",ContactPhone='" & tboContactPhone & "'" & _
",InstallDate='" & tboInstallDate & "'" & _
",TimerModel='" & tboTimerModel & "'" & _
",City='" & tboSystemCity & "'" & _
",State='" & tboSystemState & "'" & _
",Zip='" & tboSystemZip & "'" & _
",BackFlowModel='" & tboBackFlowModel & "'" & _
",LastActivation='" & tboLastActivation & "'" & _
",LastBlowout='" & tboLastBlowout & "'" & _
",TimerLocation='" & tboTimerLocation & "'" & _
",RainSensor='" & tboRainSensor & "'" & _
",PlumbingInside='" & tboPlumbingInside & "'" & _
",BlowOutValve='" & tboBlowOutValve & "'" & _
",ValveBox1='" & tboValveBox1 & "'" & _
",ValveBox2='" & tboValveBox2 & "'" & _
",ValveBox3='" & tboValveBox3 & "'" & _
",ValveBox4='" & tboValveBox4 & "'" & _
",ValveBox5='" & tboValveBox5 & "'"
strSQL = strSQL & _
",Comments='" & tboComments & "'" & _
" WHERE Address='" & tboSystemAddress & "'" & _
" AND City='" & tboSystemCity & "'"
dbs.Execute (strSQL)
End Sub

[Fletcher Arnold]

"Mason" <ma***@codemonkey.cc> wrote in message
news:eb**************************@posting.google.c om...

I have a buch of fields that I'd like to update on a form at once, but
I'm having a problem with allowing fields to be blank. If any of the
fields in the SQL statement are blank, the update doesn't work.

The way I see it, for example, SET ContactLastName='' (two single
quotes) is somehow invalidating the update, although I can't seem to
find documentation on that. If this is the case, it looks like I'd
have to do some major tweaking to the code listed below. Any input
would be appreciated.

Here is the code I'm using. I had split up the SQL string assignment
because access has a limit to the number of line continuations you can
use.

Private Sub SQLUpdate()
Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "UPDATE tblSystem " & _
"SET ContactLastName='" & tboContactLast & "'" & _
",ContactFirstName='" & tboContactFirst & "'" & _
",ContactPhone='" & tboContactPhone & "'" & _
",InstallDate='" & tboInstallDate & "'" & _
",TimerModel='" & tboTimerModel & "'" & _
",City='" & tboSystemCity & "'" & _
",State='" & tboSystemState & "'" & _
",Zip='" & tboSystemZip & "'" & _
",BackFlowModel='" & tboBackFlowModel & "'" & _
",LastActivation='" & tboLastActivation & "'" & _
",LastBlowout='" & tboLastBlowout & "'" & _
",TimerLocation='" & tboTimerLocation & "'" & _
",RainSensor='" & tboRainSensor & "'" & _
",PlumbingInside='" & tboPlumbingInside & "'" & _
",BlowOutValve='" & tboBlowOutValve & "'" & _
",ValveBox1='" & tboValveBox1 & "'" & _
",ValveBox2='" & tboValveBox2 & "'" & _
",ValveBox3='" & tboValveBox3 & "'" & _
",ValveBox4='" & tboValveBox4 & "'" & _
",ValveBox5='" & tboValveBox5 & "'"
strSQL = strSQL & _
",Comments='" & tboComments & "'" & _
" WHERE Address='" & tboSystemAddress & "'" & _
" AND City='" & tboSystemCity & "'"
dbs.Execute (strSQL)
End Sub

Is there any sane reason that you are updating all of these fields by code?
Surely if you had a bound form this would save you the heartache. Perhaps
this is unbound forms with client-server system, but then why Dim dbs As
DAO.Database?

Fletcher

[Mason Wood]

Fletcher, thanks for your response.

The reason I'm doing it manually may be quite flawed, as I'm more of a
self-taught Access user. I didn't want a user to change the value of
any field by simply typing over it, and bound forms seem to
automatically do that. My goal is to minimize user error.

The fields in the form I'm using in this case are not bound. They are
populated via code from the result of a list box that IS bound to a
table. If I bind each form field to a table field, won't I get the
automatic data overwrite I mentioned above?

The only Access 'guru' I know irl despises most of the features of
Access, utilizing only the table structures and VBA, and maybe a startup
macro. The student takes on the attributes of the teacher in this case,
although it's more from lack of knowledge than solid reasoning.

Anyway, I hope some of that jumble of thoughts and ideas helped you in
some way.

Mason

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mason Wood]

I figured it out. I thought it was a problem with the SQL statement
when in fact I needed to flag each field to allow a zero-length string.

Thanks again,

Mason

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Fletcher Arnold]

"Mason Wood" <ma***@codemonkey.cc> wrote in message
news:3f*********************@news.frii.net...

Fletcher, thanks for your response.

The reason I'm doing it manually may be quite flawed, as I'm more of a
self-taught Access user. I didn't want a user to change the value of
any field by simply typing over it, and bound forms seem to
automatically do that. My goal is to minimize user error.

The fields in the form I'm using in this case are not bound. They are
populated via code from the result of a list box that IS bound to a
table. If I bind each form field to a table field, won't I get the
automatic data overwrite I mentioned above?

The only Access 'guru' I know irl despises most of the features of
Access, utilizing only the table structures and VBA, and maybe a startup
macro. The student takes on the attributes of the teacher in this case,
although it's more from lack of knowledge than solid reasoning.

Anyway, I hope some of that jumble of thoughts and ideas helped you in
some way.

Mason

Hi Mason
Don't forget that the behavious you are talking about is only the default
behaviour of Access forms - and can be customized to work exactly as you
wish with only a few minutes work. For example, if the record should appear
in the form as read-only or perhaps you have a button to explicitly say
'edit' - or perhaps you want to have a prompt 'Are you sure you wish to
update?'.
Any of these could be done with a tiny amount of work - it is not true to
say 'Access automatically updates the record' - this is just the behaviour
of forms with no additional work. I assume you 'Access guru' knows that.
I understand wanting to modify default behaviour but honestly - there has to
be some special reasons to make all your forms unbound. Perhaps you could
get some feedback on this general issue from the rest of the group.

Fletcher