Canceling adding a new record on a form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David C. Holley - 30 Jul 2005 18:41 GMT

I have a subform that lists guests attached to a reservation using
CONTINUOUS view. Guests are selected using a comboBox. Since a guests
SHOULD NOT be listed twice on a reservation, I want to put something
together where if the guest is selected, the user is advised that the
guest is already attached to the reservation. I can check for wether or
not the guest is already attached easily enough, however I need to
figure out to which event I should attach the code - Form_BeforeInsert,
Form_AfterInsert, comboBox_?.

Reply to this Message

Rick Brandt - 30 Jul 2005 19:20 GMT

> I have a subform that lists guests attached to a reservation using
> CONTINUOUS view. Guests are selected using a comboBox. Since a guests
[quoted text clipped - 4 lines]
> figure out to which event I should attach the code -
> Form_BeforeInsert, Form_AfterInsert, comboBox_?.

BeforeInsert is to soon. That is when the record is first dirtied. It "might"
work if the control that is dirtying the form is the ComboBox.

AfterInsert is too late. No point in waiting until after the record is saved to
tell them they need to change it.

The AfterUpdate of the ComboBox or the BeforeUpdate of the form are both good
choices. You could of course make a unique index at table design level so that
it is impossible to enter such a duplicate. Then you don't need any code at
all.

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

David C. Holley - 31 Jul 2005 01:52 GMT

FYI The final code. I was quite surprised that it was as simple as it was.

Private Sub cboClient_AfterUpdate()

If DCount("lngRecordId", "tblTransferGuests", "lngClientId = " & _
Me.cboClient & " AND lngTransportId = " & Me.lngTransportID) = 1 Then

MsgBox$ ("'" & Me.cboClient.Column(1) & "' has already been attached to
this reservation.")
Cancel = True
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End If

End

>>I have a subform that lists guests attached to a reservation using
>>CONTINUOUS view. Guests are selected using a comboBox. Since a guests
[quoted text clipped - 15 lines]
> it is impossible to enter such a duplicate. Then you don't need any code at
> all.

Reply to this Message

Bas Cost Budde - 31 Jul 2005 14:56 GMT

Cancel=true? There is no Cancel parameter for AfterUpdate. Instead of
DoMenuItem you could to cboClient.Undo (which I'd prefer, if only out of
legibility)

> FYI The final code. I was quite surprised that it was as simple as it was.
>
[quoted text clipped - 10 lines]
>
> End

Signature

Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

Reply to this Message

David C. Holley - 31 Jul 2005 21:27 GMT

Ooopps. Copied the tried the code in the Form_BeforeUpdate event first.

> Cancel=true? There is no Cancel parameter for AfterUpdate. Instead of
> DoMenuItem you could to cboClient.Undo (which I'd prefer, if only out of
[quoted text clipped - 15 lines]
>>
>> End

Reply to this Message

Bas Cost Budde - 30 Jul 2005 19:53 GMT

You say "if the guest is selected", so I'd suggest you use the Click or
AfterUpdate event of the combobox. (I usually use Click because then I
don't have to distinguish between bound and unbound controls)

> I have a subform that lists guests attached to a reservation using
> CONTINUOUS view. Guests are selected using a comboBox. Since a guests
[quoted text clipped - 4 lines]
> figure out to which event I should attach the code - Form_BeforeInsert,
> Form_AfterInsert, comboBox_?.

Signature

Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

Reply to this Message

Rick Brandt - 30 Jul 2005 19:55 GMT

> You say "if the guest is selected", so I'd suggest you use the Click
> or AfterUpdate event of the combobox. (I usually use Click because
> then I don't have to distinguish between bound and unbound controls)

Are you suggesting that AfterUpdate doesn't work with an unbound ComboBox? Or
is there some other difference?

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

Bas Cost Budde - 30 Jul 2005 21:51 GMT

That is what I am suggesting indeed... be noted I decided this quite
some time ago, and never tried AfterUpdate since. Let's test. [fumbles]
No, no difference.

Which leaves me with other questions, more Why-typed. What could be the
use of a Click event for a combobox? It fires along with AfterUpdate.
Could there be some setting where AfterUpdate indeed doesn't fire? As I
suspect I must have seen at least once. I admit I am a little hasty in
generalizing sometimes, but never from zero samples :-)

>>You say "if the guest is selected", so I'd suggest you use the Click
>>or AfterUpdate event of the combobox. (I usually use Click because
>>then I don't have to distinguish between bound and unbound controls)
>
> Are you suggesting that AfterUpdate doesn't work with an unbound ComboBox? Or
> is there some other difference?

Signature

Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

Reply to this Message

Ken Snell [MVP] - 30 Jul 2005 22:56 GMT

Click event allows you to separate the situation where a user types a value
into a combobox instead of selecting it from the dropdown list. You could
use the Click event to identify when a selection is made versus a manual
entry.

Signature

Ken Snell
<MS ACCESS MVP>

> That is what I am suggesting indeed... be noted I decided this quite some
> time ago, and never tried AfterUpdate since. Let's test. [fumbles] No, no
[quoted text clipped - 12 lines]
>> Are you suggesting that AfterUpdate doesn't work with an unbound
>> ComboBox? Or is there some other difference?

Reply to this Message

Bas Cost Budde - 31 Jul 2005 14:56 GMT

I'm afraid that doesn't work. I type in a value and the Click event
fires nevertheless.

> Click event allows you to separate the situation where a user types a value
> into a combobox instead of selecting it from the dropdown list. You could
> use the Click event to identify when a selection is made versus a manual
> entry.

Signature

Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

Reply to this Message

Ken Snell [MVP] - 31 Jul 2005 19:20 GMT

My apologies.... you're correct, of course... AfterUpdate event occurs, and
then the Click event.... well, back to school to relearn what I've been
thinking...

Signature

Ken Snell
<MS ACCESS MVP>

> I'm afraid that doesn't work. I type in a value and the Click event fires
> nevertheless.
[quoted text clipped - 3 lines]
>> could use the Click event to identify when a selection is made versus a
>> manual entry.

Reply to this Message

Bas Cost Budde - 31 Jul 2005 20:55 GMT

Shake hands, me too. You for Click, I for AfterUpdate :-)

> My apologies.... you're correct, of course... AfterUpdate event occurs, and
> then the Click event.... well, back to school to relearn what I've been
> thinking...

Signature

Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

Reply to this Message