By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,346 Members | 1,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,346 IT Pros & Developers. It's quick & easy.

How to refresh a table after row deletes within form (in VB)

imrosie
100+
P: 222
Hello,

I've got a problem....I have a form based on a table with contains several controls (text and combo boxes). In the form there is a command button where some data in the controls can be deleted. If someone goes through the record selector, some of the controls may show #deleted. This is a problem. If I close the form and reopen, the issue goes away.

How can I immediately refresh the table so this doesn't occur? Each of my controls has an 'on_click' event, so those are already used. Thanks, I"m a newbie and need some help!
May 22 '07 #1
Share this Question
Share on Google+
19 Replies

puppydogbuddy
Expert 100+
P: 1,923
Hello,

I've got a problem....I have a form based on a table with contains several controls (text and combo boxes). In the form there is a command button where some data in the controls can be deleted. If someone goes through the record selector, some of the controls may show #deleted. This is a problem. If I close the form and reopen, the issue goes away.

How can I immediately refresh the table so this doesn't occur? Each of my controls has an 'on_click' event, so those are already used. Thanks, I"m a newbie and need some help!
On the next line of code after you execute the delete record command, place the following code:
Me.Refresh
May 22 '07 #2

imrosie
100+
P: 222
On the next line of code after you execute the delete record command, place the following code:
Me.Refresh
Hello puppydogbuddy,

I did what you said (see code)
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdErasePic_Click()
  2. If Not IsNull([imageFile]) Then
  3.     If MsgBox("This image will be permanantly removed, Are you sure?", vbYesNo + vbQuestion) = vbYes Then
  4.     Me!frmimagesubform.Form![imgPicture].Picture = ""
  5.     Me![imageFile] = Null
  6.     SysCmd acSysCmdClearStatus
  7.     DoCmd.SetWarnings False
  8.     Dim mysql As String  'Declare the Variable by assigning SQL String to the variable mysql
  9.  mysql = "DELETE * FROM imageInventory Where imageInventory.imageID = " & Me![imageID]
  10. Me.Refresh
  11.  'Execute the SQL Statement is next
  12. DoCmd.RunSQL mysql
  13. DoCmd.SetWarnings True
  14.     Call Form_Current
  15.     End If
  16.   End If
  17. End Sub
What happens is immediately each of the control that contained data, changes to #Deleted and then, if I click the next tab to go to another record, I get a message "Type Mismatch"....I have to click next (& previous) to begin using the record selector again......

It doesn't seem to immediately refresh after delete. Help. I even tried requery, but that still leaves that 'deleted' record in the list as #Deleted
May 22 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
Place the Me.Refresh after the DoCmd.RunSql. You currently have it before the delete is executed.
May 22 '07 #4

imrosie
100+
P: 222
Place the Me.Refresh after the DoCmd.RunSql. You currently have it before the delete is executed.
Hello, I failed to mentionl. At first I placed it right after the 'DoCmd.RunSql' and it didn't work there. So I moved it up right after the delete, which is obviously not working either.

I'm at a loss for how to correct it.

The error of Me.Refresh is in yellow, after placing it after the 'DoCmd.RunSql'. It won't recompile. Any other suggestions? I'll continue to try and figure this out. thanks
May 22 '07 #5

puppydogbuddy
Expert 100+
P: 1,923
Hello, I failed to mentionl. At first I placed it right after the 'DoCmd.RunSql' and it didn't work there. So I moved it up right after the delete, which is obviously not working either.

I'm at a loss for how to correct it.

The error of Me.Refresh is in yellow, after placing it after the 'DoCmd.RunSql'. It won't recompile. Any other suggestions? I'll continue to try and figure this out. thanks
Try using the dirty property of the form (as shown below) in lieu of the Me.Refresh
.
Expand|Select|Wrap|Line Numbers
  1. If Me.Dirty = True Then
  2. Me.Dirty = False
  3. End If
However, place the above code on the line after you set the warnings back on.
May 22 '07 #6

puppydogbuddy
Expert 100+
P: 1,923
Try using the dirty property of the form (as shown below) in lieu of the Me.Refresh
.
If Me.Dirty = True Then
Me.Dirty = False
End If

However, place the above code on the line after you set the warnings back on.
Ok, I think I know what the problem is. After you delete a record, that record remains current until you navigate to a new record. So before you execute the Dirty statement I gave you above, you have to move off the deleted record. Try moving to the next record as shown:

1. Execute your Sql to delete the record
2. Move off the deleted record to the next record >>>use a DoCmd.GoToRecord , ,acNewRec
3. execute the If me.dirty statement or the Me.Refresh statement
4. continue with your code as is
May 22 '07 #7

imrosie
100+
P: 222
Ok, I think I know what the problem is. After you delete a record, that record remains current until you navigate to a new record. So before you execute the Dirty statement I gave you above, you have to move off the deleted record. Try moving to the next record as shown:

1. Execute your Sql to delete the record
2. Move off the deleted record to the next record >>>use a DoCmd.GoToRecord , ,acNewRec
3. execute the If me.dirty statement or the Me.Refresh statement
4. continue with your code as is

Hello Puppydogbuddy,

Thanks for your perserverance...I did exactly what you said as follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdErasePic_Click()
  2. If Not IsNull([imageFile]) Then
  3.     If MsgBox("This image will be permanantly removed, Are you sure?", vbYesNo + vbQuestion) = vbYes Then
  4.     Me!frmimagesubform.Form![imgPicture].Picture = ""
  5.     Me![imageFile] = Null
  6.     SysCmd acSysCmdClearStatus
  7.     DoCmd.SetWarnings False
  8.     Dim mysql As String  'Declare the Variable by assigning SQL String to the variable mysql
  9.  mysql = "DELETE * FROM imageInventory Where imageInventory.imageID = " & Me![imageID]
  10.  'Execute the SQL Statement is next
  11. DoCmd.RunSQL mysql
  12. If Me.Dirty = True Then
  13. Me.Dirty = False
  14. End If
  15. DoCmd.SetWarnings True
  16.     Call Form_Current
  17.     End If
  18.   End If
  19. End Sub
However, the bound controls (not the unbound) still show the #Deleted and gives me a runtime error 3167, with message that record is deleted. Any other suggestions? thanks again.
Rosie
May 23 '07 #8

puppydogbuddy
Expert 100+
P: 1,923
Hello Puppydogbuddy,

Thanks for your perserverance...I did exactly what you said as follows:
Private Sub cmdErasePic_Click()
If Not IsNull([imageFile]) Then
If MsgBox("This image will be permanantly removed, Are you sure?", vbYesNo + vbQuestion) = vbYes Then
Me!frmimagesubform.Form![imgPicture].Picture = ""
Me![imageFile] = Null
SysCmd acSysCmdClearStatus
DoCmd.SetWarnings False
Dim mysql As String 'Declare the Variable by assigning SQL String to the variable mysql
mysql = "DELETE * FROM imageInventory Where imageInventory.imageID = " & Me![imageID]
'Execute the SQL Statement is next
DoCmd.RunSQL mysql
If Me.Dirty = True Then
Me.Dirty = False
End If
DoCmd.SetWarnings True
Call Form_Current
End If
End If
End Sub

However, the bound controls (not the unbound) still show the #Deleted and gives me a runtime error 3167, with message that record is deleted. Any other suggestions? thanks again.
Rosie
Rosie,
Your code above does not contain the code I prescribed in step #2 (see below):
2. Move off the deleted record to the next record >>>use a DoCmd.GoToRecord , ,acNewRec
May 23 '07 #9

imrosie
100+
P: 222
Rosie,
Your code above does not contain the code I prescribed in step #2 (see below):
2. Move off the deleted record to the next record >>>use a DoCmd.GoToRecord , ,acNewRec
You are so right...I didn't put that in as you said. I just recompiled the following:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdErasePic_Click()
  2. If Not IsNull([imageFile]) Then
  3.     If MsgBox("This image will be permanantly removed, Are you sure?", vbYesNo + vbQuestion) = vbYes Then
  4.     Me!frmimagesubform.Form![imgPicture].Picture = ""
  5.     Me![imageFile] = Null
  6.     SysCmd acSysCmdClearStatus
  7.     DoCmd.SetWarnings False
  8.     Dim mysql As String  'Declare the Variable by assigning SQL String to the variable mysql
  9.  mysql = "DELETE * FROM imageInventory Where imageInventory.imageID = " & Me![imageID]
  10.  'Execute the SQL Statement is next
  11. DoCmd.RunSQL mysql
  12. DoCmd.GoToRecord , ,acNewRec
  13. If Me.Dirty = True Then
  14. Me.Dirty = False
  15. End If
  16. DoCmd.SetWarnings True
  17.     Call Form_Current
  18.     End If
  19.   End If
  20. End Sub
I'm getting a new error msg (2105) stating "you can't go to specified record " and yes, the #Deletes are still in the bound controls. I suspect that because I have a subform which displays the images, the positioning for 'next and previous' command buttons get thrown off. Once the I put in your suggestions, the errors occur and afterwards none of the images will display again when I click previous or next...the positioning (using recordsetclone) is all thrown off. I have to close and reopen form to get next and previous working. I'm a newbie kind of in over my head. thanks in advance for your help. Here's more of my code:

For Previous:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPrev_Click()
  2. On Error GoTo Err_cmdPrev_Click
  3.  
  4.     DoCmd.GoToRecord , , acPrevious
  5.  
  6. Forms!frmimageInventory!frmimagesubform![imgPicture].Height = OrigHght
  7. Forms!frmimageInventory!frmimagesubform![imgPicture].Width = OrigWdth
  8.  
  9. Exit_cmdPrev_Click:
  10.     Exit Sub
  11.  
  12. Err_cmdPrev_Click:
  13.     MsgBox Err.Description
  14.     Resume Exit_cmdPrev_Click
  15.  
  16. End Sub
For Next:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdNext_Click()
  2. On Error GoTo Err_cmdNext_Click
  3.     DoCmd.GoToRecord , , acNext
  4.  
  5. Forms!frmimageInventory!frmimagesubform![imgPicture].Height = OrigHght
  6. Forms!frmimageInventory!frmimagesubform![imgPicture].Width = OrigWdth
  7.  
  8. Exit_cmdNext_Click:
  9.     Exit Sub
  10.  
  11. Err_cmdNext_Click:
  12.     MsgBox Err.Description
  13.     Resume Exit_cmdNext_Click
  14. End Sub
for the Form Subform (called within the main imageInventory):
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. On Error GoTo HandleErr
  3.  
  4.   Me.RecordsetClone.MoveLast
  5.  
  6.   If (Me.RecordsetClone.RecordCount) < 1.5 Then
  7.   Me.cmdNext.Enabled = False
  8.   Me.cmdPrev.Enabled = False
  9.  
  10.   ElseIf Me.RecordsetClone.RecordCount = Me.CurrentRecord Then
  11.   Me.cmdNext.Enabled = False
  12.   Me.cmdPrev.Enabled = True
  13.  
  14.   ElseIf Me.CurrentRecord = 1 Then
  15.   Me.cmdNext.Enabled = True
  16.   Me.cmdPrev.Enabled = False
  17.  
  18.   Else
  19.   Me.cmdNext.Enabled = True
  20.   Me.cmdPrev.Enabled = True
  21.  
  22.   End If
Rosie
May 23 '07 #10

puppydogbuddy
Expert 100+
P: 1,923
Rosie,

I will review and get back to you by tomorrow.
May 23 '07 #11

imrosie
100+
P: 222
Rosie,

I will review and get back to you by tomorrow.
thanks, I appreciate that. take care.
May 23 '07 #12

tdw
100+
P: 206
tdw
thanks, I appreciate that. take care.
Just curious...could you run a macro at the end of your deletion code that closes and immediately reopens the form?
May 23 '07 #13

