Stop Display Of #deleted In Form

Bob,

  That is normal.  But you can do a requery on the form to refresh all the records.

Jim.

As Jim indicated, that is normal.

When a form is loaded with a recordset, the number of records in that recordset becomes locked until the form is requeried, at which time any additions are added and deleted records are removed.  Assuming that you have opened the form with a recordset type = Dynaset, then the values in those displayed records will change if they are changed elsewhere in the application, although this will not be instantaneous.

If you, or someone else deletes a record from elsewhere in your application which is displayed in your forms recordset, then the #Deleted value will show up in the form until it the form is closed, or the recordset is requeried.  If this happens frequently, you might consider adding a button in your form header which would allow you to simply requery the form, which will remove the delete record.  If you are going to do this, then you might want to save a pointer to the record which currently has the focus, so that after the requery, you can set the focus back to that record.

HTH
Dale

Have you tried using a Form.Refresh in the VBA code?

Refresh will not remove deleted records or add records which have been added to the database which meet the criteria of the current recordset.  Refresh will only force the update of values displayed in the records which are already a part of the forms recordset.

Hi Experts,

Thanks for the comments.

Dale,

It looks to me that the 'solution' is to do a Requery and as you have suggested and capture pointer to the record which currently has the focus. so that I can go back to it.

Can you advise me how to set / use the pointer?

Thanks,
Bob C.

Bob,

   You would save the ID of the current record, requery, then jump back.  Looks something like this:

   strSearch = "[ID] = " & Me![ID]
   Me.Requery
         
   'Return to the saved record ID
   Me.RecordsetClone.FindFirst strSearch
   Me.Bookmark = Me.RecordsetClone.Bookmark

  If you want to requery a continuous form and end up in the same place (i.e. your on the 5th record from the top and you want to be on the 5th record from the top after the requery), then it gets a little more complicated.

  But this will bring you back to the same record.

Jim.

It looks to me that the 'solution' is to do a Requery and

A better solution is to not delete records this way.  Why are you deleting anything other than the current record?  You should never be updating/deleting records that are part of the bound form's RecordSource via queries.

To requery while remaining on the same record, take a look at http://www.devhut.net/2012/10/19/ms-access-vba-requery-a-form-while-remaining-on-the-same-record/ for a reusable function.

You should never be updating/deleting records that are part of the bound form's RecordSource via queries.

 Why? and what about other users?   Either way, you end up with #deleted unless you requery.

Jim.

Hi Experts,

Thanks for the additional comments.

A little background might help.  I didn't provide it originally as I thought that the solution would be quite simple.  Obviously its not.

As an example.  I have a Member Table.  It contains three Member records for:
- Father.
- Son.
- Daughter.

Each Member record has an Address Id field that links to the corresponding field in an Address Table.

The tables are populated by loading the data from an Excel File.  The Address data in the Excel File for each Member, although for the same Address is not entered the same.  This results in three Address records for the same address two of which are incorrect.

My Address Reconciliation Form allows the User to select the Good Address and a Bad Address.  By clicking on Button its Event reads all Address Id entries in the Member Table for the Bad Address, replaces it with the Good Address Id and then deletes the Bad Address record.  This is where the #Delete info comes from.

Based on this scenario I don't believe there is an issue with multiple User access.

I'm going to take a mor detailed look at your comments and will get back to you all in due course.

Thanks,
Bob C.

Just delete the Bad Address record via its ID using the RecordsetClone as shown above.

Hi Experts,

I actually didn't end up using these solutions at this time however I have retained the information for future us.

I ended up using a totally different approach.

Thanks,
Bob C.

which is (in brief), then select that comment as the solution.

Thanks,
Jim.