find duplicate using 3 fields and cancel current form entry

[tomk]

I am modifying the assets template in ACC 2007 and need to do the same check except I have 3 values that identify a duplicate record. I was able to concatenate the 3 values in txtbox uniqueidcheck for the current entry form, and then do a dlookup of the database in txtbox query_unique. I'm also using the dlookup to bring the ID (Primarykey) into another txtbox called IDLookup.



What I want to do is:
1) After they enter the third value, it checks for duplicates.
2) Prompt to change to the existing record and refresh the whole form from the existing record.



Private Sub Condition_AfterUpdate()
Dim intResponse As Integer
Dim stlinkcriteria As String
Dim QID As Integer
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
stlinkcriteria = "[ID]=" & QID
On Error GoTo Err_Handler
Forms![asset details]!Query_Unique.Requery
Forms![asset details]!IDLookup.Requery
QID = Forms![asset details]!IDLookup
If Forms![asset details]!Query_Unique = Forms![asset details]! UniqueIDCheck Then
intResponse = MsgBox("The RMA, Condition and Item already exists." & Chr(13) & "Do you want to switch to the other record?", vbQuestion + vbYesNo)
If intRespose = vbYes Then
DoCmd.SetWarnings False
Me.Undo
rsc.FindFirst stlinkcriteria
Me.Bookmark = rsc.Bookmark
'rsc.SearchForRecord , , acFirst, "[RMA Number] =" & Forms![asset details]!Manufacturer & "AND Condition =" & _
Forms![asset details]!Condition & "AND ITEM=" & Forms![asset details]!Item

DoCmd.SetWarnings True

End If
Set rsc = Nothing
End If


Exit Sub

Err_Handler:
MsgBox Err.Description
Exit Sub
End Sub

[Access_Blaster]

Hi Tom,

Is the RMA field always going to hold a unique value? non duplicates?

[tomk]

Hi, the RMA and the other 2 will all hold duplicates. What is unique is the concatenation of the 3 fields. The application is to manage returns to a warehouse. The tables will be published on sharepoint and the warehouse will enter the RMA, Condition, and Item when they physically receive it. The order admin will also enter the RMA, condition, Item when it is entered into our OM system. The issue is that we need to be sure that both of these dataentry are not duplicated since we do not know who will be the first to enter the record..either the physical or system receipt.

[tomk]

Let me try simplifying my request.

My only problem is getting it to cancel the current edited recored (The WHOLE record) and then finding the existing record and bringing that into the form for editing.

The Table is called Assets
The entry form is called "Asset Details" but is triggered from another form called "Asset Lists"
Control source for "Asset Details points to the Assets table.
The ID is the inique default primary key assigned by Access.

stlinkcriteria = Str(Forms![asset details]!IDLookup)
Q: Should this variable work with the rsc.findfirst below?

Me.Undo Q: Doesn't this rollback the current record open for editing? If not, can someone point me to something I can experiment with?
rsc.FindFirst "[ID]=" & stlinkcriteria Borrowed from another recent example in this forum.
Me.Bookmark = rsc.Bookmark Q: Not sure what this does

I tried this, both as written and then just searching for the ID based on sllinkcriteria.
'rsc.SearchForRecord , , acFirst, "[RMA Number] =" & Forms![asset details]!Manufacturer & "AND Condition =" & _
Forms![asset details]!Condition & "AND ITEM=" & Forms![asset details]!Item

Set rsc = Nothing