Link to home
Create AccountLog in
Avatar of AbacusInfoTech
AbacusInfoTech

asked on

Why does my docmd.mysql statement insert two duplicated records?

The following code snippet is assigned to the onclick event of a button. When the code runs, the inserted record is actually duplicated. It appears twice in my table, exactly the same apart from my AutoNumber. Why is this? I only want my record to be inserted once.
Private Sub btnSubmitIssue_Click()
 
' Runs when the users wants to submit an issue.
' Ensures that all boxes have got data in them.
' Asks the user if he is sure he wants to submit the issue.
' Adds the issue to the database.
' Prepares an email which can be sent to the client.
' Closes the form and returns the user to the main screen.
 
' Check that the boxes contain data.
If IsNull(Me!client) Or Len(Me!client) = 0 Then GoTo clientempty Else
If IsNull(Me!raised) Or Len(Me!raised) = 0 Then GoTo raisedempty Else
If IsNull(Me!raisedby) Or Len(Me!raisedby) = 0 Then GoTo raisedbyempty Else
If IsNull(Me!title) Or Len(Me!title) = 0 Then GoTo titleempty Else
If IsNull(Me!description) Or Len(Me!description) = 0 Then GoTo descriptionempty Else
 
' Ask the user if he is sure he wants to submit the issue.
 
Response = MsgBox("Are you sure you want to submit this case?", vbYesNo, "Warning")
If Response = vbYes Then    ' User chose Yes.
 
' Adds the issue to the database.
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblSupportIssue (client,raised,raisedby,title,description) VALUES ([client],[raised],[raisedby],[title],[description])"
DoCmd.SetWarnings True
 
' Prepares an email which can be sent to the client.
MsgBox "Email Prep"
 
Exit Sub
 
Else    ' User chose No.
MsgBox "The issue has NOT been submitted."
End If
Exit Sub
 
clientempty:
MsgBox "Please enter a value in client."
Exit Sub
 
raisedempty:
MsgBox "Please enter a value in raised."
Exit Sub
 
raisedbyempty:
MsgBox "Please enter a value in raised by."
Exit Sub
 
titleempty:
MsgBox "Please enter a value in title."
Exit Sub
 
descriptionempty:
MsgBox "Please enter a value in description."
Exit Sub
' Closes the form and returns the user to the main screen.
End Sub

Open in new window

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

> When the code runs, the inserted record is actually duplicated.
only if you run that code 2 times. it is not possible that a INSERT ... VALUES() inserts 2 records.
Avatar of AbacusInfoTech
AbacusInfoTech

ASKER

Thanks for your quick response angelIII and I understand what you are saying. My code shows only one INSERT INTO statement. I do not think there is a loop in there. Where do you think the problem lies then?
To prevent an accidental clicking of the SubmitIssue button, perhaps you should disable it immediately (set focus somewhere else first) while the process is completing, then re-enable it at an appropriate time.

mx
i don't see any reasons in that (part of) the code...
Just suggesting an idea out of left field........
 
  Idea 1: Stick a breakpoint in the code to trap the Click, best just before the SQL executes.  You might find that some accidental "double clicking" is running your code twice if you don't have double click event code defined.


   Easy fix, add some code that supports the double click event to do the SQL once or disable the btnSubmitIssue on entry to your code, enable it again at some appropriate time/event.

   Idea 2: Also consider the design of the table you are inserting to. Does your table design (or business rules) allow for duplicate entries ? If you wish to prevent duplicates (ignoring the autonumber ID), you could create another index for the table that prevents such things ever being inserted. You would then need to have some error handling code for when you tried to insert a duplicate (or perhaps look-up what you are trying to insert to see if already exists before actually doing the insert)

  Idea 3: I know it's bad coding technique, but check for any other code that calls btnXXXX_Click() within that module. Your code may be being called from somewhere else you don't expect.  I have seen that before where a programmer has just re-used the event code for an event to call another event's code. Messy and best avoided.

Andrew

Ahhhh, I think angelIII has it... the problem is in my code somewhere. If I choose not to add the record when prompted by my msgbox, one record gets added....

Looking now.
?
"i don't see any reasons in that (part of) the code..."

"one record gets added...."

How?  I don't see that ... ?

