Import csv error - Record is deleted

Greetings,
I have a button that first deletes all records from 5 tables, then imports data using csv files.  I get the runtime error 3167 - Record is deleted that points to the Me.Requery line in the code.

The data are in the tables as they should be, but the controls all show #deleted#

The odd thing is, if I just bypass the error (click END) and rerun this, it all works OK.
Private Sub cmdImportMDB_Click()
'Turn off the warnings
DoCmd.SetWarnings False
'Provide data overwrite warning
response = MsgBox("Are you sure you want to OVERWRITE the existing data?", vbOKCancel, "Import Species List")
If response = 1 Then
DoCmd.RunSQL "DELETE FROM WetForm"
DoCmd.RunSQL "DELETE FROM WetVeg"
DoCmd.RunSQL "DELETE FROM WetHyd"
DoCmd.RunSQL "DELETE FROM WetSoil"
DoCmd.RunSQL "DELETE FROM SpeciesLookup"
DoCmd.TransferText acImportDelim, , "WetForm", "C:\WetForm\WetFormExport.csv", True
DoCmd.TransferText acImportDelim, , "WetVeg", "C:\WetForm\WetVegExport.csv", True
DoCmd.TransferText acImportDelim, , "WetHyd", "C:\WetForm\WetHydExport.csv", True
DoCmd.TransferText acImportDelim, , "WetSoil", "C:\WetForm\WetSoilExport.csv", True
DoCmd.TransferText acImportDelim, , "SpeciesLookup", "C:\WetForm\SpeciesLookupExport.csv", True
MsgBox "WetForm, WetVeg, WetHyd, WetSoil, SpeciesLookUp successfully Imported", vbOKOnly
Else
End If
Me.Requery ' This is where the error points to
DoCmd.SetWarnings True
End Sub

Open in new window

phmurphyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
<Yes I do need to use the csv import.> Do you have to use this form for importing the CSV?

or we can trap the error then just clear it..


'error handling codes

on error goto ImportEH





ImportEH:
      if err.number=3167  then
         err.clear
         resume next
     end if

0
 
Rey Obrero (Capricorn1)Commented:
try moving the Me.Requery after the MsgBox



MsgBox "WetForm, WetVeg, WetHyd, WetSoil, SpeciesLookUp successfully Imported", vbOKOnly

Me.Requery '

Else
End If

DoCmd.SetWarnings True
End Sub
0
 
phmurphyAuthor Commented:
I get the same error.  It is odd that just by running it a second time it works OK, and all subsequent runs are OK too.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Rey Obrero (Capricorn1)Commented:
what is the recordsource of the form?
0
 
phmurphyAuthor Commented:
The table WetForm is the mainform and the other Wet... tables are subforms, and Specieslookup is a lookup table.
0
 
Rey Obrero (Capricorn1)Commented:
try, instead of me.requery

me.recordsource="select * from wetform"

see if it makes a difference
0
 
phmurphyAuthor Commented:
Exact same error.  Same result (no error) when reruning the same routine a second time.
I also tried me.refresh.
Here is what the help info is on the error

Record is deleted. (Error 3167)
You referred to a record that you deleted or that another user in a multiuser environment deleted. Move to another record, and then try the operation again.

Perhaps I need to do something to the tables after I delete all the records?

 
0
 
phmurphyAuthor Commented:
It seems as if the first time I run the routine it puts the tables to "sleep", and when I run it a second time it wakes them up.
0
 
Rey Obrero (Capricorn1)Commented:
do you need to run this importing of new data on this form?

can you tell more of the process you are doing?
0
 
phmurphyAuthor Commented:
Yes I do need to use the csv import.

What I am doing is exporting and importing a csv file that will be used/edited on a PDA.  Using sync software is so glitchy and unreliable I just prefer doing it this way.  The tables are just fine during the import, the form is just having a hard time with it.  As I was saying, if I just ignore the error and run the import a second time, it works fine from then on.  I can export and import multiple times with no error.
0
 
phmurphyAuthor Commented:
Sorry it took so long.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.