CODEWITHSUNDEEP
sqldatabasevbams-access
Nicholas
Nicholas

Reputation: 3737

VBA - Access - updating table with new data from user form

I have created a form in Access (provides information on reviewers), of which one part is a comment section (I have two comment sections, one shows the comments against the person and the other is to add new comments).

I have a table that holds the comments against the ID of each person which the form pulls in when you search for a reviewer through a combo box.

I have done the code for adding the comments (ID of the person and the new comments), but it just creates a new field in the table (or does not add it if I stop duplications).

What I want it to do is look to see if the ID is in the table, and if it is, replace the old comment with the new comment, otherwise, add the new ID/Comment to the table.

My code so far is:

Dim strSQL As String

   strSQL = "INSERT INTO panelComment (ID, Comments) VALUES (" & Me!Text14 & ", '" & Me!Text29 & "');"
   DoCmd.RunSQL strSQL

End Sub

Any suggestions? Thank you.

Upvotes: 1

Views: 3586

Answers (1)

Whencesoever
Whencesoever

Reputation: 2296

Dim strSQL As String
If DCount("Id","panelCOmment","ID=" & Me!Text14) >0 then
strSQL = "Update panelComment set Comments = '" & Me!Text29 & "' where Id = " & Me!Text14
else
   strSQL = "INSERT INTO panelComment (ID, Comments) VALUES (" & Me!Text14 & ", '" & Me!Text29 & "');"

ENd if
DoCmd.RunSQL strSQL

Upvotes: 1

Related Questions