Tags: access, created, database, duplicate, function, input, microsoft, mth, mysql, oracle, prevent, records, sql, table, textboxes

how to prevent duplicate records?

On Database » Microsoft Access

35,317 words with 11 Comments; publish: Fri, 04 Jan 2008 11:03:00 GMT; (25093.75, « »)

i have created a function to input data from four textboxes into a table. how do i prevent duplicate records of the same mth/Yr into the table? editing of the data in the table with reference to the mt/Yr is allowed, but how to prevent duplicate records?

Code: ( text )
  1. Public Sub PutInMonthlyRecords()
  2. Dim sql As String
  3. Dim Db As DAO.Database
  4. Dim rs As DAO.Recordset
  5. Dim f As Form
  6. Dim MthYr As String
  7. Set f = Forms!frmQpi
  8. MthYr = f("txtMthYr")
  9. sql = "SELECT * FROM [tblQpiMonthly] WHERE (([tblQpiMonthly].[Input MthYr] = 'MthYr' ));"
  10. Set Db = CurrentDb()
  11. Set rs = Db.OpenRecordset(sql)
  12. If rs.RecordCount = 0 Then
  13. If (f!txtTotalPF) = 0 Then
  14. Exit Sub
  15. End If
  16. rs.AddNew
  17. rs![Input MthYr] = f("txtMthYr")
  18. rs![Monthly TotalPF] = f("txtTotalPF")
  19. rs![Monthly Rejection] = f("txtTotalAvoid")
  20. rs![Monthly TotalAvoid] = f("txtRejection")
  21. rs.Update
  22. Else
  23. If (f!txtTotalPF) = 0 Then
  24. rs.Delete
  25. Exit Sub
  26. End If
  27. rs.Delete
  28. rs.AddNew
  29. rs![Input MthYr] = f("txtMthYr")
  30. rs![Monthly TotalPF] = f("txtTotalPF")
  31. rs![Monthly Rejection] = f("txtTotalAvoid")
  32. rs![Monthly TotalAvoid] = f("txtRejection")
  33. rs.Update
  34. End If
  35. Db.Close

All Comments

Leave a comment...

  • 11 Comments
    • Quote:
      === Original Words ===

      i have created a function to input data from four textboxes into a table. how do i prevent duplicate records of the same mth/Yr into the table? editing of the data in the table with reference to the mt/Yr is allowed, but how to prevent duplicate records?

      Code: ( text )
      1. Public Sub PutInMonthlyRecords()
      2. Dim sql As String
      3. Dim Db As DAO.Database
      4. Dim rs As DAO.Recordset
      5. Dim f As Form
      6. Dim MthYr As String
      7. Set f = Forms!frmQpi
      8. MthYr = f("txtMthYr")
      9. sql = "SELECT * FROM [tblQpiMonthly] WHERE (([tblQpiMonthly].[Input MthYr] = 'MthYr' ));"
      10. Set Db = CurrentDb()
      11. Set rs = Db.OpenRecordset(sql)
      12. If rs.RecordCount = 0 Then
      13. If (f!txtTotalPF) = 0 Then
      14. Exit Sub
      15. End If
      16. rs.AddNew
      17. rs![Input MthYr] = f("txtMthYr")
      18. rs![Monthly TotalPF] = f("txtTotalPF")
      19. rs![Monthly Rejection] = f("txtTotalAvoid")
      20. rs![Monthly TotalAvoid] = f("txtRejection")
      21. rs.Update
      22. Else
      23. If (f!txtTotalPF) = 0 Then
      24. rs.Delete
      25. Exit Sub
      26. End If
      27. rs.Delete
      28. rs.AddNew
      29. rs![Input MthYr] = f("txtMthYr")
      30. rs![Monthly TotalPF] = f("txtTotalPF")
      31. rs![Monthly Rejection] = f("txtTotalAvoid")
      32. rs![Monthly TotalAvoid] = f("txtRejection")
      33. rs.Update
      34. End If
      35. Db.Close

      Since you are allowing editing at the Table level, I feel as though your best option may be : in tblOptMonthly, set the Indexed property of the [Input MthY] Field to Indexed = Yes(No Duplicates). In the PutInMonthlyRecords() Routine, trap the specific Duplicate Record Error that will now be generated if you try to add a Duplicate Record on the [Input MthY] Field via VCBA code, and Exit the Sub-Routine. The Record should not be Appended since rs.Update will not be successful in this scenario.

      You are now covered in both situations:

      1) Editing a Record and creating a Duplicate Record based on the [Input MthY] Field.

      2) Adding a Record via the Sub-Routinre and creating a Duplicate Record on the [Input MthY] Field.

      #1; Fri, 04 Jan 2008 11:04:00 GMT
    • Nice answer ADezii.

      Ariel,

      If you feel that any part of this resolution is too complicated for you to implement - don't worry, just post back explaining which parts you find complicated and ADezii (or someone else even if he's too busy) will be able to help you through it.

      Error handling is a very important part of VBA code but some people avoid using it. Don't let that frighten you off this solution.

      Best of luck.

      MODERATOR.

      #2; Fri, 04 Jan 2008 11:05:00 GMT
    • Quote:
      === Original Words ===

      Nice answer ADezii.

      Ariel,

      If you feel that any part of this resolution is too complicated for you to implement - don't worry, just post back explaining which parts you find complicated and ADezii (or someone else even if he's too busy) will be able to help you through it.

      Error handling is a very important part of VBA code but some people avoid using it. Don't let that frighten you off this solution.

      Best of luck.

      MODERATOR.

      thank you. i am new to programming...i don't understand a single thing. any similiar sample programs where i can get ideas from?

      #3; Fri, 04 Jan 2008 11:06:00 GMT
    • Ariel,

      Please reread my earlier post. The wording was not random.

      Unless I/we have a particular point to help with, we are looking at saying that the answer is not good enaough and starting again.

      That is certainly not the case here, and you have a choice to use it or not. We can help you but I am certainly not prepared to look elsewhere until I'm sure you've given the answer a fair try. As I say, we can help with that. I am certainly not offering to do work for you, simply to help you do the work yourself.

      MODERATOR.

      #4; Fri, 04 Jan 2008 11:07:00 GMT
    • From the original post...
      Code: ( text )
      1. Set f = Forms!frmQpi
      2. MthYr = f("txtMthYr")
      3. sql = "SELECT * FROM [tblQpiMonthly] WHERE (([tblQpiMonthly].[Input MthYr] = 'MthYr' ));"
      Can someone please explain this to me? Some of the syntax used in this routine is unfamiliar to me, so don't think that I'm telling you anything is wrong - I just didn't quite follow it.

      The main problem I have is with the underlined sections. The way I read it, this is supposed to be matching on a month and year value found in textbox txtMthYr, but is in fact searching for the literal string "MthYr".

      Am I misreading this?

      #5; Fri, 04 Jan 2008 11:08:00 GMT
    • It took me a while to realise something. In hindsight, if the WHERE clause is wrong, this might never find a match, and hence give rise to the whole issue of creating duplicates.
      #6; Fri, 04 Jan 2008 11:09:00 GMT
    • here is the function which i re-edited. the codes that were not affecting the program have been deleted.

      Code: ( text )
      1. Public Sub PutInMonthlyRecords()
      2. Dim sql As String
      3. Dim Db As DAO.Database
      4. Dim rs As DAO.Recordset
      5. Dim f As Form
      6. Set f = Forms!frmQpi
      7. Set Db = CurrentDb()
      8. sql = "SELECT * FROM [tblQpiMonthly];"
      9. Set rs = Db.OpenRecordset(sql)
      10. If (f!txtTotalPF) = 0 Then
      11. Exit Sub
      12. End If
      13. If (f!txtTotalPF) = 0 Then
      14. Exit Sub
      15. End If
      16. If (f!txtTotalPF) = 0 Then
      17. Exit Sub
      18. End If
      19. rs.AddNew
      20. rs![Input MthYr] = f("txtMthYr")
      21. rs![Monthly TotalPF] = f("txtTotalPF")
      22. rs![Monthly Rejection] = f("txtTotalAvoid")
      23. rs![Monthly TotalAvoid] = f("txtRejection")
      24. rs.Update
      25. Db.Close
      26. End Sub
      Basically, when the record is not found in the table, it will add the new record. if the record is found in the table, it should edit the record instead of adding the same record into the table.
      #7; Fri, 04 Jan 2008 11:10:00 GMT
    • the form looks this way (see attachment0
      #8; Fri, 04 Jan 2008 11:11:00 GMT
    • the form looks this way (see attachment)
      #9; Fri, 04 Jan 2008 11:12:00 GMT
    • Ariel,

      You need to look again at ADezii's post (#2) and try to work your way through it. We can help with specific questions but not in the form of "I'm lost - please tell me the whole thing again in easier words."

      You should do what you can then, when you get stuck, post exactly what your problems is and we can help you through it - with reference to post #2 that we can all see.

      Does this make sense to you?

      If you abort from an answer because it's a little complicated to understand, and try a different tack, then we could be here forever as you keep chopping and changing. This is unfair on our experts so I don't want to see it happen.

      If you can go through it as I've described above, then we can help you.

      MODERATOR.

      #10; Fri, 04 Jan 2008 11:13:00 GMT
    • here is the code for the statement from this:

      Code: ( text )
      1. rs.AddNew
      2. rs![Input MthYr] = f("txtMthYr")
      3. rs![Monthly TotalPF] = f("txtTotalPF")
      4. rs![Monthly Rejection] = f("txtTotalAvoid")
      5. rs![Monthly TotalAvoid] = f("txtRejection")
      6. rs.Update
      to this:

      Code: ( text )
      1. While Not rs.EOF
      2. If Not rs.NoMatch Then
      3. Exit Sub
      4. rs.AddNew
      5. rs![Input MthYr] = f("txtMthYr")
      6. rs![Monthly TotalPF] = f("txtTotalPF")
      7. rs![Monthly Rejection] = f("txtTotalAvoid")
      8. rs![Monthly TotalAvoid] = f("txtRejection")
      9. rs.Update
      10. Next
      11. rs.MoveNext
      12. Wend
      #11; Fri, 04 Jan 2008 11:14:00 GMT