If Record Exist, Replace Value Only on Selected Field not to Add new record (1 Viewer)

luzmen · Sep 19, 2018

Hello World,

I have this code that copy values to table:

Code:

On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
               
    Set db = CurrentDb()

    Set qdf = db.CreateQueryDef("", "INSERT INTO [TBL_Answer] (UserID, QAType, QuestionID, ChoiceAnswer, CorrectAnswer, Checking) VALUES ([MyVal1], [MyVal2], [MyVal3], [MyVal4], [MyVal5], [MyVal6])")
    qdf.Parameters("[MyVal1]") = Forms![FRM_Question]![Text_StoredUser]
    qdf.Parameters("[MyVal2]") = Forms![FRM_Question]![Text101]
    qdf.Parameters("[MyVal3]") = Forms![FRM_Question]![Text_CurRecord]
    qdf.Parameters("[MyVal4]") = Forms![FRM_Question]![Frame89]
    qdf.Parameters("[MyVal5]") = Forms![FRM_Question]![Text_Answer]
    qdf.Parameters("[MyVal6]") = Forms![FRM_Question]![Text_Check]

    qdf.ReturnsRecords = False
    qdf.Execute

ExitMe:
    Set qdf = Nothing
    Set db = Nothing

    Exit Sub
ErrorHandler:
    MsgBox Err.Number & ": " & Err.Description
    GoTo ExitMe

How to add/revise a/the code that if UserID, QAType, QuestionID, CorrectAnswer was already added in the target Table to save, it will only replace ChoiceAnswer and Checking not to add another record when execute.

Thank you so much in advance.

isladogs · Sep 19, 2018

Post was moderated. Posting to trigger email notifications.
I've also deleted the duplicate post

The_Doc_Man · Sep 19, 2018

The problem is that you technically cannot make the INSERT INTO a "conditional" query if you call it. So the trick is (perhaps) to make the subroutine do THREE things:

1. Test for the existence of the record based on whatever is the prime key, whether it is a simple key or compound key, using a DCount.

2. If the result of the test was 0, perform your INSERT INTO

3. If the result of the test was 1, instead perform an UPDATE.

(4th thing: If the test was > 1 then your key isn't unique, which is not so good).

Now, as to using a querydef... You used it correctly so far as I can tell, but it might be a shade simpler for cleanup using another method.

Consider (for the INSERT INTO case and also for the UPDATE case) that you might wish to build this as a string. Action queries can be used in the way I will offer as a possible alternative. I want to emphasize that you didn't do anything wrong. This is just another way to approach the problem that I personally prefer but YOU WERE NOT WRONG to use what you used.

Code:

...
Dim xSQL as String

   xSQL = "INSERT INTO [TBL_Answer] (UserID, QAType, QuestionID, ChoiceAnswer, CorrectAnswer, Checking) VALUES ('"
   xSQL = xSQL & Forms![FRM_Question]![Text_StoredUser] & "','"
   xSQL = xSQL & Forms![FRM_Question]![Text101] & "','"
   {three more parameters}
   xSQL = xSQL & Forms![FRM_Question]![Text_Check] & "' );"

   CurrentDB.Execute xSQL, dbFailOnError

You can also do UPDATE queries this way. You already have error checking in place in your sub, so if the query fails, that dbFailOnError flag will let you know. The way I see it, creating that temporary querydef vs creating a simple string is six of one, half a dozen of another. And you don't have to worry about setting the "ReturnsRecords" flag as false.

Also, if it happens that any of those fields are numeric, you can omit the apostrophe that is part of that sequence double-quote apostrophe comma apostrophe double-quote ("','") that I put at the end of the individual concatenation statements.

If Record Exist, Replace Value Only on Selected Field not to Add new record (1 Viewer)

luzmen

Registered User.

isladogs

MVP / VIP

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread