Update after deleting a record has no effect

[fugifox]

I have a Customers table containing all my Customers personal data and a Form frmCustomers for handling them. At the Form there's a listbox displaying all the Clients (fullnames) for quick access.

I delete the current record in my Form by calling the VB command

 DoCmd.RunCommand acCmdDeleteRecord

The problem is that after the record is being deleted it is still displayed in the list box.
I've tried adding

Me.Requery
Me.Refresh

after the delete command but with no effect.

The only way to refresh the records is to manually select the Update option from the Records menu at the main Toolbar.
I even tried the calling from VB the above menu option
DoCmd.DoMenuItem acFormBar, acRecordsMenu, (don't remember the rest by heart)
which I suppose should have the same result with clicking to the menu but to my disappointment the list box keeps denying to reflect tha changes through VB.

Is this some kind of Access bug?

 

[pbaldy]

Have you tried re-querying the listbox?

Me.ListboxName.Requery

 

[fugifox]

Have you tried re-querying the listbox?

Me.ListboxName.Requery

Yes, forgot to mention it.
No result.

 

[DCrake]

Simple Software Solutions

The root of your problem is the acDeleteRecord command. Access deletes the curerntly selected record (what you want) but won't phyically delete it untill you either close the table or move to a different record. This is to allow for the Undo command.

I would suggest you perform the delete slightly different

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From Table Where Id =" YourId
DoEvents
Me.Listbox.Requery
DoCmd.SetWarnings True

CodeMaster:

 

[fugifox]

thanks a lot dcrake

 

[fugifox]

dcrake the solution you provided worked like a charm.

But I'm now facing the same problem with the Save command.
What's more although you can perform the delete action with a delete query
I can not think of any corresponding query for the save command.
Maybe properly constructing an Append query would do the trick
but seems rather cumbersome.

Any suggestion about?

 

[pbaldy]

An append query would be the corresponding query, but if the form is bound you don't have to run a query. You can force the save with:

DoCmd.RunCommand acCmdSaveRecord

 

[boblarson]

An append query would be the corresponding query, but if the form is bound you don't have to run a query. You can force the save with:

DoCmd.RunCommand acCmdSaveRecord

And another way would be:

If Me.Dirty Then Me.Dirty = False

 

[fugifox]

And another way would be:

If Me.Dirty Then Me.Dirty = False

I didn't quite understand this way.
Just setting the dirty property to false forces the form to be saved and show the changes in the listbox?

 

[pbaldy]

Either method will just force the save; you'd still have to requery the listbox.

 

[fugifox]

Either method will just force the save; you'd still have to requery the listbox.

Ok, thnx a lot for the help.

I would like to ask this again because it sounds a little weird to me.

The line of code

If Me.Dirty Then Me.Dirty = False

is equal to

DoCmd.RunCommand acCmdSaveRecord

?

 

[pbaldy]

Basically yes. Obviously that method tests first, but

Me.Dirty = False

will force a save.

 

[boblarson]

As Paul says, the If Me.Dirty line checks to see if there is anything to save first. If not, it won't try to save. If you run the other it will save regardless. Not a big deal normally, so you can use whatever method works for you (easier to remember).

 

[fugifox]

thanks a lot guys!