Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116

    Unanswered: Msg Box if dup record created

    I have the following code for the click event on 4 ctls which creates a record in a table having 3 PKs (TEmployeeID, TCourseID, and TQuestionID). If the event would create a duplicate record in the table, I want a msg box to come up requesting the user to confirm they would like to change their entry to whatever they just clicked. The TAnswer value is the only thing differentiating the events (value=1-4). I'm not sure what the best way to go about doing this is (consolidate this code into 1 event with 4 cases and then have the error event requesting if the record should be updated, or what).

    Private Sub B_Click()
    Dim rst As New ADODB.Recordset
    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection
    rst.Index = "PrimaryKey"
    rst.Open "tblQuizTemp", cnn, adOpenStatic, adLockOptimistic, _
    adCmdTableDirect
    rst.AddNew
    rst!TEmployeeID = Me![qzEmployeeID]
    rst!TCourseID = Me![CourseID]
    rst!TQuestionID = Me![QuestionID]
    rst!TAnswer = "2"
    rst.Update
    End Sub

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Dulpicate Records !!

    Hi

    I would suggest entering a procedure like the following in the form module, and calling it from the four(?) button click (or whatever determents the TAnswer value (string), and use the appropriate value for the argument.

    Code:
    Sub EnterAnswer(TAns As String)
        Dim NumOfRecords As Integer
        
        NumOfRecords = DCount("TEmployeeID", "tblQuizTemp", "TEmployeeID = " & [qzEmployeeID] & " AND TCourseID = " & [CourseID] & " AND TQuestionID = " & [QuestionID])
        
        If NumOfRecords > 0 Then
            If MsgBox("Record already exists. Do you want to update existing record ?" _
                        , vbYesNo + vbQuestion, "Your Title") = vbYes Then
                
                DoCmd.RunSQL "UPDATE tblQuizTemp " & _
                                "SET TAnswer = " & TAns & _
                                "WHERE TEmployeeID = " & [qzEmployeeID] & " AND TCourseID = " & [CourseID] & " AND TQuestionID = " & [QuestionID] & ";"
            End If
        Else
            DoCmd.RunSQL "INSERT INTO tblQuizTemp (TEmployeeID, TCourseID, TQuestionID, TAnswer ) " & _
                            "SELECT " & [qzEmployeeID] & " AS Expr1, " & [CourseID] & " AS Expr2, " & [QuestionID] & " AS Exp3, " & TAns & " AS Exp4 ;"
    
        End If
    End Sub
    (I assume all IDs are numeric)

    In DB is multi-user you may need to consider transactions.

    Hope this helps with some ideas


    MTB

  3. #3
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    MTB,

    Thanks for your help. Do you mean for this to be part of the on click event for each possible answer, or as part of the form's VB?

    THX,
    -K

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I am not sure how you determine the 'Answer' but based on you previous post I envisage using my subroutine something like

    Private Sub B_Click()

    EnterAnswer "2"

    End Sub

    and if 'A' button is for answer "1" then

    Private Sub A_Click()

    EnterAnswer "1"

    end sub

    etc

    Hope that is a little clearer

    MTB

  5. #5
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    I can't get this code to work. I'm not sure what I'm doing wrong. I keep getting the error sub or funtion not defined on the EnterAnswer line.

    Private Sub A_Click()
    EnterAnswer "1"
    Dim NumOfRecords As Integer

    NumOfRecords = DCount("TEmployeeID", "tblQuizTemp", "TEmployeeID = " & [qzEmployeeID] & " AND TCourseID = " & [CourseID] & " AND TQuestionID = " & [QuestionID])

    If NumOfRecords > 0 Then
    If MsgBox("Record already exists. Do you want to update existing record ?" _
    , vbYesNo + vbQuestion, "Your Title") = vbYes Then

    DoCmd.RunSQL "UPDATE tblQuizTemp " & _
    "SET TAnswer = " & TAns & _
    "WHERE TEmployeeID = " & [qzEmployeeID] & " AND TCourseID = " & [CourseID] & " AND TQuestionID = " & [QuestionID] & ";"
    End If
    Else
    DoCmd.RunSQL "INSERT INTO tblQuizTemp (TEmployeeID, TCourseID, TQuestionID, TAnswer ) " & _
    "SELECT " & [qzEmployeeID] & " AS TEmployeeID, " & [CourseID] & " AS TCourseID, " & [QuestionID] & " AS TQuestionID, " & TAns & " AS TAnswer;"

    End If

    End Sub

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Mesage box for dulicate record & Cosolidated Sub Procedure

    Hi again

    I think you have not grasped the point of a sub procedure.

    The AddAnswer procedure should be called from each answer button click event.

    The sub or funtion not defined error is due to the sub not being defined OUTSIDE the click event.

    The Code in the form module shoud look like this

    Code:
    Sub EnterAnswer(TAns As String)
        Dim NumOfRecords As Integer
        
        NumOfRecords = DCount("TEmployeeID", "tblQuizTemp", "TEmployeeID = " & [qzEmployeeID] & " AND TCourseID = " & [CourseID] & " AND TQuestionID = " & [QuestionID])
        
        If NumOfRecords > 0 Then 'RECORD ALREADY EXISTS
            If MsgBox("Record already exists. Do you want to update existing record ?" _
                        , vbYesNo + vbQuestion, "Your Title") = vbYes Then
                'OVERWRITE ANSWER IF RESPONCE IS 'YES'
                DoCmd.RunSQL "UPDATE tblQuizTemp " & _
                                "SET TAnswer = " & TAns & _
                                "WHERE TEmployeeID = " & [qzEmployeeID] & " AND TCourseID = " & [CourseID] & " AND TQuestionID = " & [QuestionID] & ";"
            End If
        Else 'NO RECORD EXISTS
            DoCmd.RunSQL "INSERT INTO tblQuizTemp (TEmployeeID, TCourseID, TQuestionID, TAnswer ) " & _
                            "SELECT " & [qzEmployeeID] & " AS Expr1, " & [CourseID] & " AS Expr2, " & [QuestionID] & " AS Exp3, " & TAns & " AS Exp4 ;"
    
        End If
    End Sub
    
    Private Sub A_Click()
        EnterAnswer "1"
    End Sub
    
    Private Sub B_Click()
        EnterAnswer "2"
    End Sub
    
    Private Sub C_Click()
        EnterAnswer "3"
    End Sub
    
    Private Sub BD_Click()
        EnterAnswer "4"
    End Sub

    If you are not familiar with acton queries in code, but are OK with DAO record sets then the EnterAnswer Sub Procedure could be written something like the following

    Code:
    Sub EnterAnswer(TAns As String)
        Dim rst As New ADODB.Recordset
        Dim cnn As ADODB.Connection
        Dim SQL As String
        
        Set cnn = CurrentProject.Connection
    
        SQL = "SELECT TEmployeeID, TCourseID, TQuestionID, TAnswer "
        SQL = SQL & "FROM tblQuizTemp "
        SQL = SQL & "WHERE TEmployeeID = " & Me![qzEmployeeID] & " AND "
        SQL = SQL & "TCourseID = " & Me![CourseID] & " And "
        SQL = SQL & "TQuestionID = " & Me![QuestionID] & ";"
        
        'THE WHERE CONDITION LIMITS THE RECORD SET TO A SINGLE (PRIMARY KEY) RECORD - IF IT EXISTS
    
        
        rst.Open SQL, cnn, adOpenStatic, adLockOptimistic
            
        If rst.BOF And rst.EOF Then 'NO RECORD EXISTS
            rst.AddNew
            rst!TEmployeeID = Me![qzEmployeeID]
            rst!TCourseID = Me![CourseID]
            rst!TQuestionID = Me![QuestionID]
            rst!TAnswer = TAns
            rst.Update
        Else 'RECORD ALREADY EXISTS
            If MsgBox("Record already exists. Do you want to update existing record ?" _
                        , vbYesNo + vbQuestion, "Your Title") = vbYes Then
                'OVERWRITE ANSWER IF RESPONCE IS 'YES'
                rst!TAnswer = TAns
            End If
        
        End If
        
        rst.Close
        cnn.Close
        
    End Sub
    Again all above assumed all IDs are numeric and TAnswer is text.

    Hope this is a little clearer!

    MTB

  7. #7
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    You're right to assume I didn't understand this. I'm still learning VB. Thanks, so much, for all your help!!!

  8. #8
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    I'm getting a run time error saying I cancelled the previous operation when the dcount begins. Any ideas?

  9. #9
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    To be honest, I have no idea what the probelm could be (I have never had that error relating to DCount - has anyone else?), but it is difficult to say without having the actual code & objects to run and look at.

    I obvoiusly have not run the code so there could well be syntax or logical errors which would need sorting.

    So, if it's not too big, you could post your DB, or at last a copy with the relevant tables forms and code left in it.


    MTB

  10. #10
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    MTB,

    Thanks for all your help. Here's a copy. It's someone else's database that I've been working through the code on.

  11. #11
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    Sorry, here is the file...
    Attached Files Attached Files

  12. #12
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Unfortunatly I cannot open the DB, I assume it's Access 2003 and I anlo have 97 and 2k currently avaliable to me.

    Sorry.

    Have you tried break point and stepping through the code, or inseting message boxes to print varable or control values to screen (or imediate window) at points before the error to verify you are getting what you expect !?

    MTB

  13. #13
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    The final answer thanks to Izy and MTB... It's a long one.

    http://www.dbforums.com/showpost.php...05&postcount=1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •