Prevent duplication of certain field

[HighLightUK]

Now from the title this sounds easy, but what I would like to do is this:

1) When the user enters a value in my JobRef textbox on my form, it checks to see whether there is another identical jobref in the underlying table for the current value in the COrder field (these fields are both in the same table).

2)If there is, the user is informed this is a duplicate value and the JobRef textbox is highlighted for overwriting.

This occurs as a COrder (effectively a works order) is issued on a weekly basis by the client and all the JobRefs reset every week.

Below is an example of the set-up:

COrder JobRef
C-0001 7DF-01
C-0001 7DF-02
C-0001 E-01 etc
C-0002 7DF-01
C-0002 E-01 etc

The field JobRef is a text field and currently uses a multiple mask approach. The user enters a number and the mask is added by the system.

So to clarify, if the user enters another 01 (and its a 7DF Job, so the value stored in the table is 7DF-01), against C-0001, the system will flag this as duplicated.

Any ideas on how to achieve this?

 

[BSman]

In the OnUpdate property of the control where JobRef is entered (txtJobRef), put something like the following code. I've assumed that the COrder value is contained in a control called txtCOrder.

Dim lngCount as Long
lngCount = DCount("[JobRef]","[Jobs]","[COrder] = '" & me.txtCOrder & "' AND [JobRef] = '" & me.txtJobRef & "'&quot
if lngCount > 0 then
msgbox "That job reference number has already been used for that COrder. Please enter the correct JobRef."
' blank out the value in txtJobRef
me.txtJobRef = ""
' the next line puts the cursor back in txtJobRef.
' if the cursor goes to the next field, set it on the
' field just before txtJobRef (in the tab order)
' so it will end up in txtJobRef when you go back to form
me.txtJobRef.SetFocus
exit Sub
End If


I included the statement to blank out the value in the txtJobRef control. That insures that the user cannot just press <Enter> and possibly keep the same value.