Full Version: Stop Producing Duplicate Entries In Table Insert
UtterAccess Forums > Microsoft® Access > Access Forms
IanStow
Hi
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.
CODE
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
    Else
      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
tType)"
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"
DoCmd.Restore
End Sub
ipisors
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:
!--c1-->
CODE
If Dcount("fieldname", "tablename", "criteria")=0 then
'code to insert
End If
IanStow
Hi Isaac
Below is a snapshot of TblPayslips. The criteria would have to be all fields apart from the PayslipsID field
Ian
IanStow
Hi Isaac
Have managed to do it thru the form query that I use to display the reports generated (see Below)
Ian
ipisors
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.