By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,564 Members | 834 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,564 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+
15 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)
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]
Me.Refresh
'Execute the SQL Statement is next
DoCmd.RunSQL mysql
DoCmd.SetWarnings True
Call Form_Current
End If
End If
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
.
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.
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:
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
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:
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
DoCmd.GoToRecord , ,acNewRec
If Me.Dirty = True Then
Me.Dirty = False
End If
DoCmd.SetWarnings True
Call Form_Current
End If
End If
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:
Private Sub cmdPrev_Click()
On Error GoTo Err_cmdPrev_Click

DoCmd.GoToRecord , , acPrevious

Forms!frmimageInventory!frmimagesubform![imgPicture].Height = OrigHght
Forms!frmimageInventory!frmimagesubform![imgPicture].Width = OrigWdth

Exit_cmdPrev_Click:
Exit Sub

Err_cmdPrev_Click:
MsgBox Err.Description
Resume Exit_cmdPrev_Click

End Sub

For Next:
Private Sub cmdNext_Click()
On Error GoTo Err_cmdNext_Click
DoCmd.GoToRecord , , acNext

Forms!frmimageInventory!frmimagesubform![imgPicture].Height = OrigHght
Forms!frmimageInventory!frmimagesubform![imgPicture].Width = OrigWdth

Exit_cmdNext_Click:
Exit Sub

Err_cmdNext_Click:
MsgBox Err.Description
Resume Exit_cmdNext_Click
End Sub

for the Form Subform (called within the main imageInventory):
Private Sub Form_Current()
On Error GoTo HandleErr

Me.RecordsetClone.MoveLast

If (Me.RecordsetClone.RecordCount) < 1.5 Then
Me.cmdNext.Enabled = False
Me.cmdPrev.Enabled = False

ElseIf Me.RecordsetClone.RecordCount = Me.CurrentRecord Then
Me.cmdNext.Enabled = False
Me.cmdPrev.Enabled = True

ElseIf Me.CurrentRecord = 1 Then
Me.cmdNext.Enabled = True
Me.cmdPrev.Enabled = False

Else
Me.cmdNext.Enabled = True
Me.cmdPrev.Enabled = True

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

Post your reply

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