imrosie
100+
P: 222
Just curious...could you run a macro at the end of your deletion code that closes and immediately reopens the form?
I'm new and haven't dabbled in the macro area yet....How would I do that? thanks
May 24 '07 #14

puppydogbuddy
Expert 100+
P: 1,923
Hi Rosie,

I did some research on this. Here’s what the book “Fixing Access Annoyances by Phil Mitchell and Evan Callahan says:

The # tags like #deleted that show up in fields are actually a good thing-they tell you more than if Access just left the fields blank. Documentation is hard to find because the Help search engine filters out special characters like #. Here is some more detailed information based on MS KB 209132. The #deleted error generally (but not always) indicates that the record referred to has been deleted since your view of the data was last updated. All that needs to be done is to requery the recordset and the ghost record should go away.

There, you have it. The only question is t hat, depending on whether the delete command is executed from the main form or from the subform, and depending on whether the deleted records are displayed on the form or subform, changes the appropriate requery syntax.

Based on the information provided, I am assuming that the delete command is being executed from the subform where the records are displayed. In that case the syntax of Me.Requery should be appropriate.

So eliminate the DoCmd.GoToRecord ,, acNewRec from the code you provided me yesterday and insert Me.Requery just after the DoCmd.RunSQL mysql code line as shown. If this does not work, then comment out the two SetWarnings statements, recompile and rexecute your code and let me know what warning messages are (if any).
Expand|Select|Wrap|Line Numbers
  1.  DoCmd.SetWarnings False
  2. Dim mysql As String 'Declare the Variable by assigning SQL String to the variable mysql
  3. mysql = "DELETE * FROM imageInventory Where imageInventory.imageID = " & Me![imageID]
  4. 'Execute the SQL Statement is next
  5. DoCmd.RunSQL mysql
  6. Me.Requery ‘replaces the GoTo NewRec statement
  7. If Me.Dirty = True Then
  8. Me.Dirty = False
  9. End If
  10. DoCmd.SetWarnings True
May 24 '07 #15

tdw
100+
P: 206
tdw
I'm new and haven't dabbled in the macro area yet....How would I do that? thanks
Well, first of all if you can get the Requery to work, I'm sure that is better than running a close & reopen macro (I'm no expert, just had a thought really :-) ) but if you need to try the macro, here is a way:

-First, Create a New Macro by selecting "Macros" from the database window, and clicking "New".
-In the design view of the macro, in the first line under the heading "Actions" type or select the command Close, then fill out the appropriate info in the bottom left of the screen (i.e. Object Type: Form, Object Name: 'name of your form'), etc).
-In the next line under "Actions" type or select the command "OpenForm" and again fill out the appropriate info at the bottom of the screen. If you need help with the info at the bottom let me know.

Then once you have created the macro and given it a name, you can call it from your form in a couple ways:

1. In an event (i.e. On Click, After Update, etc.) you can just enter the name of the macro (or select it from the drop-down list.

2. In the VBA code of your form, you can put in the line "DoCmd.RunMacro MacroName" (where MacroName is the name of your macro)
May 24 '07 #16

P: 2
Emrosie,
there is only one solution for your issue , and i personally use it , drop form in another blank form as sub form, so if you run delete query on sub form ,all subform fields will be #delete, then you run subformname.requery all fields will be reset again and you can process to pending query or even go to another record
reason , you can not move while record deleted without save , and #deleted record will never be saved so you need to run queries from main form to sub form and thats the only way, contact me i will send sample to you
Dec 19 '19 #17

gits
Expert Mod 5K+
P: 5,390
@ahmedaboelez

you might realize that the original thread is posted like 12 years ago - so probably no further reply from the OP will happen.
Dec 19 '19 #18

P: 2
sorry , i did not note time , its just i have some tricky ideas to solve access problems , wanted to share with people
Dec 20 '19 #19

NeoPa
Expert Mod 15k+
P: 31,770
While there is unlikely to be a reply, your post may still have value as these threads are still discovered anew even after so many years. As Gits says though, don't hold your breath waiting for a reply from the OP ;-)
Dec 22 '19 #20

Post your reply

Sign in to post your reply or Sign up for a free account.