Hello,
I have a function that goes through each field in a form and checks if it was changed between itself and the existing recordset. It is used to track changes done in any record when the logged in user (Forms!frmLogin !cboUname.Colum n(4)) is logged in.
The problem I have is when my control loop checks a field that is linked to a table with a deleted record, the function crashes. This situation arises when two forms are opened at the same time and the user deletes a record from one form while the other is open. How do I check if a field says "#Deleted" and then skip it?
Here is my code for easy reference, it crashes on the line - ElseIf frm.Controls(ctrlSource) = rsexist.Fields(ctrlSource) Then
... -
Function fillLastUpdated(ByRef frm As Form, tableName As String, uniqueCtl As TextBox, Optional undoChanges As Boolean = False) As Boolean
-
If frm.Dirty And Not frm.NewRecord Then 'We don't want to insert this stuff into a new record in case the user doesn't want to save changes
-
Dim rsexist As Recordset 'rsexist is the existing record set while the current record is from form frm
-
Dim ctl As Control
-
Dim keyname As String, keyValue As String, ctrlSource As String
-
Dim changes As String
-
Dim fieldChanged As Boolean, messageShown As Boolean
-
-
Set rsexist = CurrentDb.OpenRecordset(tableName, dbReadOnly)
-
keyname = uniqueCtl.ControlSource
-
keyValue = uniqueCtl.Value 'rscurr.Fields(valueSearch)
-
fieldChanged = False
-
messageShown = False
-
changes = ""
-
If Not rsexist.nomatch Then 'Else If we don't have a match, then the unique identifier was changed and we have an updated record
-
For Each ctl In frm.Controls
-
If HasProperty(ctl, "ControlSource") Then
-
ctrlSource = ctl.ControlSource
-
If fieldExists(ctrlSource, rsexist) Then 'Sometimes you have a control that is part of another table
-
If IsNull(frm.Controls(ctrlSource)) And IsNull(rsexist.Fields(ctrlSource)) Then '2 Null strings do not pass equal check
-
ElseIf frm.Controls(ctrlSource) = rsexist.Fields(ctrlSource) Then
-
ElseIf InStr(1, ctrlSource, "LastUpdated", vbTextCompare) Then 'Changes in updated boxes shouldn't be tracked
-
Else
-
If undoChanges Then
-
If messageShown Then
-
Else
-
Call MsgBox("This item is procured and cannot be edited. Undoing all changes.", vbExclamation, "Record is Locked")
-
messageShown = True
-
frm.Undo 'Had to use generic undo since individual field fixing doesn't update the display
-
Exit For 'Had to use generic undo since individual field fixing doesn't update the display
-
End If
-
Else
-
If hasLabel(ctl) Then
-
changes = "[" & ctl.Controls(0).Caption & "]=" & rsexist.Fields(ctrlSource) & " -> " & frm.Controls(ctrlSource) & "; " & changes
-
Else
-
changes = "[" & ctrlSource & "]=" & rsexist.Fields(ctrlSource) & " -> " & frm.Controls(ctrlSource) & "; " & changes
-
End If
-
fieldChanged = True
-
End If
-
End If
-
End If
-
End If
-
Next ctl
-
End If
-
If fieldChanged Then 'If undoChanges, then fieldChanged never becomes true
-
frm!txtLastUpdated = Now()
-
frm!cboLastUpdatedByUserID = Forms!frmLogin!cboUname.Column(1)
-
frm!ChangeTrackingBox.Value = Format(Now(), "mm/dd/yy") & " - " & Forms!frmLogin!cboUname.Column(4) & _
-
": " & Left$(changes, Len(changes) - 2) & Chr(13) & Chr(10) & frm!ChangeTrackingBox.Value
-
End If
-
End If
-
-
If messageShown Then 'If the message box was shown for the item already being procured, then cancel any form navigation
-
fillLastUpdated = False
-
Else
-
fillLastUpdated = True
-
End If
-
Exit Function
-
End Function
-
Thanks in advance for the help!
4 3510 MMcCarthy 14,534
Recognized Expert Moderator MVP
You need to requery/reopen the form containing the deleted record. That will get rid of the #deleted markers.
Have you looked at the IsDirty event. I think this would save you a lot of effort on edited records.
Thanks for the reply. I can't requery the form because these operations are done before a form requery (Form_Before_Up date event). The purpose is to check if any fields have been changed, and if so, record all changes made into <frm!ChangeTrac kingBox.Value> and also the person who updated it <frm!cboLastUpd atedByUserID> and the date it was done <frm!txtLastUpd ated>. If that particular record is locked, then undoChanges is passed as true, in which case it stops the first moment it finds a box with a differing value.
I need some way to automatically skip deleted boxes without it crashing the whole function.
Thanks again for the help.
MMcCarthy 14,534
Recognized Expert Moderator MVP
I can't think of anything offhand. If you try to check the value of the control then the control has focus and the application throws an error.
You might want to look at overwriting the error using error handling. If the error message thrown up on crashing gives the err number then use it to catch the error and code the behavior you want. Does that make sense?
I basically used an error handler that checks if the error code for "#Deleted" was hit, then it does frm.undo to essentially wipe out all changes and tells the user to reopen the form and try again.
If there is a way to just skip these deleted boxes that would be a huge blessing.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: tabonni |
last post by:
Hi All
I connected MS Exchange Server to MS Access database using Access link
table function. I can open the database table and see the fields and
data inside the link table. However, the table seems cannot be queried
by SQL statement.
My situation is:
I'm building an intranet. I have a ASP login page for all staff in the
company to login. Other people can't register or login the intranet.
|
by: Jucius |
last post by:
Using Delphi for developing, I get and delete records from an Access
Database 2000.
Sometimes, It happened 3 times (but not reproductible), the application did
not succeed in connecting the database. A table is corrupted and when you
compact, this table is deleted with an error -1206 created.
I did not find resources on this problem on the web. if somebody can help, I
have to solve it by Monday (it is quite HOT !).
If somebody can...
|
by: John |
last post by:
Hi
I have a janusys (www.janusys.com) grid2000 ex on an access form. From time
to time the clients report a 'Record Deleted' error after which everything
goes blank. No user seems to have deleted any record deliberately. Any idea
what this error could be?
|
by: bill_hounslow |
last post by:
I'm trying to transfer data from one Sql Server into a table on
another, using a simple INSERT query in an Access database with links
to tables on both servers (the reasons for this are complicated but it
IS the simplest solution, believe me).
The 'Select' clause of the query works fine when run alone, but, when I
run the INSERT query I get a Runtime Error 3167 Record Deleted.
I get the error even when I'm the only person accessing...
|
by: merco |
last post by:
HI,
i have an access MDB linked by ODBC to SQL2k.
I get a strange behaviour when i do an INSERT INTO to a local Mdb table
from "LEFT OUTER JOINED" SQL tables.
If the related table hasn't related record to the first table,
it get an Error 3167.
This behaviour is not present with MDB tables JOINS.
| |
by: PAUL |
last post by:
Hello,
I have 2 datasets I am trying to update. The parent table seems to
update fine but when I go update the chiled table I get an error message
that says I need a related record in the parent table. However I put some
code in to display the key field of each parent table record (parent
dataset) and the value I am trying to put into the child table is there.
ParentTable ChildTable
ID------------------------<...
|
by: jaishu |
last post by:
Hi all,
I am in the process of developing a small Access application. i have the table locally in Access and I am using the following code to get the recordcount ( or to check if any record exists), but when i check for rst.eof condition, it is always true..but when i actually run the query i see, many rows returned, i m not clear about how to specify the connection parameter with the recordset, as i m using the tables locally, but i...
|
by: david720 |
last post by:
Error 3167 Record is deleted
And
Sometimes the main entry form displays a record in the form where all
fields are "#Delete"
Why do we get this error sometimes (about 2 times a week)?
It happens from different users and on different workstations.
Also in this application no records are ever deleted and would be
difficult for a user to delete a record
|
by: Michael R |
last post by:
Hello.
I have a form with 2 sub-forms in it. One is sfrmFormulations, other is sfrmCategories. Logically, tblFormulations and tblCategories related on one-to-many basis and have delete and update cascade. In the form, they are kept related by on current event in sfrmFormulations, which activates a requery in sfrmCategories.
When I delete a record in sfrmFormulations, I get the following error:
Run-time error '3167'
Record is deleted....
|
by: Vee007 |
last post by:
Following is my code:
Dim objCatalog As ADOX.Catalog
Dim objTableLink As ADOX.Table
Dim objADOConnection As ADODB.Connection
Try
objADOConnection = New ADODB.Connection
objADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Database\abc.mdb;User Id=admin;Password=;")
objCatalog = New ADOX.Catalog
objCatalog.ActiveConnection = objADOConnection
...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |