By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,523 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

how to prevent duplicate records?

P: 6
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?

Expand|Select|Wrap|Line Numbers
  1. Public Sub PutInMonthlyRecords()
  2.  
  3.     Dim sql As String
  4.     Dim Db As DAO.Database
  5.     Dim rs As DAO.Recordset
  6.     Dim f As Form
  7.     Dim MthYr As String
  8.  
  9.     Set f = Forms!frmQpi
  10.  
  11.     MthYr = f("txtMthYr")
  12.  
  13.     sql = "SELECT * FROM [tblQpiMonthly] WHERE (([tblQpiMonthly].[Input MthYr] = 'MthYr' ));"
  14.  
  15.     Set Db = CurrentDb()
  16.     Set rs = Db.OpenRecordset(sql)
  17.  
  18.  
  19.         If rs.RecordCount = 0 Then
  20.             If (f!txtTotalPF) = 0 Then
  21.                 Exit Sub
  22.             End If
  23.  
  24.             rs.AddNew
  25.                 rs![Input MthYr] = f("txtMthYr")
  26.                 rs![Monthly TotalPF] = f("txtTotalPF")
  27.                 rs![Monthly Rejection] = f("txtTotalAvoid")
  28.                 rs![Monthly TotalAvoid] = f("txtRejection")
  29.  
  30.             rs.Update
  31.  
  32.       Else
  33.             If (f!txtTotalPF) = 0 Then
  34.                 rs.Delete
  35.  
  36.                 Exit Sub
  37.               End If
  38.  
  39.  
  40.             rs.Delete
  41.             rs.AddNew
  42.                 rs![Input MthYr] = f("txtMthYr")
  43.                 rs![Monthly TotalPF] = f("txtTotalPF")
  44.                 rs![Monthly Rejection] = f("txtTotalAvoid")
  45.                 rs![Monthly TotalAvoid] = f("txtRejection")
  46.  
  47.             rs.Update
  48.  
  49.         End If
  50.  
  51.  
  52. Db.Close
  53.  
  54.  
Jan 13 '07 #1
Share this Question
Share on Google+
11 Replies


ADezii
Expert 5K+
P: 8,597
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?

Expand|Select|Wrap|Line Numbers
  1. Public Sub PutInMonthlyRecords()
  2.  
  3.     Dim sql As String
  4.     Dim Db As DAO.Database
  5.     Dim rs As DAO.Recordset
  6.     Dim f As Form
  7.     Dim MthYr As String
  8.  
  9.     Set f = Forms!frmQpi
  10.  
  11.     MthYr = f("txtMthYr")
  12.  
  13.     sql = "SELECT * FROM [tblQpiMonthly] WHERE (([tblQpiMonthly].[Input MthYr] = 'MthYr' ));"
  14.  
  15.     Set Db = CurrentDb()
  16.     Set rs = Db.OpenRecordset(sql)
  17.  
  18.  
  19.         If rs.RecordCount = 0 Then
  20.             If (f!txtTotalPF) = 0 Then
  21.                 Exit Sub
  22.             End If
  23.  
  24.             rs.AddNew
  25.                 rs![Input MthYr] = f("txtMthYr")
  26.                 rs![Monthly TotalPF] = f("txtTotalPF")
  27.                 rs![Monthly Rejection] = f("txtTotalAvoid")
  28.                 rs![Monthly TotalAvoid] = f("txtRejection")
  29.  
  30.             rs.Update
  31.  
  32.       Else
  33.             If (f!txtTotalPF) = 0 Then
  34.                 rs.Delete
  35.  
  36.                 Exit Sub
  37.               End If
  38.  
  39.  
  40.             rs.Delete
  41.             rs.AddNew
  42.                 rs![Input MthYr] = f("txtMthYr")
  43.                 rs![Monthly TotalPF] = f("txtTotalPF")
  44.                 rs![Monthly Rejection] = f("txtTotalAvoid")
  45.                 rs![Monthly TotalAvoid] = f("txtRejection")
  46.  
  47.             rs.Update
  48.  
  49.         End If
  50.  
  51.  
  52. Db.Close
  53.  
  54.  
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.
Jan 13 '07 #2

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 13 '07 #3

P: 6
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?
Jan 14 '07 #4

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 14 '07 #5

Expert 5K+
P: 8,435
From the original post...
Expand|Select|Wrap|Line Numbers
  1.     Set f = Forms!frmQpi
  2.     MthYr = f("txtMthYr")
  3.     sql = "SELECT * FROM [tblQpiMonthly] WHERE (([tblQpiMonthly].[Input MthYr] = 'MthYr' ));"
  4.  
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?
Jan 15 '07 #6

Expert 5K+
P: 8,435
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.
Jan 15 '07 #7

P: 6
here is the function which i re-edited. the codes that were not affecting the program have been deleted.
Expand|Select|Wrap|Line Numbers
  1. Public Sub PutInMonthlyRecords()
  2.  
  3.     Dim sql As String
  4.     Dim Db As DAO.Database
  5.     Dim rs As DAO.Recordset
  6.     Dim f As Form
  7.  
  8.  
  9.     Set f = Forms!frmQpi
  10.     Set Db = CurrentDb()
  11.  
  12.  
  13.     sql = "SELECT * FROM [tblQpiMonthly];"
  14.  
  15.     Set rs = Db.OpenRecordset(sql)
  16.  
  17.             If (f!txtTotalPF) = 0 Then
  18.                Exit Sub
  19.             End If
  20.  
  21.             If (f!txtTotalPF) = 0 Then
  22.                   Exit Sub
  23.             End If
  24.  
  25.             If (f!txtTotalPF) = 0 Then
  26.                  Exit Sub
  27.              End If
  28.  
  29.                 rs.AddNew
  30.  
  31.                 rs![Input MthYr] = f("txtMthYr")
  32.                 rs![Monthly TotalPF] = f("txtTotalPF")
  33.                 rs![Monthly Rejection] = f("txtTotalAvoid")
  34.                 rs![Monthly TotalAvoid] = f("txtRejection")
  35.  
  36.             rs.Update
  37.  
  38.  
  39. Db.Close
  40.  
  41. End Sub
  42.  
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.
Attached Images
File Type: jpg table.JPG (38.2 KB, 298 views)
Jan 15 '07 #8

P: 6
the form looks this way (see attachment0
Jan 15 '07 #9

P: 6
the form looks this way (see attachment)
Attached Images
File Type: jpg form1.JPG (31.4 KB, 232 views)
Jan 15 '07 #10

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 15 '07 #11

P: 6
here is the code for the statement from this:
Expand|Select|Wrap|Line Numbers
  1. rs.AddNew
  2.  
  3.                 rs![Input MthYr] = f("txtMthYr")
  4.                 rs![Monthly TotalPF] = f("txtTotalPF")
  5.                 rs![Monthly Rejection] = f("txtTotalAvoid")
  6.                 rs![Monthly TotalAvoid] = f("txtRejection")
  7.  
  8.                   rs.Update
  9.  
to this:
Expand|Select|Wrap|Line Numbers
  1. While Not rs.EOF
  2.                  If Not rs.NoMatch Then
  3.                    Exit Sub
  4.  
  5.                 rs.AddNew
  6.  
  7.                 rs![Input MthYr] = f("txtMthYr")
  8.                 rs![Monthly TotalPF] = f("txtTotalPF")
  9.                 rs![Monthly Rejection] = f("txtTotalAvoid")
  10.                 rs![Monthly TotalAvoid] = f("txtRejection")
  11.  
  12.                   rs.Update
  13.               Next
  14.                   rs.MoveNext
  15.              Wend
  16.  
Jan 15 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.