How to avoid double entry of Data

[Frank Situmorang]

Hello,

I create a button to combine the member notes from several pastors.

There is a possibility of hit the button twice, What this the VBA in order
if it found abosolutly identical record, it will not be accpted in this
update query:

This is my updatequery:
Private Sub MemberNotesUpdate_Click()
On Error GoTo Err_MemberNotesUpdate_Click

DoCmd.SetWarnings False 'Turn messages back OFF
DoCmd.OpenQuery "Qry_UpdateCatatan", acNormal, acEdit
'Add your other query here
DoCmd.SetWarnings True 'Turn messages back ON


Exit_MemberNotesUpdate_Click:
Exit Sub

Err_MemberNotesUpdate_Click:
MsgBox Err.Description
Resume Exit_MemberNotesUpdate_Click
End Sub

Thanks in advance

 

[Tom van Stiphout]

On Tue, 12 Jan 2010 21:00:01 -0800, Frank Situmorang

This can best be prevented by placing a unique index on the
combination of fields that is required to be unique.

-Tom.
Microsoft Access MVP

 

[Stuart McCall]

Hello,

I create a button to combine the member notes from several pastors.

There is a possibility of hit the button twice, What this the VBA in order
if it found abosolutly identical record, it will not be accpted in this
update query:

This is my updatequery:
Private Sub MemberNotesUpdate_Click()
On Error GoTo Err_MemberNotesUpdate_Click

DoCmd.SetWarnings False 'Turn messages back OFF
DoCmd.OpenQuery "Qry_UpdateCatatan", acNormal, acEdit
'Add your other query here
DoCmd.SetWarnings True 'Turn messages back ON


Exit_MemberNotesUpdate_Click:
Exit Sub

Err_MemberNotesUpdate_Click:
MsgBox Err.Description
Resume Exit_MemberNotesUpdate_Click
End Sub

Thanks in advance

So you want to disable double clicking on a command button? If so, in the
button's DblClick event procedure, write:

Cancel = True

Job done.

 

[John W. Vinson]

Hello,

I create a button to combine the member notes from several pastors.

There is a possibility of hit the button twice, What this the VBA in order
if it found abosolutly identical record, it will not be accpted in this
update query:

Absolutely identical to what? What other record are you comparing it to?

 

[FPS, Romney]

Stuart,
Does your suggestion actually work on your copy of Access? It doesn't on
mine - Access 97. For example, double-clicking a command button with code in
the single-click event which opens a form results in the form being opened,
but then the second click is applied to wherever the mouse cursor happens to
be resting on the screen. If there happens to be another command button at
that area of the screen then it's like deliberately clicking that button as
soon as the form opens.
Mark

 

[Stuart McCall]

Stuart,
Does your suggestion actually work on your copy of Access? It doesn't on
mine - Access 97. For example, double-clicking a command button with code
in
the single-click event which opens a form results in the form being
opened,
but then the second click is applied to wherever the mouse cursor happens
to
be resting on the screen. If there happens to be another command button at
that area of the screen then it's like deliberately clicking that button
as
soon as the form opens.
Mark

Yes this technique works for me. Tested in A2000 & A2003. I don't have a
copy of A97 any more so can't test it for you, sorry.

You could try DoCmd.CancelEvent instead. I don't know if that will work,
though.

 

[FPS, Romney]

Stuart,
It's interesting, but not only does DoCmd.CancelEvent not work in my version
of Access 97, but Cancel = True does not work on our Access 2003 computers
running an Access 2000 FE. As long as there is code behind the (single) On
Click event, that will take precedence over anything applied to the On Dbl
Click event. The first click of a double-click will be interpreted as a
single click and any code attached to single-click event will run. The
second click of a double-click will then be interpreted as a second
single-click. The On Dbl Click event seems to work only when there is
nothing applied to the On Click event.
Mark

 

[Stuart McCall]

Stuart,
It's interesting, but not only does DoCmd.CancelEvent not work in my
version
of Access 97, but Cancel = True does not work on our Access 2003 computers
running an Access 2000 FE. As long as there is code behind the (single) On
Click event, that will take precedence over anything applied to the On Dbl
Click event. The first click of a double-click will be interpreted as a
single click and any code attached to single-click event will run. The
second click of a double-click will then be interpreted as a second
single-click. The On Dbl Click event seems to work only when there is
nothing applied to the On Click event.
Mark

<snip>

I discussed this very topic only recently with MVP Dirk GoldGar. He ran some
tests and here is what he found (I'm sure he won't be offended by my
re-posting):

1. If there is no DblClick event procedure for the button, double-clicking
the button makes the Click event fire twice.

2. If there is a DblClick event procedure, and that procedure doesn't cancel
the event, double-clicking the button causes this sequence of events to
fire:

Click
DblClick
Click

I already knew this, in principle, because of prior work I've done
investigating the difference between the event sequence when double-clicking
labels versus command buttons. The special event sequence for command
buttons is documented in the help file.

3. If there is a DblClick event procedure, and that procedure *does* cancel
the event (by setting the Cancel argument to True), double-clicking the
button causes this sequence of events to fire:

Click
DblClick

As you see, the Click event does not fire the second time.

Therefore, it isn't necessary to set up a timer, or compare the time between
clicks, to suppress the double-clicking of a command button. I think it was
very clever of MS to set it up that way.

 

[FPS, Romney]

Stuart,
If you have time, please try the following and let me know the results, ok?
On any form, add a command button and an unbound text field (mine was named
"Text15".
For the (single) On Click event place the following:

Private Sub Command14_Click()
Me!Text15 = "single click"
MsgBox "single click"
End Sub

For the On Dbl Click event place the following:

Private Sub Command14_DblClick(Cancel As Integer)
Me!Text15 = "double click"
MsgBox "double click"
End Sub

With both On Click and On Dbl Click events in place, are you ever able to
get Text15 to read "double click", or the MsgBox to display "double click"?
I cannot.

Also, in regards to double-clicking in general, when I double-click a
command button that, say, opens a form specified in the (single) On Click
event, that form doesn't open twice. Nor in the above example does the
MsgBox open twice. If I change the code to contantenate some value in
Text15, double-clicking does not increase the value of the text box any more
than a single click would do.
e.g.,
Private Sub Command14_Click()
Me!Text15 = Me!Text15 & "single click"
End Sub

-- click the button once and the text field displays "single click".
-- without removing "single click" from the text box, double-click on the
button. The text box now reads: "single clicksingle click"
[as if the button was single-clicked]
-- not "single clicksingle clicksingle click"

Bottom line (at least for my system) is that for command buttons, the On Dbl
Click event only works if there is NOT an On Click event for that button.
And ...
If there IS an On Click event and you double-click the button, it will act
as a single click EXCEPT if the code opens a new form. In that case, the
second click of that double-click will be applied to wherever the mouse is
positioned over the area of that new form.