Delete Records Problem

[SteveL]

Please see the attachment.

Note that the sub-form on the right-hand side of the form that opens when you open the database has 4 line records. Obviously the sub-form is a continuous form. What I am having trouble with is this... If I delete the record that is red, (the top record), I get a message that says that someone else is using the database which is not true because I am the only one using it. Then I click [OK] on the message and the records display with #Deleted in all of the fields.

The delete method is doing exactly what it should be doing... delete all 4 records. But how do I get rid of the message that says another user is using the database, and, how do I make the #Deleted records go away? (A Refresh All does the trick but I don't want to have to click the Refresh All button.

Note, for developmental purposes should you decide to try to help me out... to get the 4 records back in the form just select "Equipment" from the Category drop-down and select "Some kind of line item" from the 2nd drop-down.

Any help would sure be appreciated.

--Steve

 

[jdraw]

Steve,

Any help would sure be appreciated

.
I think it would be helpful to you and to readers if you could describe WHAT you are trying to do in plain English. Then use that description to identify the tables and relationships involved in support of your "business". You have described HOW you have done something---

Note that the sub-form on the right-hand side of the form that opens when you open the database has 4 line records. Obviously the sub-form is a continuous form.....

and it isn't doing what you want. But we all need to start with WHAT, and make sure the scope is complete, then identify alternatives as to How it may be accomplished.

Your relationships window shows no relationships. It isn't clear that your tables are normalized.

 

[CJ_London]

you are getting the error message because you are trying to delete records which are currently being viewed in the form - all Access knows is the record is locked, not that they are locked by you.

I would look at these actions

store the values for your filter
remove the form recordsource
delete the records using your stored values
replace the form recordsource

also consider using the before/after del confirm events

and use docmd.echo to prevent screen changes whilst the above is going on.

 

[SteveL]

JDraw: The database is a actually a stripped down version of the original database due to confidentiality issues. The original is normalized. Plus I'm sorry if I didn't explain the issue well enough.

CJ London: I don't know how to do what you are suggesting.

 

[CJ_London]

 dim JID as Long
 dim  UID as long
 etc
  
 JID=txtJobIDtemp
 UID=txtUnitIDtemp
 etc
  
 me.recordsource=""
 currentdb.execute("DELETE * .... WHERE JobID = " & JID & " AND ...etc
 me.recordsource="tblUnitLineItems"