Link to home
Start Free TrialLog in
Avatar of Sandra Smith
Sandra SmithFlag for United States of America

asked on

Skip record in code if being edited

I have a process in an ACCESS 2003 database the updates certain fields via a module.  However, if a user is editng a record, how can I tell the VBA code to skip this record?

Avatar of mbizup
Flag of Kazakhstan image

Assuming you have a numeric primary key/ID field, add a WHERE clause to the SQL in your update code to exclude the current record:

WHERE ID <> Forms!YourFormName!YourIDField

or if the SQL is within a VBA string:

"UPDATE ..... WHERE ID <> " &  Forms!YourFormName!YourIDField
How are are updating records? Update SQL, (post sql) or recordset.edit/recordset.update method? (Please post module)
This may be tricky if more that one record is being edited...
Typically a DBA might "take down" the db when Bulk Updates are run, to avoid issues like this.

Or perhaps a simple: "On Error Resume Next" in your error handler can work around this.

So just to round out our knowledge of your setup here, can you explain the nature of these updates?

Avatar of Sandra Smith


There is no form, it is a VBA process.  What happens (and I really don't know why the previous programmer did it this way), the code loops through the Profiles table and deletes all the data for the PDF link field.  Then it goes to the scanned doc folder, loops through all 2500 plus (and growing) pdf files, matches up the file name account with the client account and inserts the link to the pdf file this back into the field for the client (honestly, this thing takes aobut 30 minutes and is only getting worse).  However, if someone runs this and another user is editing a record, the whole thing fails.  Problem is at that point, all the pdf links have been deleted! So, until i can get this thing completely re-written, I just need an error trap to simply resume next, but I only want it to resume based on the error that the record is current in use returns.   I have no control over when this process is run so cannot take it off line- that is under the control of the user group.

I would post, but the entire thing is about five pages long, with calls to very messy functions that took me a while to figure out what was going on.

but I only want it to resume based on the error that the record is current in use returns.  

IF you have an error number that is associated with that, you could add an error handler to your code like this:

On Error goto EH

   '  Your Code goes here

Exit Sub  ' (or function)

    if Err.Number = 1234   Then   Resume Next'<--- replace 1234 with the exact error number

End Sub

Open in new window

However, if there is no associated error number, that is not going to work.
Heres some pseudo code, I put @ in front of generalized statements, the others are actual  code.

Do until <some condition>  '(or maybe its a For Each)
  @Gather your information.
  On Error Goto SkipThisOne
  @Write Data line to table  (This is where it crashes)
  @Maybe You do more stuff here if it didn't crash.
  continue loop
  @Maybe you want to handle that error here, like write to text file
End loop

On error goto 0
>The whole thing fails-->Describe how it shows failure.
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry I have not gotten back to this.  Other users can be in editing during the working day.  This process is run whenever the person responsible wants to, there is no schedule and the way days go here, it is difficult to impose a structure.  However,  this was what I was looking for what I used.  Eventually, I want to automate this to run at 4:00 AM so the problem becomes moot.<br /><br />Sandra

But also note that many other things can go wrong (besides Locked records)
What if the db is closed, sleep or hibernating?
What if a 4AM process is missed?
What if the code runs twice?

Finally the issue of your process "deleting" the records first is still a big pitfall.
Again, if the data is deleted and a subsequent process fails, there is n way to get the deleted records back.
(You should investigate a system to "Copy" there records somewhere first)

So you still seem to need rock-solid error handing and possible Rollbacks in place.

There is also the slight chance that you can have a "race event" in that the record it locked after checking, before you lock.
<There is also the slight chance that you can have a "race event" in that the record it locked after checking, before you lock. >
Good point, MrBullwinkle

For your reason and mine (and a few others) I try to avoid automated, unattended processes...

Jeff, yes the deleting is a big problem and one Big reason I am trying to recode this.  Part of the reason I was thinking of an automated process early on the day was to avoid anyone being in the database.  I am going toward having the newly scanned docs in another directory so the process only looks at new scanned docs, not polling every PDF for a new version.  Once the link is saved in the database field, then the doc is moved to its permanent directory, thus reducing to only polling up to 20, rather than a growing population.  but if the users are still in the database ,they could be working on the record that also has a document link being created. But if I can capture the record , I can produce a message box at the end of the process for those that were skipped and the use can manually enter the PDF path if they wish.