Prev: filter and unbound field
Next: Preventing Duplicates in a Continuous Form Bound to N:N JoinTable
From: Allen Browne on 15 Jul 2010 22:10
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(a)dfenton.com.invalid> wrote in message
news:Xns9DB6C1E36F458f99a49ed1d0c49c5bbb2(a)74.209.136.90...
> "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/

From: Marshall Barton on 16 Jul 2010 14:28
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
From: David W. Fenton on 16 Jul 2010 14:34
John Spencer <JSPENCER(a)Hilltop.umbc> wrote in
news:i1pl23$hfk$1(a)news.eternal-september.org:

> 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/
From: David W. Fenton on 16 Jul 2010 14:40
"Allen Browne" <AllenBrowne(a)SeeSig.invalid> wrote in
news:u4SdnX8Qsrk6IqLRnZ2dnUVZ_rudnZ2d(a)westnet.com.au:

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

> 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/
From: Karl Hoaglund on 16 Jul 2010 19: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
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: filter and unbound field
Next: Preventing Duplicates in a Continuous Form Bound to N:N JoinTable