Preventing Duplicates in a Continuous Form Bound to N:N Join Table

#11

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

Quote:

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

I have in the combobox rowsource a column that tells me if it
already exists, so I don't need to do that.

The proper place to do this is in the BeforeUpdate, so it can be
cancelled before the user leaves the control.

But I couldn't get that to work without one of the default Access
messages popping up.

As with almost anything related to combo boxes in continuous forms
that need to be conditionally populated based on the context, this
just doesn't work. And that's one of my principle reasons why I just
normally don't allow this kind of thing in continuous form.

And it's why I'll be implementing it the easy way that completely
circumvents the problem, as I should have done in the first place!

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

#12

"Allen Browne" <AllenBrowne (AT) SeeSig (DOT) invalid> wrote in
news:u4SdnX8Qsrk6IqLRnZ2dnUVZ_rudnZ2d (AT) westnet (DOT) com.au:

Quote:

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

This is all way more complicated than it needs to be. It's a
workaround for the basic architecture of continuous forms, and
simply not worth doing, in my opinion.

Keep in mind that a popup form gives me all sorts of flexibility
beyond what getting this to work in the continuous form would.

Quote:

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.

I don't believe in allowing a user to select a value that they are
prohibited from storing. In the popup form, I can easily accomplish
this, as it's unbound, and doesn't need to reflect existing data.

My philosophy is to not produce errors that can be avoided, and to
not allow a user to do something they may not be able to complete --
that is, don't offer them an item in the combo box as a choice if
they won't be able to actually use it in the end. While the
workaround you describe above accomplishes this, I still don't like
the fussing about with multiple controls to display the same data,
and the possible issues with refreshing the other side of the join.

I just think it's much cleaner to do adds in a separate form.

I normally even hesitate to allow editing in continuous/datasheet
forms (except for a few very special cases), but in this case the
only other editable fields are a date field and a text field, which
don't have any control issues like a combo box does, so I don't mind
allowing those to be editable (and 99% of the time, they won't be
changed by the after the original record is created).

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

#13

Hi David. I'm not sure why your BeforeUpdate handling didn't work. It seemed
to for me. I developed a quick form in Continuous forms mode (on foods rather
than inventory) which includes a combo box for food type. I have this code
behind the BeforeUpdate event of that combo box, to make sure that no duplicate
food types are entered:

Private Sub Combo2_BeforeUpdate(Cancel As Integer)
If DCount("ItemID", "FoodOrders", "FoodsID = " & Me.Combo2) > 0 Then
MsgBox "duplicate"
Cancel = True
End If
End Sub

It seems to work fine. The error message is presented and the user is returned
to the form in edit mode. S/he sees the pencil in the upper left-hand corner
indicating the record is being updated, but the change to the combo box is
still pending.

Did you set the Cancel parameter value to true, like I did? If so, what type
of other saving errors are getting?

Karl

----------------------------
Karl Hoaglund, MCSD
Microsoft Access Programmer
Nexus Consulting Group, Inc.
http://www.nexuscgi.net

#14

Karl Hoaglund <hoagluk (AT) yahoo (DOT) com> wrote in
news

art1of1.1.kkqZ5yj4RvTQ6Q (AT) ue (DOT) ph:

Quote:

Did you set the Cancel parameter value to true, like I did? If
so, what type of other saving errors are getting?

Of course I set Cancel to true. But it was triggering an Access
error message that I forget.

I've moved on and implemented something else, so I'm not going to
try to puzzle it out at this point. FWIW, I treated the BeforeUpdate
event the way I always do, and never have problems (except with
unbound controls, which is not the case in this instance), so I
can't say what was causing things to not work this time.

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Preventing Duplicates in a Continuous Form Bound to N:N Join Table

comp.databases.ms-access comp.databases.ms-access

Re: Preventing Duplicates in a Continuous Form Bound to N:N Join Table - 07-16-2010 , 01:34 PM

Re: Preventing Duplicates in a Continuous Form Bound to N:N Join Table - 07-16-2010 , 01:40 PM

Re: Preventing Duplicates in a Continuous Form Bound to N:N Join Table - 07-16-2010 , 06:13 PM

Re: Preventing Duplicates in a Continuous Form Bound to N:N Join Table - 07-17-2010 , 02:08 PM