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!
19 16895
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
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
Place the Me.Refresh after the DoCmd.RunSql. You currently have it before the delete is executed.
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
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.
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
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
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
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
Rosie,
I will review and get back to you by tomorrow.
Rosie,
I will review and get back to you by tomorrow.
thanks, I appreciate that. take care.
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?
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
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). -
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
-
Me.Requery ‘replaces the GoTo NewRec statement
-
If Me.Dirty = True Then
-
Me.Dirty = False
-
End If
-
DoCmd.SetWarnings True
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)
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
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.
sorry , i did not note time , its just i have some tricky ideas to solve access problems , wanted to share with people
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 ;-)
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
| |