Results 1 to 11 of 11

Update recordset not properly functioning with Indexed field; database published to SharePoint

  1. #1
    wily_wolf is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Feb 2016
    Posts
    31

    Update recordset not properly functioning with Indexed field; database published to SharePoint

    I have a database that I recently published to SharePoint. Before I published to SharePoint, everything seemed to be working as was meant to. However, after I published it, the database started having issues with updating information within the table because of the field I designated as an "Index" or "Unique" value. I have this indexed value because I don't want there to be multiples of the same record (which seems to happen when I uncheck the "Index" box). If the box is left unchecked then there will be a new (but same) record entered under a different ID. Here is the code that I have:

    Private Sub cmdSaveRecord_Click()
    Dim rst As DAO.Recordset
    Dim strMsg As String
    Set rst = CurrentDb.OpenRecordset("tblECLUData")

    If DCount("ID", "tblECLUData", "ClaimNumber = '" & Me.cboFind.value & "'") = 0 Then 'record does not exist
    rst.AddNew
    Else
    rst.Edit
    End If

    rst![State] = Me.txtState
    rst![ClaimNumber] = Me.txtClaimNumber
    rst![ClaimStatus] = Me.cboClaimStatus
    rst![ECLUAnalyst] = Me.cboLITRep
    rst![ECLUMgmt] = Me.cboECLUMgmt
    rst![Plaintiff] = Me.txtIns
    rst![ReportType] = Me.cboRptType
    rst![LitAssoc] = Me.cboLitAssoc
    rst![AsgmntRec] = Me.txtAsgmntRec
    rst![ClmFileSent] = Me.txtClmFileSnt


    rst![LitAnalystCal] = Me.txtLitAnalystCal
    rst![AsgmntClo] = Me.txtAsgmntClosed
    rst![TMDiaryDate] = Me.txtDiary
    rst![DteDue] = Me.txtDteDue
    rst![DteComplete] = Me.txtDteComplete

    rst.Update

    rst.Close
    Set rst = Nothing
    strMsg = "Data successfully entered into database."

    MsgBox strMsg, vbOKOnly, "Entry Successful"

    End Sub

    I should say that, if this is the first time that some is adding the information to the table then it works fine, but, if they try to update the information from the form, then Error 3899 comes up: "The list item could not be inserted or updated because duplicate values were found for one or more fields in the list."

    Any help would be appreciated. Thanks.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    15,852
    Your recordset was opened on the whole table, and at the point you edit, it's on the first record (you didn't move it anywhere). That means it's editing the first record to match some other record, thus your error. I'd open the recordset on SQL that used the same criteria as the DCount(), and test for EOF.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    wily_wolf is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Feb 2016
    Posts
    31
    I think I understand what you're saying, but could you provide an illustration of what the code may look like?

  4. #4
    Bulzie is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    506
    Maybe something like:

    Set rst = CurrentDb.OpenRecordset("Select * From tblECLUData Where ClaimNumber = '" & Me.cboFind.value & "'")

    If rst.RecordCount = 0 then
    rst.AddNew
    Else
    rst.Edit
    End If

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    15,852
    Like I said, I would test for EOF, as in my experience testing the record count isn't always reliable.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  6. #6
    wily_wolf is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Feb 2016
    Posts
    31
    So, instead of

    "If rst.RecordCount = 0 Then"

    use

    "If Not rst.EOF Then"?

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    15,852
    Yes, with or without the Not depending on how your logic flows.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  8. #8
    wily_wolf is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Feb 2016
    Posts
    31
    Okay, thank you. I'll give that a try and let you know the results.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    15,852
    No problem, post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  10. #10
    wily_wolf is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Feb 2016
    Posts
    31
    I used the code shown by Bulzie along with "If rst.EOF Then" and that seemed to have done the trick. Greatly appreciate the help.

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    15,852
    Happy to help!
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 03-04-2016, 03:41 AM
  2. Replies: 2
    Last Post: 04-16-2014, 10:17 AM
  3. Update Query functioning in second attempt
    By drunkenneo in forum Programming
    Replies: 2
    Last Post: 11-18-2013, 04:12 AM
  4. Need design suggestions for indexed-no duplicates field
    By jax1000rr in forum Database Design
    Replies: 3
    Last Post: 01-29-2013, 05:42 AM
  5. After Update/concatenation not functioning
    By tariq1 in forum Programming
    Replies: 4
    Last Post: 08-19-2012, 10:54 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums