CODEWITHSUNDEEP
sqlms-access
Dre24
Dre24

Reputation: 13

MS Access, new record sometimes creates two records

I have query results in a subform which is manipulated via buttons outside of the subform.

Image of the form:

Image of the form

Pretty much all the 'Add Record' button does is run an INSERT statement and refreshes the form/query. The INSERT statement contains mostly the same information as whats in the previous rows; information that won't change such as DLA_HOLDER_ID, DLA_TYPE_ID, etc. It leaves most other fields blank for entry and uses Date() and Time() for DLA_CREATION_DATE and RECORD_DATE respectively.

When a row is added, the TEMP_STATUS is 'N', once saved it becomes 'S' and if canceled an UPDATE statement is ran deleting rows that satisfy the query results with TEMP_STATUS 'N'. This all works perfectly.

My issue is, sometimes when pressing 'Add Record', two rows are inserted. It might only happen say 5%-10% of the time and occurs at random. When the two rows are added, they are the exact same except for the RECORD_TIME being one second apart. I.e row1 = 2:06:24 PM, row2 = 2:06:25 PM.

Is there a way to fix this? The only way I can think of this happening is by chance the time taken to process the INSERT query, the Time() value could change from say 2:06:24 to 2:06:25 and instead of picking one it inserts both. (Yes I understand it shouldn't work that way, but it's all I can think of.)

EDIT Here is the code for the 'Add Record' button. The button simply cannot be pressed twice as it requires either the 'Save Record' or 'Cancel' buttons to be pressed to re-enable the 'Add Record' button.

Private Sub cmdNewRec_Click()

insert_query = "INSERT INTO DLA_RELATIONSHIP (DLA_HOLDER_ID, DLA_TYPE_ID, DLA_LOCATION_ID, DLA_PARAMETER_ID, DLA_TITLE, DLA_CREATION_DATE, TEMP_STATUS) SELECT DLA_HOLDER_ID, DLA_TYPE_ID, DLA_LOCATION_ID, DLA_PARAMETER_ID, DLA_TITLE, DLA_CREATION_DATE, ('N') AS TEMP_STATUS FROM DLA_RELATIONSHIP WHERE (((DLA_HOLDER_ID)= txt1) And ((DLA_TYPE_ID)= txt2) And ((DLA_LOCATION_ID)=txt6) and ((DLA_PARAMETER_ID)= txt3) And ((DLA_TITLE)=txt4) And ((DLA_CREATION_DATE)=txt5))"

DoCmd.SetWarnings False
DoCmd.RunSQL insert_query
DoCmd.SetWarnings True

Me.Form.refresh

lblNotice1.Caption = "New record present with unsaved changes"

cmdNewRec.Enabled = False
cmdSave.Enabled = True
cmdCancel.Enabled = True

End Sub

Again, keep in mind it occurs at complete random (again leading me to thinking it is the Time() function somehow playing in to this).

Upvotes: 1

Views: 275

Answers (1)

Kateract
Kateract

Reputation: 852

You might try adding TOP 1 to the select on your insert statement:

INSERT INTO DLA_RELATIONSHIP (DLA_HOLDER_ID, DLA_TYPE_ID, DLA_LOCATION_ID, DLA_PARAMETER_ID, DLA_TITLE, DLA_CREATION_DATE, TEMP_STATUS) 
SELECT TOP 1 DLA_HOLDER_ID, DLA_TYPE_ID, DLA_LOCATION_ID, DLA_PARAMETER_ID, DLA_TITLE, DLA_CREATION_DATE, ('N') AS TEMP_STATUS 
FROM DLA_RELATIONSHIP 
WHERE (((DLA_HOLDER_ID)= txt1) And ((DLA_TYPE_ID)= txt2) And ((DLA_LOCATION_ID)=txt6) and ((DLA_PARAMETER_ID)= txt3) And ((DLA_TITLE)=txt4) And ((DLA_CREATION_DATE)=txt5))

If this fixes your issue, then the where condition is returning more than one record for the select, which would get two rows inserted.

However, you probably should be inserting the record directly from the values in your text boxes as opposed to selecting them from the table; that would make the select unnecessary.

Upvotes: 1

Related Questions