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