Full Version: Stop Producing Duplicate Entries In Table Insert
UtterAccess Forums > Microsoft® Access > Access Forms
I have the following code that (1st Part) checks to see if report exists and if so displays msgbox (which works fine). Is it possible to do the same type of thing for the insert to table part of the code.
Many Thanks Ian.
Private Sub SaveToDiskandClose_Click()
Dim StrFilePath As String
Dim StrSave As String
StrFilePath = "c:\MyCompany\PaySlips\"
StrSave = Me.TxtEmployeeName & " (TY) " & Me.TxtTaxYear & " (TM) " & Me.TxtTaxMonth & ".PDF"
If Len(Dir(StrFilePath & StrSave)) > 0 Then
    If MsgBox(StrFilePath & StrSave & " already exists. Do you want to DELETE and regenerate it", vbYesNo) = vbYes Then
      Kill StrFilePath & StrSave
      Exit Sub
    End If
End If
    DoCmd.OutputTo acOutputReport, "RptEmployeeP45", acFormatPDF, StrFilePath & StrSave
Dim strSQL As String
strSQL = "insert into tblPayslips (TxtEmployeeName,TxtTaxYear,TxtTaxMonth,txttaxmonthstart,txttaxmonthend,Repor
strSQL = strSQL & "values ('" & Me.TxtEmployeeName & "', '" & Me.TxtTaxYear & "', '" & Me.TxtTaxMonth & "',"
strSQL = strSQL & "'" & Me.TxtTaxMonthStart & "', '" & Me.TxtTaxMonthEnd & "', 'P45')"
'MsgBox strSQL ' see what SQL is produced...
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.Close acReport, "RptEmployeeP45"
DoCmd.Close acForm, "FrmP45"
DoCmd.SelectObject acForm, "FrmExpensesEmployeesNew"
End Sub
You would need to decide how a 'duplicate' record is defined. Does every single field value have to be identical in order for it to be a duplicate? Or is there (hopefully) one key or unique field that you could simply do something like:
If Dcount("fieldname", "tablename", "criteria")=0 then
'code to insert
End If
Hi Isaac
Below is a snapshot of TblPayslips. The criteria would have to be all fields apart from the PayslipsID field
Hi Isaac
Have managed to do it thru the form query that I use to display the reports generated (see Below)
Sounds like it's working out, good to hear.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.