Append Query

[Steven M. Britton]

I have a button that runs an append query, it adds to an
Orders table (Similar to Northwind), and the OrderID
(AutoNumber) is the primary Key. But if the user clicks
the button twice or three times it adds the records again
and again. I can't change the primary key though because
it is related to the Order_Detail via that ID.

What should I do to prevent adding duplicate data?

This is what I currently have, but it doesn't work at
all... I am new at this to any help would be great!!!

Private Sub btnAppendOrders_Click()
On Error GoTo Err_btnAppendOrders_Click
'Check for duplicates
Form.Filter = tblOrders.CCNumber <>
qryAppendOrders.CCNumber
Form.FilterOn = True
If Form.RecordSource.RecordCount = 0 Then

'Append Orders
Dim stDocName As String

stDocName = "qryAppendOrders"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End If

End Sub

 

[Jonathan Parminter]

-----Original Message-----
I have a button that runs an append query, it adds to an
Orders table (Similar to Northwind), and the OrderID
(AutoNumber) is the primary Key. But if the user clicks
the button twice or three times it adds the records again
and again. I can't change the primary key though because
it is related to the Order_Detail via that ID.

What should I do to prevent adding duplicate data?

This is what I currently have, but it doesn't work at
all... I am new at this to any help would be great!!!

Private Sub btnAppendOrders_Click()
On Error GoTo Err_btnAppendOrders_Click
'Check for duplicates
Form.Filter = tblOrders.CCNumber <>
qryAppendOrders.CCNumber
Form.FilterOn = True
If Form.RecordSource.RecordCount = 0 Then

'Append Orders
Dim stDocName As String

stDocName = "qryAppendOrders"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End If

End Sub
.

Hi Steven,

you might like to disable the button after the append
(will have to move focus to another control before line
cmdAppend.enabled=false). Then enable once the user has
done something that justifies this - for example the
OnCurrent event as the user moves from one record to
another.

Luck
Jonathan

 

[Treebeard]

Private Sub btnAppendOrders_Click()
If IsNull DLookup ("[CCNumber]", "tblOrders", "[CCNumber] = " &
Me.CCNumber) then

' run the insert query

Else

' display error message indicating
' that the record already exists

End If