473,320 Members | 1,828 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

imrosie
222 100+
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
19 16895
puppydogbuddy
1,923 Expert 1GB
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
222 100+
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
1,923 Expert 1GB
Place the Me.Refresh after the DoCmd.RunSql. You currently have it before the delete is executed.
May 22 '07 #4
imrosie
222 100+
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
1,923 Expert 1GB
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
1,923 Expert 1GB
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
222 100+
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
1,923 Expert 1GB
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
222 100+
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
1,923 Expert 1GB
Rosie,

I will review and get back to you by tomorrow.
May 23 '07 #11
imrosie
222 100+
Rosie,

I will review and get back to you by tomorrow.
thanks, I appreciate that. take care.
May 23 '07 #12
tdw
206 100+
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
222 100+
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
1,923 Expert 1GB
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
206 100+
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
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
5,390 Expert Mod 4TB
@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
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
32,556 Expert Mod 16PB
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

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

Similar topics

10
by: Bo Rasmussen | last post by:
Hi, I have a problem : I have a form with some buttons. When one of these buttons is pressed a new URL with some parameters to e.g. delete something from a database. The problem is that when the...
2
by: Doron | last post by:
Hi all I need to Delete a user from a database open with user that do not have permision to do so I have created a function that will create new workspace with UserName and Password of one that...
17
by: Jim Little | last post by:
Hello, I'm driving myself crazy either because I'm missing something about ASP.NET, or what I'm trying to do simply can't be done. First, I am not using session variables to track state. My...
7
by: Juan Romero | last post by:
Hey guys, please HELP I am going nuts with the datagrid control. I cannot get the damn control to refresh. I am using soap to get information from a web service. I have an XML writer output...
0
by: reloader | last post by:
Hi all, I would like to ask you about combobox refershing. My code is: <title>none</title> <body> <table border="1" width="100%" cellspacing="0" cellpadding="2"> <% dim m_DB
6
by: polocar | last post by:
Hi, I'm writing a program in Visual C# 2005 Professional Edition. This program connects to a SQL Server 2005 database called "Generations" (in which there is only one table, called...
3
by: autospanker | last post by:
Ladies and Gentleman, I have been having this problem that has been driving me insane. I have a website that when viewed in Firefox first, the content in the body area is pushed down. Then when...
5
by: Randy | last post by:
I have button that deletes items from a SQL datatable. The items are displayed for the user in listbox, which is bound to the dataset. The code works just fine insofar as it deletes the correct...
1
by: ll | last post by:
Hi all, I've inherited a site and am currently looking to redesign a page that displays a table of course curriculum, with each row representing a "Topic" of the curriculum. There is a courseID...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.