Check for Duplicates (1 Viewer)

[mike60smart]

Hi Everyone

I am trying to use the following to check for Duplicates in a Table

The code runs and Prompts to say there is a Duplicate but when I click Ok the
data is inserted.

How would I amend the code so that it cancels the insert?

Any help appreciated.

Dim strWhere As String
        
80      strWhere = "AcedemicYearID = " & Me.AcedemicYearID & " And StudentClassID = " & Me.StudentClassID & ""
        
90      If DCount("*", "tblStudentFeeYearly", strWhere) > 0 Then
100       MsgBox "Sorry, you've already entered fees for this year."
110     Me.Undo
120     End If

 

[cheekybuddha]

In which event does this code run, Mike?

 

[mike60smart]

Hi David

Full Code is as follows:-

Private Sub cmdInsertFees_Click()


10        On Error GoTo cmdInsertFees_Click_Error


20    If IsNull(Me.StudentID) Then
30      MsgBox "Please Enter Student Details before you proceed", vbOKOnly, "Copy Fee"
40      Exit Sub
50      End If


60        Me.Refresh
          
          Dim Db As DAO.Database
          Dim StrSql As String
          Dim StrSqlA As String
          
70       Set Db = CurrentDb
        
      Dim strWhere As String
        
80      strWhere = "AcedemicYearID = " & Me.AcedemicYearID & " And StudentClassID = " & Me.StudentClassID & ""
        
90      If DCount("*", "tblStudentFeeYearly", strWhere) > 0 Then
100       MsgBox "Sorry, you've already entered fees for this year."
110     Me.Undo
120     End If
      
        
        
        
130      StrSql = "INSERT INTO tblStudentFeeYearly ( StudentClassID, FeeTypeID, FeeAmt, AcedemicYearID) " & _
             " SELECT [InsYFee].StudentClassID, [InsYFee].FeeType_ID, [InsYFee].FeeAmount, [InsYFee].AcedemicYearID " & _
             " FROM [InsYFee] " & _
             " WHERE [InsYFee].StudentID= " & Me.StudentID & " AND [InsYFee].AcedemicYearID= " & Me.cboAcademicYear & ";"
140       Debug.Print StrSql
          
150      With Db
160     .Execute StrSql, dbFailOnError
        
170     End With

 

[cheekybuddha]

Change line 120 to: Else

Add line 180: End If

 

[Gasman]

You are not exiting the sub Mike.?

Jeez!

Indenting in a decent manner might have highlighted that?
Obviously not walking through the code either?

 

[mike60smart]

You are not exiting the sub Mike.?

Jeez!

Indenting in a decent manner might have highlighted that?
Obviously not walking through the code either?

Hi Gasman
Is there a Tool to use with Access that does Indenting ?

 

[mike60smart]

Change line 120 to: Else

Add line 180: End If

Hi David

That fixes that yet again

Many thanks

 

[Gasman]

Hi Gasman
Is there a Tool to use with Access that does Indenting ?

Well I indent as I type in the code.
However to clean up code like yours and others, I use Smart Indent, which you can add to Access and use in all your DBs.

Private Sub cmdInsertFees_Click()
    On Error GoTo cmdInsertFees_Click_Error
    If IsNull(Me.StudentID) Then
        MsgBox "Please Enter Student Details before you proceed", vbOKOnly, "Copy Fee"
        Exit Sub
    End If
    Me.Refresh

    Dim Db As DAO.Database
    Dim StrSql As String
    Dim StrSqlA As String
    Set Db = CurrentDb

    Dim strWhere As String
    strWhere = "AcedemicYearID = " & Me.AcedemicYearID & " And StudentClassID = " & Me.StudentClassID & ""
    If DCount("*", "tblStudentFeeYearly", strWhere) > 0 Then
        MsgBox "Sorry, you've already entered fees for this year."
        Me.Undo
    End If
    StrSql = "INSERT INTO tblStudentFeeYearly ( StudentClassID, FeeTypeID, FeeAmt, AcedemicYearID) " & _
           " SELECT [InsYFee].StudentClassID, [InsYFee].FeeType_ID, [InsYFee].FeeAmount, [InsYFee].AcedemicYearID " & _
           " FROM [InsYFee] " & _
           " WHERE [InsYFee].StudentID= " & Me.StudentID & " AND [InsYFee].AcedemicYearID= " & Me.cboAcademicYear & ";"
    Debug.Print StrSql
    With Db
        .Execute StrSql, dbFailOnError
    End With

End Sub

 

[Pat Hartman]

Life is sooooooo much better when your code is indented reliably ---- EXCEPT that Gasman copied the bad code rather than the fixed code. Line numbers and indentation fight with each other. I'm not sure what you are using to number the lines but you might have to make a choice. Line numbers or good formatting.

Another option is to add an Exit sub in the If where you discover the error. Or preferably a GoTo your exit_sub proc that should precede the error proc (which I've pointed out below seems to be MIA)

Also, I prefer to put all the Dims at the top of a procedure. I group all the DAO (or word or Excel or FSO, etc) objects together and the rest I sort alphabetically as I define them. Neatness counts

On Error GoTo cmdInsertFees_Click_Error

This proc seems to be missing. Do you have Option Explicit defined for this module?

 

[Gasman]

EXCEPT that Gasman copied the bad code rather than the fixed code.

Yes Pat, I was only showing the result of Smart Indent.
My solution was different to cheekybuddha's and Mike went with Cheekybuddha's. Also saw no need to indent any amended code, which did not exist anyway? , the solution was merely a note on lines to correct/add ?

 

[jdraw]

Mike,
Do you have MZTools? It can do indenting and line number and much more.

 

[cheekybuddha]

This proc seems to be missing.

Probably comes after line 170 in the pasted code.

The sub as pasted is obviously incomplete as it is missing End Sub, so likely other code as well

 

[Gasman]

Life is sooooooo much better when your code is indented reliably ---- EXCEPT that Gasman copied the bad code rather than the fixed code. Line numbers and indentation fight with each other. I'm not sure what you are using to number the lines but you might have to make a choice. Line numbers or good formatting.

Another option is to add an Exit sub in the If where you discover the error. Or preferably a GoTo your exit_sub proc that should precede the error proc (which I've pointed out below seems to be MIA)

Also, I prefer to put all the Dims at the top of a procedure. I group all the DAO (or word or Excel or FSO, etc) objects together and the rest I sort alphabetically as I define them. Neatness counts


This proc seems to be missing. Do you have Option Explicit defined for this module?

To be fair, these are not Mike's DB's whatsoever, but his clients, so heaven knows what they have constructed.

 

[ebs17]

Dcount and the append query are two accesses to the target table. You can also combine this into one access by building an inconsistency check into the append query, so that only new data records are added and no errors are generated due to duplicates. You can display whether something has been inserted after the Execute call:

MsgBox db.RecordsAffected

 

[mike60smart]

To be fair, these are not Mike's DB's whatsoever, but his clients, so heaven knows what they have constructed.

Many thanks Everyone
I do use MZTools but was not aware that it was able to Indent as well.
Will now investigate

Many thanks yet again.