how to prevent duplicate records?

[ariel81]

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?

Public Sub PutInMonthlyRecords()
 
    Dim sql As String
    Dim Db As DAO.Database
    Dim rs As DAO.Recordset
    Dim f As Form
    Dim MthYr As String
 
    Set f = Forms!frmQpi
 
    MthYr = f("txtMthYr")
 
    sql = "SELECT * FROM [tblQpiMonthly] WHERE (([tblQpiMonthly].[Input MthYr] = 'MthYr' ));"
 
    Set Db = CurrentDb()
    Set rs = Db.OpenRecordset(sql)
 
 
        If rs.RecordCount = 0 Then
            If (f!txtTotalPF) = 0 Then
                Exit Sub
            End If
 
            rs.AddNew
                rs![Input MthYr] = f("txtMthYr")
                rs![Monthly TotalPF] = f("txtTotalPF")
                rs![Monthly Rejection] = f("txtTotalAvoid")
                rs![Monthly TotalAvoid] = f("txtRejection")
 
            rs.Update
 
      Else
            If (f!txtTotalPF) = 0 Then
                rs.Delete
 
                Exit Sub
              End If
 
 
            rs.Delete
            rs.AddNew
                rs![Input MthYr] = f("txtMthYr")
                rs![Monthly TotalPF] = f("txtTotalPF")
                rs![Monthly Rejection] = f("txtTotalAvoid")
                rs![Monthly TotalAvoid] = f("txtRejection")
 
            rs.Update
 
        End If
 
 
Db.Close
 
 

[ADezii]

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?

Public Sub PutInMonthlyRecords()
 
    Dim sql As String
    Dim Db As DAO.Database
    Dim rs As DAO.Recordset
    Dim f As Form
    Dim MthYr As String
 
    Set f = Forms!frmQpi
 
    MthYr = f("txtMthYr")
 
    sql = "SELECT * FROM [tblQpiMonthly] WHERE (([tblQpiMonthly].[Input MthYr] = 'MthYr' ));"
 
    Set Db = CurrentDb()
    Set rs = Db.OpenRecordset(sql)
 
 
        If rs.RecordCount = 0 Then
            If (f!txtTotalPF) = 0 Then
                Exit Sub
            End If
 
            rs.AddNew
                rs![Input MthYr] = f("txtMthYr")
                rs![Monthly TotalPF] = f("txtTotalPF")
                rs![Monthly Rejection] = f("txtTotalAvoid")
                rs![Monthly TotalAvoid] = f("txtRejection")
 
            rs.Update
 
      Else
            If (f!txtTotalPF) = 0 Then
                rs.Delete
 
                Exit Sub
              End If
 
 
            rs.Delete
            rs.AddNew
                rs![Input MthYr] = f("txtMthYr")
                rs![Monthly TotalPF] = f("txtTotalPF")
                rs![Monthly Rejection] = f("txtTotalAvoid")
                rs![Monthly TotalAvoid] = f("txtRejection")
 
            rs.Update
 
        End If
 
 
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.

[NeoPa]

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.

[ariel81]

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?

[NeoPa]

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.

[Killer42]

From the original post...

    Set f = Forms!frmQpi
    MthYr = f("txtMthYr")
    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?

[Killer42]

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.

[ariel81]

here is the function which i re-edited. the codes that were not affecting the program have been deleted.

Public Sub PutInMonthlyRecords()
 
    Dim sql As String
    Dim Db As DAO.Database
    Dim rs As DAO.Recordset
    Dim f As Form
 
 
    Set f = Forms!frmQpi
    Set Db = CurrentDb()
 
 
    sql = "SELECT * FROM [tblQpiMonthly];"
 
    Set rs = Db.OpenRecordset(sql)
 
            If (f!txtTotalPF) = 0 Then
               Exit Sub
            End If
 
            If (f!txtTotalPF) = 0 Then
                  Exit Sub
            End If
 
            If (f!txtTotalPF) = 0 Then
                 Exit Sub
             End If
 
                rs.AddNew
 
                rs![Input MthYr] = f("txtMthYr")
                rs![Monthly TotalPF] = f("txtTotalPF")
                rs![Monthly Rejection] = f("txtTotalAvoid")
                rs![Monthly TotalAvoid] = f("txtRejection")
 
            rs.Update
 
 
Db.Close
 
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.

[ariel81]

the form looks this way (see attachment0

[ariel81]

the form looks this way (see attachment)

[NeoPa]

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.

[ariel81]

here is the code for the statement from this:

rs.AddNew
 
                rs![Input MthYr] = f("txtMthYr")
                rs![Monthly TotalPF] = f("txtTotalPF")
                rs![Monthly Rejection] = f("txtTotalAvoid")
                rs![Monthly TotalAvoid] = f("txtRejection")
 
                  rs.Update
 

to this:

While Not rs.EOF
                 If Not rs.NoMatch Then
                   Exit Sub
 
                rs.AddNew
 
                rs![Input MthYr] = f("txtMthYr")
                rs![Monthly TotalPF] = f("txtTotalPF")
                rs![Monthly Rejection] = f("txtTotalAvoid")
                rs![Monthly TotalAvoid] = f("txtRejection")
 
                  rs.Update
              Next
                  rs.MoveNext
             Wend