Prev: filter and unbound field
Next: Preventing Duplicates in a Continuous Form Bound to N:N JoinTable
From: David W. Fenton on 14 Jul 2010 17:26
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/
From: David W. Fenton on 14 Jul 2010 19:21
Banana <Banana(a)Republic.com> wrote in
news:4C3E31B3.4070508(a)Republic.com:

> On 7/14/10 2:26 PM, David W. Fenton wrote:
>> 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).
>
> 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 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.

>> Now, I can't eliminate already-linked Customers from the combo
>> box, because then the combo box for existing records will be
>> blank.
>
> 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/dev/Continuous+Forms--Combo+Box
> +with+Variable+Row+Source

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/
From: Jon Lewis on 15 Jul 2010 10:51
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(a)dfenton.com.invalid> wrote in message
news:Xns9DB5C4E13E3B4f99a49ed1d0c49c5bbb2(a)74.209.136.91...
> Banana <Banana(a)Republic.com> wrote in
> news:4C3E31B3.4070508(a)Republic.com:
>
>> On 7/14/10 2:26 PM, David W. Fenton wrote:
>>> 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).
>>
>> 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 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.
>
>>> Now, I can't eliminate already-linked Customers from the combo
>>> box, because then the combo box for existing records will be
>>> blank.
>>
>> 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/dev/Continuous+Forms--Combo+Box
>> +with+Variable+Row+Source
>
> 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/


From: David W. Fenton on 15 Jul 2010 19:01
John Spencer <JSPENCER(a)Hilltop.umbc> wrote in
news:i1n113$anp$1(a)news.eternal-september.org:

> 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.

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/
From: David W. Fenton on 15 Jul 2010 19:03
"Jon Lewis" <jon.lewis(a)cutthespambtinternet.com> wrote in
news:nu-dnfPvKufmvaLRnZ2dnUVZ7oOdnZ2d(a)bt.com:

> 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?

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/
 |  Next  |  Last
Pages: 1 2 3
Prev: filter and unbound field
Next: Preventing Duplicates in a Continuous Form Bound to N:N JoinTable