SQL Update query

**Fletcher Arnold** · Nov 12 '05, 03:44 PM

Re: SQL Update query

"Mason" <mason@codemonk ey.cc> wrote in message
news:eb2cafcc.0 310270851.38278 94d@posting.goo gle.com...[color=blue]
> 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 & "'" & _
> ",ContactFirstN ame='" & tboContactFirst & "'" & _
> ",ContactPhone= '" & tboContactPhone & "'" & _
> ",InstallDate=' " & tboInstallDate & "'" & _
> ",TimerMode l='" & tboTimerModel & "'" & _
> ",City='" & tboSystemCity & "'" & _
> ",State='" & tboSystemState & "'" & _
> ",Zip='" & tboSystemZip & "'" & _
> ",BackFlowModel ='" & tboBackFlowMode l & "'" & _
> ",LastActivatio n='" & tboLastActivati on & "'" & _
> ",LastBlowout=' " & tboLastBlowout & "'" & _
> ",TimerLocation ='" & tboTimerLocatio n & "'" & _
> ",RainSenso r='" & tboRainSensor & "'" & _
> ",PlumbingInsid e='" & tboPlumbingInsi de & "'" & _
> ",BlowOutValve= '" & tboBlowOutValve & "'" & _
> ",ValveBox1 ='" & tboValveBox1 & "'" & _
> ",ValveBox2 ='" & tboValveBox2 & "'" & _
> ",ValveBox3 ='" & tboValveBox3 & "'" & _
> ",ValveBox4 ='" & tboValveBox4 & "'" & _
> ",ValveBox5 ='" & tboValveBox5 & "'"
> strSQL = strSQL & _
> ",Comments= '" & tboComments & "'" & _
> " WHERE Address='" & tboSystemAddres s & "'" & _
> " AND City='" & tboSystemCity & "'"
> dbs.Execute (strSQL)
> End Sub[/color]

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** · Nov 12 '05, 03:44 PM

Re: SQL Update query

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** · Nov 12 '05, 03:45 PM

Re: SQL Update query

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** · Nov 12 '05, 03:45 PM

Re: SQL Update query

"Mason Wood" <mason@codemonk ey.cc> wrote in message
news:3f9d5b16$0 $200$75868355@n ews.frii.net...[color=blue]
> 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[/color]

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