duplicate records

[tricky354]

i'm using the follwing code to enter data from 1 form to a table:

Private Sub cmdConfirmQuote_Click()
On Error GoTo Err_cmdConfirmQuote_Click

DoCmd.RunSQL "INSERT INTO inventory_sales (clientId, quoteNumber) VALUES (" & Me.[clientId] & ", " & Me.[quoteNumber] & ")"

Exit_cmdConfirmQuote_Click:
Exit Sub

Err_cmdConfirmQuote_Click:
MsgBox Err.description
Resume Exit_cmdConfirmQuote_Click

End Sub

is there anyway of changing it to check for duplicate records that already exist ( basically records can only be entered form the form to the table once )

also is there anyway of changing the details or removing 'append' message box?

 

[Dennisk]

use the currentdb/Application (depends on your version of access) .Execute

method. one of the parameters is fail on error so any error trapping routine is executed where the error can be tested for duplicates.

this function does exactly what you require

Const CHANGES_NOT_SUCCESSFUL = 3022

Public Function WriteSessionLock(dteDiaryDate As Date) As Boolean
'
' Returns true if write is successful, otherwise false
'
On Error GoTo Err_Handler

WriteSessionLock = False

CurrentDb.Execute "INSERT INTO tblSessionLock (SessionLockDate) VALUES( #" & dteDiaryDate & "#)", dbFailOnError
On Error GoTo 0
WriteSessionLock = True

Exit_Handler:
Exit Function

Err_Handler:
If Err <> CHANGES_NOT_SUCCESSFUL Then
MsgBox Err & " " & Err.Description
End If
Resume Exit_Handler
End Function

 

[tricky354]

where would i..

insert this code?
sorry i'm v.new to all this!

 

[Dennisk]

create a new code module and paste in the sample code.
Amend the code to refere to your table and column name here I am inserting a date into a booking system session lock.

you should have a form for entering data so you can call the function in the new or add button

Private Sub btnAdd_Click()
If Not IsNull(Me!txtDiaryDate) Then
binWriteStatus = WriteSessionLock(Me!txtDiaryDate)
End If
End Sub

 

[tricky354]

i'm trying, but

i keep getting myself confused...

heres where i'm at:
i've created a button to add 'append' the form data into the table and added the following code:

Private Sub cmdConfirmQuote_Click()
Dim WriteSessionLock
On Error GoTo Err_cmdConfirmQuote_Click

If Not IsNull(Me![quoteNumber]) Then
binWriteStatus = WriteSessionLock(Me![quoteNumber])
DoCmd.RunSQL "INSERT INTO inventory_sales (clientId, quoteNumber) VALUES (" & Me.[clientId] & ", " & Me.[quoteNumber] & ")"
End If

Exit_cmdConfirmQuote_Click:
Exit Sub

Err_cmdConfirmQuote_Click:
MsgBox Err.description
Resume Exit_cmdConfirmQuote_Click

End Sub

but really, i'm very confused....
can you show me and example so i can see where i'm going wrong.
sorry for being stupid

 

[Dennisk]

you dim statement should read Dim binWriteStatus as Boolean,
then test if the write has succedded or not if so then insert the rest of the row FOR THAT Quote Number

 

[tricky354]

compile error:

now i get an Compile Error stating the folowing:

Sub or Function not defined:

Private Sub cmdConfirmQuote_Click()
Dim binWriteStatus As Boolean
On Error GoTo Err_cmdConfirmQuote_Click

If Not IsNull(Me![quoteNumber]) Then
binWriteStatus = WriteSessionLock(Me![quoteNumber])
DoCmd.RunSQL "INSERT INTO inventory_sales (clientId, quoteNumber) VALUES (" & Me.[clientId] & ", " & Me.[quoteNumber] & ")"
End If

Exit_cmdConfirmQuote_Click:
Exit Sub

Err_cmdConfirmQuote_Click:
MsgBox Err.description
Resume Exit_cmdConfirmQuote_Click

End Sub

 

[GaryPanic]

would a simplier idea work (just butting in on this )
have a yes/no field on the table and once entered have this set the flag to yes - and have your button disabled if me.field=yes/true or whatever
this would allow a 1 time addition ..... ??

might be wrong

 

[Dennisk]

you could also use DCOUNT to count the number of occurrences of your key sould be 1 for already present and 0 for not present.