How to speed up VBA code to store everything correctly

[aman]

Hi guys

I have developed a system that my client is using now. There is some problem coming up in storing the scanned barcodes in the database. Actually i have written the following code in the lost focus event of textbox:

Private Sub Text17_LostFocus()
sSQL = "INSERT INTO inputtable ([date],location,department,Barcode,Signature) VALUES (#" & Format(Me.Text4.Value, "mm-dd-yy") & "#, '" & Me.Combo12.Value & "','" & Me.Combo15.Value & "', '" & Me.Text17.Value & "','No')"
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
sSQL = "Delete from inputtable where Barcode=' '"
DoCmd.RunSQL sSQL
end sub

The user scans the barcodes in text17 very quickly so in that case some barcodes does not come up in the table and sometimes the barcodes get stored in incorrect order. I think speed is the issue. Is there any option I can put in to speed up vba code processing.

Regards
Aman

 

[namliam]

This is your time drain:
sSQL = "Delete from inputtable where Barcode=' '"

Skip the delete action untill the form is closed and you will (likely) see a considerable increase in performance.

 

[aman]

Hi namiliam

Thanks for ur reply.. Could you please tell me if multiple users access this form and will scan the different barcodes simultaneously in the textbox then will the system work fine or i need to make some changes in the system to make it work in multiuser environment.

Cheers
AMan

 

[namliam]

should work, order gets jumbled though... unless you use something with username loged into the database or something.

 

[aman]

Thanks a lot for your help.

AMan