mx
let me suggest this code rewrite:
Private Sub btnSubmitIssue_Click()
 
' Runs when the users wants to submit an issue.
' Ensures that all boxes have got data in them.
' Asks the user if he is sure he wants to submit the issue.
' Adds the issue to the database.
' Prepares an email which can be sent to the client.
' Closes the form and returns the user to the main screen.
 
' Check that the boxes contain data.
 
Dim strMessage as string
 
 
If IsNull(Me!client) Or Len(Me!client) = 0 Then 
  strMessage = "Please enter a value in client."
  GoTo ShowMessage
End If
 
If IsNull(Me!raised) Or Len(Me!raised) = 0 Then 
  strMessage = "Please enter a value in raised."
  GoTo ShowMessage
End If
 
If IsNull(Me!raisedby) Or Len(Me!raisedby) = 0 Then 
  strMessage = "Please enter a value in raisedby."
  GoTo ShowMessage
End If
 
If IsNull(Me!title) Or Len(Me!title) = 0 Then 
  strMessage = "Please enter a value in title."
  GoTo ShowMessage
End If
 
If IsNull(Me!description) Or Len(Me!description) = 0 Then 
  strMessage =  "Please enter a value in description."
  GoTo ShowMessage
End If
 
' Ask the user if he is sure he wants to submit the issue.
 
Response = MsgBox("Are you sure you want to submit this case?", vbYesNo, "Warning")
If Response = vbYes Then    ' User chose Yes.
 
  ' Adds the issue to the database.
   DoCmd.SetWarnings False
   DoCmd.RunSQL "INSERT INTO tblSupportIssue (client,raised,raisedby,title,description) VALUES ([client],[raised],[raisedby],[title],[description])"
   DoCmd.SetWarnings True
 
  ' Prepares an email which can be sent to the client.
  strMessage ="Email Prep"
 
Else    ' User chose No.
  strMessage = "The issue has NOT been submitted."
End If
 
ShowMessage:
  MsgBox strMessage  
 
End Sub

Open in new window

I'm interested in the result after the rewrite from AngelIII. It is certainly much tidier than the original, but I  _can_not_  see where the code in the original can insert more than 1 record.

Andrew


Thanks AngelIII.

This is a weird one. Even with your code I had the same problem. I have figured out that the duplicated record is getting added when I CLOSE the form, but there is NO CODE in my OnClose Event.

The DataEntry property on the form was set to YES but I have changed this to NO and still get the same issue.

I am going to clutch at other straws.....
Ditto on the rewrite and ditto on " _can_not_  see where the code in the original can insert more than 1 record."

It would be nice to know why ... that is happening ... short of the button is being clicked twice ..

mx
Behaviour is the same in Access 2000 and 2003....
mx,
   "It would be nice to know why ... that is happening ... short of the button is being clicked twice .."


   Other events or code on the form that we have no knowledge of......

As the originator of the question stated, "Even with your code I had the same problem. I have figured out that the duplicated record is getting added when I CLOSE the form"





Silly Q,
     are the ID's of the inserted adjacent or are we looking at old data lying around ?

This is a really simple and very new form in a new database. I will double check some stuff and upload it if I can't find some interfering code!
In your Insert statement you are using field values in the VALUES() part, not the values from the Controls.  That will just double up the record.  Do you want me to 'spell it out' for you?  You need the five control values Me!Client, Me!Raised, etc.
The ID's are incremented by one.
Database here, fill your boots:

http://www.aitm.co.uk/downloads/database.zip

I dislike the taste of humble pie, and do not want to have any tonight, but am preparing for the eventuality...
GRayL

I think I know what you are getting at and will look into it. My hunch is that the problem lies *elsewhere* because I partially replicated the problem even with the INSERT statement remarked out....
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
It's not the code.
WHoops, you were quicker than me:-)
Pretty fundamental. I probably deserve a slice of humble pie if not the whole thing. Thanks all for your help, points going to angelIII unless there are any objections...
>I probably deserve a slice of humble pie if not the whole thing.
I suggest a "Schwarzwälder Kirschtorte" except I prefer to eat it:

Schwarzwalder-Kirschtorte.jpg
Check out my team of Access Developers...
humblepie.jpg