Enforcing item uniqueness

[Jess]

I have a continuous subform within a form. This continuous subform is used by
my users (CustomerID) to enter a new order (OrderID). An order is composed of
one or multiple items (ItemID). The main form and the subform are joined
trough the CustomerID field. Each record in the continuous subform represents
an ItemID for a given order (OrderID) and a given customer. The main form has
the customer info (CustomerID, address, etc.).

This subform also keeps track of every order entered by my users (OrderID).
My subform is based upon a table with multiple fields, the most important
ones previously cited: CustomerID, OrderID, ItemID.

I do not wish my users (CustomerID) to be ordering the same item (ItemID)
twice for a given Order (OrderID).

I find this not so simple. If I query the table my subform is based on in
the before_update sub, changes/additions will not be in the table: Access has
not saved them yet; I would have to access the "dirty content" of the OrderID
field text property. The fact that my subform is continuous makes it
difficult for me to access the "dirty content" (text property) of the ItemID
combobox –I have to move the focus.

How can I accomplish this? I am pretty sure that somebody has come up with a
good approach. I do not want to reinvent the wheel.

 

[Jack Leach]

If you have a composite key (a primary key based on more than one field), you
can let access take care of this for you... it simply won't allow you to add
a record if CustomerID, OrderID and ItemID all make up the table's primary
key. That's one way, but for many reason, I personally avoid data structures
like this (I'm not a fan of composite keys at all and there's a lot of
complications for using them for this purpose, IMO).


So leaves what you're trying to do... validate the save and cancel if it
doesn't work. Fortunately, I don't think it's as difficult (and I don't
think there's much of another way around). You shouldn't have to change
records in your subform (it will save when you do anyway), you just need to
grab the ID's from the current record and compare them to records in the
records in your table.

Because field updates happen when you leave the control, but the form update
doesn't happen until it saves, you can generally count on being able to use
the plain old value of the control in a form's Before_Update without having
to screw around reading text values (but if you do... Me.Controlname.Setfocus
is usually sufficient).

One way would be to use DLookup (or Elookup) against the table (which may
not be the same recordsource as your form). If it returns Null, there's no
record that matches your criteria...

If Not IsNull(DLookup("fldname", "tblname", _
"([Customer] = " & Me.CustomerID & ") AND " & _
"([OrderID] = " & Me.OrderID & ") AND " & _
"([ItemID] = " & Me.ItemID & ")" Then
MsgBox "Record Exists!"
Cancel = True
End If


If, by chance, you want to compare only records of that subform, you'll need
to use a recordsetclone and findfirst...

Dim rs As DAO.Recordset
Dim stCriteria As String
Set rs = Me.RecordsetClone
stCriteria = "([CompanyID] = " & Me.CompanyID & ") AND " & _
...
...
rs.FindFirst stCriteria
If Not rs.NoMatch Then
MsgBox "Record Exists!"
Cancel = True
End If
rs.Close
Set rs = Nothing



If, by chance, you do need to refer to the text rather than value of the
controls, build a criteria string like so:

Me.CompanyID.SetFocus
stCriteria = ([CompanyID] = " & Me.CompayID.Text
Me.OrderID.SetFocus
strCriteria = stCriteria & ") AND ([OrderID] = " & Me.OrderID.Text
'etc

not much of reinventing the wheel rather than standard procedure.

hth


(note: in the recordsetclone example, the cleanup (rs.Close and Set rs =
Nothing) may not get executed... I forget if Cancel = True runs the rest of
the code or terminiates the procedure...)

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)