Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Posts
    35

    Unanswered: somehow duplicate records being inserted

    --- cross posted in dBforums Usenet Groups comp.databases.* comp.databases.ms-access forum ---

    I do have another problem though and am hoping to get some insight.

    These are the three subroutines my form uses but somehow, my form is inserting every record into my table twice. Also, is there a better way to set a default value for my variables instead of all the if statements below??

    Thanks!

    ~ Joyce



    ====================================

    Option Compare Database

    Private Sub addNewSurvey_Click()
    DoCmd.GoToRecord , , acNewRec
    End Sub

    Private Sub Form_Open(Cancel As Integer)
    DoCmd.GoToRecord , , acNewRec
    End Sub

    Private Sub SaveSurvey_Click()
    Dim errMsg As String
    Dim sqlString As String
    Dim dbs As Object
    Dim rst As Recordset
    sqlString = ""

    Set dbs = CurrentDb()

    ' check for required field
    If IsNull(providerID) Then
    errMsg = "- Provider ID" & vbNewLine
    Else
    errMsg = ""
    End If
    ' set up defualt values for non required fields
    If IsNull(q1Answer) Then
    q1 = 0
    End If
    If IsNull(q2Answer) Then
    q2 = 0
    End If
    If IsNull(q3Answer) Then
    q3 = 0
    End If
    If IsNull(q4Answer) Then
    q4 = 0
    End If
    If IsNull(q5Answer) Then
    q5 = 0
    End If
    If IsNull(q6Answer) Then
    q6 = 0
    End If
    If IsNull(q7Answer) Then
    End If
    If IsNull(q8Answer) Then
    q8 = 0
    End If
    If IsNull(q9Answer) Then
    q9 = 0
    End If
    If IsNull(q10Answer) Then
    q10 = 0
    End If
    If IsNull(q11Answer) Then
    q11 = 0
    End If
    If IsNull(q12Answer) Then
    q12 = 0
    End If
    If IsNull(q13Answer) Then
    q13 = 0
    End If
    If IsNull(q14Answer) Then
    q14Answer = 0
    End If
    If IsNull(q15Answer) Then
    q15Answer = 0
    End If
    If IsNull(q16Answer) Then
    q16Answer = 0
    End If
    If IsNull(q17Answer) Then
    q17Answer = 0
    End If
    If IsNull(q18Answer) Then
    q18Answer = ""
    End If
    If IsNull(q19Answer) Then
    q19Answer = 0
    End If
    If IsNull(q19AnswerName) Then
    q19AnswerName = ""
    End If
    If IsNull(q19AnswerPhone) Then
    q19AnswerPhone = ""
    End If

    If errMsg = "" Then

    sqlString = "INSERT INTO tblSurvey (pin, q1,q2,q3,q4,q5,q6,q7,q8,q9,q10,q11,q12,q13,"
    sqlString = sqlString & "q14,q15,q16,q17,q18,q19,q19Name,q19Phone) VALUES ("
    sqlString = sqlString & providerID & "," & q1Answer & "," & q2Answer & "," & q3Answer & "," & q4Answer
    sqlString = sqlString & "," & q5Answer & "," & q6Answer & "," & q7Answer & ","
    sqlString = sqlString & q8Answer & "," & q9Answer & "," & q10Answer & ","
    sqlString = sqlString & q11Answer & "," & q12Answer & "," & q13Answer & ","
    sqlString = sqlString & q14Answer & "," & q15Answer & "," & q16Answer & ","
    sqlString = sqlString & q17Answer & ", '" & q18Answer & "' ," & q19Answer & ",'"
    sqlString = sqlString & q19AnswerName & "','" & q19AnswerPhone & "');"

    DoCmd.RunSQL (sqlString)

    providerID.SetFocus
    Exit Sub
    Else
    MsgBox "To save this survey, a provider ID must be entered and questions 1 - 13 must be answered. Please select the 'Ok' button below, go back to the form and be sure the following information has been filled out appropriately: " & vbNewLine & errMsg
    End If

    MsgBox "Thank you. To enter a new survey and clear out this form, press the Add New survey button."
    End Sub

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    OK,

    let's start with Nulls, one long string and ADO instead of DAO.

    remove all your IF Null statements and reference to currentDB


    jiri



    sqlString = "INSERT INTO tblSurvey (pin, q1,q2,q3,q4,q5,q6,q7,q8,q9,q10,q11,q12,q13," & _
    "q14,q15,q16,q17,q18,q19,q19Name,q19Phone) VALUES (" & _
    providerID & "," & Nz(q1Answer, 0) & "," & Nz(q2Answer, 0) & "," & Nz(q3Answer, 0) & "," & _
    Nz(q4Answer, 0) & "," & Nz(q5Answer, 0) & "," & Nz(q6Answer, 0) & "," & Nz(q7Answer, 0) & "," & _
    Nz(q8Answer, 0) & "," & Nz(q9Answer, 0) & "," & Nz(q10Answer, 0) & "," & _
    Nz(q11Answer, 0) & "," & Nz(q12Answer, 0) & "," & Nz(q13Answer, 0) & "," & _
    Nz(q14Answer, 0) & "," & Nz(q15Answer, 0) & "," & Nz(q16Answer, 0) & "," & _
    Nz(q17Answer, 0) & ", '" & Nz(q18Answer, 0) & "' ," & Nz(q19Answer, 0) & ",'" & _
    Nz(q19AnswerName, "") & "','" & Nz(q19AnswerPhone, "") & "');"

    CurrentProject.Connection.Execute sqlString

  3. #3
    Join Date
    Nov 2002
    Posts
    35

    no more dupliate but now Run-time error: 2105

    Thanks playernovis,

    The code was perfect! (I broke it into many strings because sometimes my editor has a fit with long string: <http://www.perlgurl.net/work/codingStyle.gif> though this is the first time I have seem the VB Editor do this, I have seen notepad do this with really long lines too )

    I now only get one record inserted ( I still wonder where the rr in my logic was though), but now my Click event on my "Add New Survey" command button does this to me:

    Run-time error: 2105
    "You can't go to the specified record"

    Debugger tells me it is the only line in Sub addNewSurvey_Click:
    Why did this line work before, and now it doesn't?? I have include the three subs in my app below.

    Any and all help is appreciated
    ~ Joyce
    ================================================== ================

    Option Compare Database

    Private Sub addNewSurvey_Click()
    DoCmd.GoToRecord , , acNewRec
    End Sub

    Private Sub Form_Open(Cancel As Integer)
    DoCmd.GoToRecord , , acNewRec
    End Sub

    Private Sub SaveSurvey_Click()
    Dim errMsg As String
    Dim sqlString As String
    sqlString = ""


    ' check for required field
    If IsNull(providerID) Then
    errMsg = "- Provider ID" & vbNewLine
    Else
    errMsg = ""
    End If
    If errMsg = "" Then
    sqlString = "INSERT INTO tblSurvey (pin, q1,q2,q3,q4,q5,q6,q7,q8,q9,q10,q11,q12,q13,"
    sqlString = sqlString & "q14,q15,q16,q17,q18,q19,q19Name,q19Phone) VALUES (" & providerID & ","
    sqlString = sqlString & Nz(q1Answer, 0) & "," & Nz(q2Answer, 0) & "," & Nz(q3Answer, 0) & "," & Nz(q4Answer, 0)
    sqlString = sqlString & "," & Nz(q5Answer, 0) & "," & Nz(q6Answer, 0) & "," & Nz(q7Answer, 0) & "," & Nz(q8Answer, 0)
    sqlString = sqlString & "," & Nz(q9Answer, 0) & "," & Nz(q10Answer, 0) & "," & Nz(q11Answer, 0) & ","
    sqlString = sqlString & Nz(q12Answer, 0) & "," & Nz(q13Answer, 0) & "," & Nz(q14Answer, 0) & ","
    sqlString = sqlString & Nz(q15Answer, 0) & "," & Nz(q16Answer, 0) & "," & Nz(q17Answer, 0) & ", '"
    sqlString = sqlString & Nz(q18Answer, 0) & "' ," & Nz(q19Answer, 0) & ",'" & Nz(q19AnswerName, "")
    sqlString = sqlString & "','" & Nz(q19AnswerPhone, "") & "');"

    CurrentProject.Connection.Execute sqlString

    providerID.SetFocus
    MsgBox "Thank you. To enter a new survey and clear out this form, press the Add New survey button."
    Exit Sub
    Else
    MsgBox "To save this survey, a provider ID must be entered and questions 1 - 13 must be answered. Please select the 'Ok' button below, go back to the form and be sure the following information has been filled out appropriately: " & vbNewLine & errMsg
    End If
    End Sub

  4. #4
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    i looked at your code again and I have to correct my help little bit....

    you mix together two things.... on one side you have fully bound form to your table and on the other side you use INSERT.

    you cannot use both - that's why you have duplicates - you save it using INSERT and Access saves it by itself.

    1. if you want to bound form (have table as Datasource), do it, but remove that INSERT and access with save the record for you. Have validation rule for each field of the table and that's it.


    2. if you want to have fully unbound form, then you have to use INSERT, but also reading data from the table, moving from record to record....

    look at NORTHWIND - microsoft sample database (it's saved in Samples folder under Office) - it's a good start.


    jiri

  5. #5
    Join Date
    Nov 2002
    Posts
    35
    Originally posted by playernovis
    i looked at your code again and I have to correct my help little bit....

    you mix together two things.... on one side you have fully bound form to your table and on the other side you use INSERT.

    you cannot use both - that's why you have duplicates - you save it using INSERT and Access saves it by itself.


    jiri
    Ohhhh......now I get it! I didn't understand that binding the form also caused the insert. It all makes sense now. Thanks so much!

    ~ Joyce

Posting Permissions

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