Saving to a Table with ADO recordset saves same record twice 1

[polnichek]

I have a save button on my form which opens a recordset and saves the data to the table. If the user clicks on the button twice by mistake it will save the data twice in two separate records. I will actually get two rows in the table with identical information (with different primary key values).
When the user clicks on "Save" the following function is called:
AddToIncidentTable

Function definition:

Sub AddToIncidentTable() 'testing code to add a new record....


Dim cnThisConnect As ADODB.Connection
Dim rst As New ADODB.Recordset
Set cnThisConnect = CurrentProject.Connection
With rst
.Open "tblIncident", cnThisConnect, adOpenKeyset, adLockOptimistic, adCmdTable
.AddNew
!DateOcc = Me.txtDate.Value
!TimeOcc = Me.txtTime.Value
!ReviewTeam = Me.txtRevTeam.Value
!Incident = Me.txtAbbIncDesc.Value 'assign abbreviated description
!IncidentDescription = Me.txtIncidentDesc 'assign long version of incident description
!CommunicationCompleteasof = Me.txtCommComplete.Value
!PeopleIndividual = Me.cboPI.Value
!PeopleOther = Me.cboPO.Value
!EnvironmentInternaltoBuilding = Me.cboEI.Value
!EnvironmentExternaltoBuilding = Me.cboEE.Value
!PropertyWithinBuilding = Me.cboPW.Value
!PopertyExternaltoBuilding = Me.cboPE.Value
!SocialPsychologicalIndividual = Me.cboSPI.Value
!SocialPsychologicalCommunity = Me.cboSPC.Value
!OpinionIndividual = Me.cboOInd.Value
!OpinionCommunity = Me.cboOC.Value
!HighestLevel = bytHighestLevelAtt
.Update
lngIncID = !IncidentID
End With

'lngIncID = .IncidentID
rst.Close
Set rst = Nothing
Debug.Print lngIncID, "test of autonumber capture"


End Sub

I know some of these table attribute names are silly but I am modifying an existing accident tracking system so I am stuck with them. Any help with this would be greatly appreciated.

Polnichek

 

[Remou]

How about disabling the save button once it has been clicked? Alternatively, is there any natural unique field that can be checked, date and time for example?

 

[polnichek]

Yes Thank you Remou for both ideas. I had already tested your solution at the suggestion of another friend of mine and I was about to update the post when I got a notification about your suggestion! I have used the first idea (disabling the command button) and it works just fine. I will investigate the possibility of of using a natural field to see if that is applicable, I could use the field "incidentID" in my database. Is it your suggestion that if there is a value in this field then don't save by using a simple IF statement?

Thanks again,

Polnichek

 

[Remou]

Not saving if there is an IncidentID is certainly an idea, it might also be possible to update if such an ID existed, rather than adding a new record. What I was mainly thinking of is to add a unique index to the table to make sure that a record can never be added twice with the same ID.

 

[polnichek]

Yes I see what you had in mind now. That will defintely work in this application. I could assign the "IncidentID" when it is created by the autonumber field to a variable and if that variable already has a value then I do not save the current record again.

does this make sense?

Polnichek

 

[Remou]

I think to assign the ID to a textbox may be useful both here and later. What I was mainly thinking of is something in the data that just cannot happen twice, which would be a 'natural' key. However, there is no reason for your data to have this, it was just a thought.

 

[polnichek]

And a very good thought, I am going to use that idea actually. Thanks again for your help. This is a great venue for helping people to get unstuck!

Regards,

Polnichek