I'm trying to do something I normally don't do, and that's allow the
addition of records in a continuous form.

The form is bound to the join table for a many-to-many relationship
that joins inventory and customers. When the form displays as a
subform of the inventory form, the combo box listing customers is
visible, and when displayed as a subform of the customer form, the
combo box listing inventory items is listed.

Now, what I want is to prohibit the user from adding a duplicate
record.

For instance, if you're viewing InventoryID 100 and it's already got
a record in the subform for CustomerID 3000, I don't want the user
to be able to even attempt to add a new record in that subform with
CustomerID 3000. If I allowed the user to attempt that, they get the
nasty duplicate value in index error message (because the primary
key of the join table is the composite key of InventoryID +
CustomerID).

Now, I can't eliminate already-linked Customers from the combo box,
because then the combo box for existing records will be blank.

I also can't use the BeforeUpdate event of the combo box, because
even if I cancel that event, it still causes the Insert events to
start firing, and triggers the error.

I can't use the BeforeInsert event of the form for some reason,
because cancelling the insert doesn't seem to prevent the error
message.

Am I stuck doing what I normally do, and using an unbound dialog
form to do the add, and allowing no additions in the continuous form
itself? That certain would make things substantially easier in terms
of handling the duplicates problem, but it does mean altering the
existing subform and creating a new one.

Maybe someone can spot something wrong in my description that shows
I'm doing it wrong?

(this is, by the way, one of the main reasons why I almost always
avoid allowing record addition in subforms)

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On 7/14/10 2:26 PM, David W. Fenton wrote:
If this is an Access standard error dialog, the only way to trap this is
via Form's OnError event which you can write custom VBA code to check
the DataErr parameter then suppress the default messagebox and show your
own or whatever you prefer.

I recall seeing a sample overlaying the textbox atop the combobox so you
can eliminate the duplicate listing without blanking out the names. I've
not tried that sample, though.

Here's a link that claims to do something similar:
http://metrix.fcny.org/wiki/display/...ble+Row+Source


HTH.

Banana <Banana (AT) Republic (DOT) com> wrote in
news:4C3E31B3.4070508 (AT) Republic (DOT) com:

I thought about that and didn't follow through on it.

OK, just tried putting an error handler in the form's OnError event,
and it doesn't fire even when I force save the record. The default
error message comes before then. But that error message is:

The value in the field or record violates the validation rule for
the record or field...

There are no validation rules anywhere in any of these fields or at
the table level, so I assumed this was referring to the index.
Forcing the save does trigger an error message, the usual one about
duplicate values in the index, but it's not being delivered through
the form's OnError event, since it's not the MsgBox() I'm returning
(which includes both error number and description; the error number
is absent from the error message I'm getting).

OK, I've had enough. If I'd have been able to handle this in the
form's Error event, I would have stuck with it, but since that's not
working (maybe because I'm doing it wrong), I'm going with the much
simpler solution of entirely forbidding additions in the subform.
This will make the code in the subform much simpler and manageable,
which is why I've tended to prohibit it in the past.

I know about that alternative and reject it out of hand. It raises
all sorts of other potential problems with focus, and I'm already
showing/hiding combo boxes depending on which form the subform is
loaded in.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

David,

The code in the form's error event should be something like the following.
I'm not sure what the error number is for your error (3022?). So you may need
to uncomment the Debug.Print DataErr line to get the correct error number.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
'Debug.Print DataErr
If DataErr = 3022 Then
MsgBox "Whoops! Duplicate entered"
Response = acDataErrContinue
Else
Response = acDataErrDisplay
End If
End Sub

I am guessing that your attempt was different from the above since you did not
post your code.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

David W. Fenton wrote:

I have an almost identical situation where trapping DataErr = 3022 in the
Form_Error event as John suggests works fine. There's no Validation rules,
just a composite Primary Key in the table. Are you sure you have not
overlooked a Validation Rule set in the Table Properties (as opposed to
Field Properties) that triggers the error message?

Jon


"David W. Fenton" <XXXusenet (AT) dfenton (DOT) com.invalid> wrote

John Spencer <JSPENCER (AT) Hilltop (DOT) umbc> wrote in
news:i1n113$anp$1 (AT) news (DOT) eternal-september.org:

You're right -- I didn't understand that I had to use the DataErr
parameter rather than just using the default error object.

The problem here is that it's not happening in the right place -- it
only occurs when the record is saved, and I want it to happen as
soon as the user selects the duplicate from the dropdown list.

I just don't think there's any solution to this problem in a
continuous form.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Jon Lewis" <jon.lewis (AT) cutthespambtinternet (DOT) com> wrote in
news:nu-dnfPvKufmvaLRnZ2dnUVZ7oOdnZ2d (AT) bt (DOT) com:

It did turn out that I'd disabled the BeforeUpdate event of one of
the combo box boxes and not the other, and that was triggering the
messages I was getting. When I fixed that, the Error event would
fire, but only when the record was actually saved, which is too late
-- I want it to fire when the value is chosen from the dropdown
list, i.e., in the BeforeUpdate event, but this is not possible, so
far as I can tell.

I've wasted way too much time trying to make this work when I knew
going into it that it just wasn't possible and that the only way is
the way I've done it for the last 15 years, which is to add records
in an unbound popup form.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Hi David

One option might be to change the recordsource of the continuous form to a
query that includes both the subform's table and the customer table. In the
query, you can create a calculated field that has the same expression as the
combo's display column.

Now you can put a text box bound to this expression on top of the combo, and
size it so the combo's drop-down still appears to the right. In the text
box's GotFocus, SetFocus to the combo. The trick is that the combo jumps in
front of the text box only on the selected row.

In this way, you can exclude the client from the combo's RowSource, so the
user can't select it, and yet still display the client on all other rows of
the continuous form (since the text box on top has that info.)

While I've used that technique for other purposes, I doubt I would be so
concerned about the timing of preventing the client, but I recognise that's
a stylistic issue.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"David W. Fenton" <XXXusenet (AT) dfenton (DOT) com.invalid> wrote

If you want immmediate confirmation of the possible duplicate, all you need to
do is to use the after update event of the control(s) involved to search the
table for an existing value.

If Me.NewRecord Then
If DCount("*","NameOfJoinTable","InventoryID=" & txtINventoryID & " AND
CustomerID = " & txtCustomerID)>0 THEN
MsgBox "This Customer already has this item"
'Choose one of the following or do something else.
'Me.Undo 'Undo the entire form
'Me.txtInventory.Undo 'Undo just the control

End if
End If

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

David W. Fenton wrote:

For what little it's worth, I've done the same thing Allen
described for about as long as you've been using the unbound
form approach.

--
Marsh