trouble with SQL update

[Mizpah]

I,ve been working on a form that uses an Access database to populate
formfields on a Word form I've created. Things have been going
smoothly, but now I've ran into a bit of a problem. On the Word form
there are 3 activeX command buttons. One creates a new record, one
deletes it, and the other updates an existing record.

I successfully have been able to set up my code to fill the form from
the database, and to create a new record in the database from data
entered onto the form. Now I am working on the update feature. I am new
to SQL, but I have a good grasp on the basics. I know how I want to do
this, but I am not sure of the most efficient way to write the code to
accomplish this.

On the form are 40 formfields. The user first fills the form by
choosing the existing record they want to update. The formfields on the
form are filled and the form is protected. The user tabs to the
formfield they want to change, make the change and then push the update
existing record button. Ideally only those formfileds that were changed
are updated. This is what I have:

datConnection.ConnectionString = "data
source=C:\datastores\clients.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
datConnection.Open

Call GetFormData

Dim strSQL As String

strSQL = "SELECT * FROM Main WHERE Main!Client = " & Chr(34) &
datClient & Chr(34) & ";"
datRS.Open strSQL, datConnection, adOpenKeyset, adLockOptimistic


If Not datRS.EOF Then
datListName = datRS!ListName
intResponse = MsgBox("Are you sure you want to update the
record for " & datListName & "?", 36)
Else
MsgBox "The record for " & datListName & "was not found.",
vbInformation
End If

If intResponse = 6 Then
Update Main SET ????????????? WHERE Main!Client = " & Chr(34) &
datListName & Chr(34) & ";"
Else
End
End If

datRS.Close
datConnection.Close

Set datRS = Nothing
Set datConnection = Nothing

But I am not sure what to put after the SET since the user could change
any number and combination of the existing 40 formfields. I do have two
public subs one called GetFormData, which takes the text in each
formfield and assigns it to a variable. The other is PopulateDatabase
which takes the data in those variables and then assigns them to the
appropriate place in the database. I don't know if I could use this in
some way to overwrite the existing record, but that doesn't seem to
efficient.

Any help would be appreciated.
Thanks

 

[Doug Robbins - Word MVP]

As you do not know which field in the record the user has changed, just
update them all.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Mizpah]

Thank you Doug

I thought about doing that, but then, for some reason, I discarded it
because I wrapped my mind around doing something else. So I went ahead
and wrote the code to update them all and it worked great. Don't know
what I was thinking on Friday, but after a long week I think my mind
was fried.