Delete a group of records in VB won't work

  • Thread starter Thread starter Tim Crowley via AccessMonster.com
  • Start date Start date
T

Tim Crowley via AccessMonster.com

I have a waste to energy program I'm converting from Foxpro 2.5 to Access
2002. The history data file has several hundred thousand records, so I am
writing the data initially to a temp file and when completed to the history
file using a command button (Save/Print). Writing the record to the history
file works ok but, when I try to delete the record in the temp file the
record isn't deleted, doesn't even ask to confirm delete. In my test in the
immediate window in VB it worked on the first try but wouldn't work again
after I attempted to run the code from my app.

The code is:

CurrentDb.Execute "DELETE * from tbl_msw_temp where isnull(in_process)",
dbFailOnError

[in_process] field is blank when the record is completed

If I delete just the current record after save function using:

RunCommand acCmdDeleteRecord

it works.

Unfortunately I have a couple of scenarios when the routine must delete
more than the current record.

I've tried several different in_process syntax's (ie.)
in_process=""
in_process=" "
in_process = null
Etc.

To no avail.

Any suggestions?

Thanks
TIM
 
Hi Alex

I tried the change 'in_process is null' but to no avail

do you think it has anything to do with my access setup since it did work
once in the immediate window before deciding to never work again?

Tim
 
Hi Tim,
so what is in in_process field actually? null or something else?
if null - then
CurrentDb.Execute "DELETE * from tbl_msw_temp where in_process is
null",dbFailOnError
should work
 
Hi Alex, me again

The syntax of my code is exactly as you had previously suggested.

CurrentDb.Execute "DELETE * from tbl_msw_temp where in_process is null",
dbFailOnError

The in_process field just has a text "x" inserted while I'm processing the
transaction. When the truck weighs out on the outgoing scale I change the
in_process field to blank (ie. " ") to mark for deletion. Pretty simple
delete coding I would think but unfortunately this is one of those day's
when my computer hates me.

TMC
 
Hi Tim,
if you update it to " " - then you have also to filter by:
in_process = " "
so check how you update field and make corresponding filter
 
Back
Top