Access creating deleted record occassionally on one form

[Kev86]

I have a Access database being used by about 50-60 people. Most of the time it works fine but occasionally it crashes and users end up with a 'Record is deleted' error.
I know when this occurs and it's usually when members of my team are using a Paid Jobs form, they update some fields and when they move to the record below the record they have just been working on shows up as 'Deleted' and then everyone has issues getting into forms and tables and that's when they get the 'Record is Deleted' error.

What I can't work out though is why it only happens on this form and only happens occasionally. There is VBA code with If statements in the Before Update event to prevent people from setting certain criteria such as a date in the past etc. There is also some VBA code in the After Update event which just generates an E-mail based on certain criteria, it only generates the E-mail in .Display and not .Send.

Any help on this would be much appreciated.

[CJ_London]

Access crashing implies an issue with your code or the way you have it setup. At the moment you have not provided that information.

At the very least we would need to know how your app is set up for the users - is the db split between BE and FE? does each user have their own copy of the FE? What is the BE - Access? Sql Server? How do the users connect? via hard wired LAN? Wirelessly?

Do you get any error messages? if so, what are they?

We would also need to see all the code associated with your form - this includes any code in a standard module called by the form code.

[Kev86]

Hi CJ thank you for your response. So the database is split by FE and BE, each user has a local copy of the FE stored on their PC which they use to access the database via the BE which is stored on the companies network drive. There is a mixture of people connecting via LAN and wirelessly either in the office or from home, the issue has happened for users both at home and in the office so no regular theme there. The BE is not SQL server, just a BE stored on a company Network drive.

No there's no error messages that occur when this happens, all the user does is complete the line they are updating and move down to the next line and at that point the previously update line above shows as Deleted.

Here is the code from the form:

Private Sub Form_AfterUpdate()


If Not IsNull(Me.OPC) Then
If Not IsNull(Me.[3rdPartyReqSent]) Then




Dim o As Outlook.Application
    Dim M As Outlook.MailItem
    
    Set o = New Outlook.Application
    Set M = o.CreateItem(olMailItem)
    
    With M
        .BodyFormat = olFormatHTML
        .Subject = "3rd Party Consents Required - " & Me.SAP & " - " & Me.SiteAddress & " " & Me.Town & " " & Me.Postcode
        .To = Me.OPCEmail
        .HTMLBody = "Job " & Me.SAP & " - " & Me.SiteAddress & " - Job has been paid & accepted, please obtain 3rd party consents and once cleared update the database"
        .Display
        
        
    
    End With
       
    
    
    Set M = Nothing
    Set o = Nothing


End If
End If




If Not IsNull(Me.DeliveryArea) Then
If Not IsNull(Me.Delivery_Email) Then
If (Me.OPCDeliveryPending) = False Then


Dim a As Outlook.Application
    Dim b As Outlook.MailItem
    
    Set a = New Outlook.Application
    Set b = a.CreateItem(olMailItem)
    
    With b
        .BodyFormat = olFormatHTML
        .Subject = Me.SAP & " - " & Me.SiteAddress & " " & Me.Town & " " & Me.Postcode & " - " & Me.DeliveryResource
        .To = Me.Delivery_Email
        .HTMLBody = "Job " & Me.SAP & " - " & Me.SiteAddress & " - The job has been passed to your Delivery area" & "<br />" & "<br />" & "MCCSS / Post Quote Call Score - "
        .Display
        
        
    
    End With
       
    
    
    Set a = Nothing
    Set b = Nothing


End If
End If
End If




If Not IsNull(Me.DeliveryArea) Then
If Not IsNull(Me.Delivery_Email) Then
If (Me.OPCDeliveryPending) = True Then


Dim c As Outlook.Application
    Dim D As Outlook.MailItem
    
    Set c = New Outlook.Application
    Set D = c.CreateItem(olMailItem)
    
    With D
        .BodyFormat = olFormatHTML
        .Subject = Me.SAP & " - " & Me.SiteAddress & " " & Me.Town & " " & Me.Postcode & " - Pending Consents" & " - " & Me.DeliveryResource
        .To = Me.Delivery_Email
        .HTMLBody = "Job " & Me.SAP & " - " & Me.SiteAddress & " - The job has been passed to your Delivery area, please start works but do not energise until the Consents Surveyor has confirmed consents have cleared" & "<br />" & "<br />" & "MCCSS / Post Quote Call Score - "
        .Display
        
        
    
    End With
       
    
    
    Set c = Nothing
    Set D = Nothing


End If
End If
End If






End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)


If Me.JobtoDelivery < Me.PaymentRec Then
    MsgBox "Job to Delivery date cannot be before Payment Received Date"
    Cancel = True
    Me.JobtoDelivery.SetFocus
End If


If Me.AssetHierReq < Me.PaymentRec Then
    MsgBox "Asset Hierarchy Requested date cannot be before Payment Received Date"
    Cancel = True
    Me.AssetHierReq.SetFocus
End If


If Me.[3rdPartyReqSent] < Me.PaymentRec Then
    MsgBox "3rd Party Request Sent date cannot be before Payment Received Date"
    Cancel = True
    Me.[3rdPartyReqSent].SetFocus
End If


If IsNull(Me.PaidJobQDP) Then
If Not IsNull(Me.JobtoDelivery) Then
    MsgBox "Job to Delivery QDP field cannot be blank when Job to Delivery field is updated"
    Cancel = True
    Me.PaidJobQDP.SetFocus
End If
End If


If IsNull(Me.JobtoDelivery) Then
If Not IsNull(Me.DeliveryArea) Then
    MsgBox "Job to Delivery field cannot be blank whilst Delivery Area field is updated"
    Cancel = True
    Me.JobtoDelivery.SetFocus
End If
End If


If IsNull(Me.DeliveryArea) Then
If Not IsNull(Me.JobtoDelivery) Then
    MsgBox "Delivery Area field cannot be blank whilst Job to Delivery field is updated"
    Cancel = True
    Me.DeliveryArea.SetFocus
End If
End If


If Me.ThirdParty = True Then
If IsNull(Me.[3rdPartyReqSent]) Then
If Not IsNull(Me.JobtoDelivery) Then
    MsgBox "Job to Delivery date cannot be updated whilst 3rd Party Clearance is Required"
    Cancel = True
    Me.[3rdPartyReqSent].SetFocus
End If
End If
End If


If Not IsNull(Me.AssetHierReq) Then
If Not IsNull(Me.JobtoDelivery) Then
    MsgBox "Asset Hierarchy must be completed before this job can be sent to Delivery"
    Cancel = True
    Me.JobtoDelivery.SetFocus
End If
End If


If Not IsNull(Me.[3rdPartyReqSent]) Then
If Not IsNull(Me.JobtoDelivery) Then
    MsgBox "3rd Party Clearance must be completed before this job can be sent to Delivery"
    Cancel = True
    Me.JobtoDelivery.SetFocus
End If
End If


If IsNull(Me.NumofMPAN) Then
If Not IsNull(Me.JobtoDelivery) Then
    MsgBox "Num of Mpan's field cannot be blank when job is sent to Delivery"
    Cancel = True
    Me.NumofMPAN.SetFocus
End If
End If


If Not IsNull(Me.[3rdPartyReqSent]) Then
If IsNull(Me.OPC) Then
    MsgBox "OPC field cannot be blank when 3rd Party Request Sent field is updated"
    Cancel = True
    Me.OPC.SetFocus
End If
End If


If IsNull(Me.[3rdPartyReqSent]) Then
If Not IsNull(Me.OPC) Then
    MsgBox "OPC Surveyor field cannot be updated when 3rd Party Request Sent field is blank"
    Cancel = True
    Me.OPC.SetFocus
End If
End If






End Sub


Private Sub JobtoDelivery_AfterUpdate()


UpdateQDPJobToDeliveryDateLog Me.SAP, Nz(Me.JobtoDelivery, "")


End Sub


Private Sub PaidJobQDP_AfterUpdate()


UpdateQDPPaidJobsLog Me.SAP, Nz(Me.PaidJobQDP, "")


End Sub

[Bob Fitz]

...There is a mixture of people connecting via LAN and wirelessly either in the office or from home, the issue has happened for users both at home and in the office so no regular theme there...

I suspect that the wireless connections will be the cause of your problems. Access does not play nicely with wireless connections. I believe I'm correct in saying that you would need to change the BE to, perhaps SQL Server Express. An alternative for those using a wireless connection might be to use a remote desktop application.

EDIT: Additional info. The following link may be of interest: https://www.access-programmers.co.uk...etwork.319092/

[Micron]

I'm not sure a random issue can be uncovered by reviewing puzzle pieces of code. One thing you forgot that was requested is the code for procedures that are being called, such as UpdateQDPJobToDeliveryDateLog. Quite possible that each procedure posted leads down some other path - so consider copying db, compact and zip and posting. You can removed objects if you're sure they have no relevance but the result may be that something pertinent is removed.

Nothwithstanding that any connection type seems to have this happen, connecting to Access via wifi is not advised.

[Bulzie]

So no one is actually deleting any records on this form or other forms that that could show those same records? Are any records actually getting deleted or is it just displaying that on the form?

Do they have SQL? It might make sense long term to switch to SQL BE if that many folks are using the system.

[CJ_London]

I agree = likely to be the wifi connection - easy enough to check - next time a user has the problem - ask them for that detail

I support a client's home grown application which sounds like has some of the same functionality. Among many other things it sends emails and retrieves emails for further processing, creates documents on another server etc. Users used to get the occasional error. This was down to three factors - outlook not open, on first login and unstable wifi.

All three were resolved by adding in code to check that the required connections existed (outlook open, other server available) before commencing each interaction with outlook/server, giving the user the option to wait or abort. In the case of creating documents on the other server this check was done before each document was created. In one case the process was changed because it took several seconds to create a particular document to creating it locally, check the server was available and then move it across.

I have been caught out in the past - using my laptop hardwired, called into a meeting taking the laptop, I'm asked a question, open the app and boom, FE corrupted because it was trying to connect wirelessly.

[Kev86]

Thank you all for your responses so far. I have always wondered whether it's a connection issue with people working from home but it's then odd it only occurs on this one form and no others. I am not that tech savvy to move it to an SQL server but it might be worth looking into as we have speed issues also, so with moving it to SQL server would this also help with the database speed and performance?

CJ how do I get the form to check a required connection exists and Outlook is open before trying to run the transaction?

I have also had someone mention that moving the BE to Dataverse will help with a lot of the performance issues, anyone have a view on this?

Micron here is the code being called...

Public Sub UpdateQDPPaidJobsLog(strSAP As String, strPaidJobQDP As String)


    strSQL = "INSERT INTO QDPChangeLog  (SAP , ChangeDate , JobtoDeliveryQDP, User) VALUES ('" & strSAP & "' ,  NOW() , '" & strPaidJobQDP & "', fOSUsername() )"
    'Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges




End Sub

Public Sub UpdateQDPJobToDeliveryDateLog(strSAP As String, strJobtoDelivery As String)


    strSQL = "INSERT INTO QDPChangeLog  (SAP , ChangeDate , JobtoDeliveryDate, User) VALUES ('" & strSAP & "' ,  NOW() , '" & strJobtoDelivery & "', fOSUsername() )"
    'Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges




End Sub

[ArviLaanemets]

Are people having this problem, when working in local LAN, using always cable connection, or they may use local wireless connection too? Because with local wireless there may be connection problems occasionally too (number of allowed connections for router are exceeded, user is moving from service area of one router to another, etc.).

The foolproof way would be, that all laptop users (unless you have only cable connection), and all users who may connect remotely, have Terminal Server access in your LAN, and always work with this app from TS profile.

[Micron]

Not sure I see the point in dbSeeChanges without error handling. That option is supposed to raise a run time error if two users are trying to change the same set of records at the same time. If that happens, you're letting Access decide what to do?

You might want to consider other means of connecting for remote users. Citrix would be one. Another used to be Terminal Services but I think it's called something else now (Remote Desktop?).

[CJ_London]

CJ how do I get the form to check a required connection exists and Outlook is open before trying to run the transaction?

For office apps code I use

Function AppIsAvailable(appName As String) As Boolean
Dim officeApp As Object

    On Error Resume Next

    Set officeApp = GetObject(, appName & ".Application")
    AppIsAvailable = Err = 0
    
End Function

and used in code with say

if AppIsAvailable("Outlook") then
    'carry on
Else
   msgbox "Please open Outlook and try again", vbOKOnly
end if

for drives, my client uses mapped drives so the code is

Function DriveIsAvailable(MDrive As String) As Boolean

    On Error Resume Next

    ChDir MDrive & ":\"
    DriveIsAvailable = Err = 0
    
End Function

and used in code with say

if DriveIsAvailable("Z") then
    'carry on
Else
   msgbox "Drive not available, there may be a temporary network failure so please try again later. If the problem persists please contact your IT dept", vbOKOnly
end if

[Kev86]

Hi Arvil, It has happened for users in the office working on LAN but very very rare. This predominantly occurs for users working from home and I guess they are most likely using wifi rather than a LAN connection. We have lots of users working remotely and in the office using the database but the issue really only occurs on this form for some reason. Could be it's got too many IF statements and sometimes failing to run maybe and causing the issue.

[Kev86]

Hi CJ,

Thank you, I am not much of a coder but I will take a look at what you have provided and see whether I can add it in at all. The first piece of code you have provided is that it looking to make sure the connection is available with the Access database server?

Function AppIsAvailable(appName As String) As Boolean
Dim officeApp As Object

    On Error Resume Next

    Set officeApp = GetObject(, appName & ".Application")
    AppIsAvailable = Err = 0
    
End Function

Micron,

Thank you for your response, I am not sure how I could get the application built into Citrix through the company systems but i will have to ask.

[ArviLaanemets]

Hi Arvil, It has happened for users in the office working on LAN but very very rare. This predominantly occurs for users working from home and I guess they are most likely using wifi rather than a LAN connection. We have lots of users working remotely and in the office using the database but the issue really only occurs on this form for some reason. Could be it's got too many IF statements and sometimes failing to run maybe and causing the issue.

When the FE is opened from TS, then data exchange happens only between TS Server and server where BE is stored, i.e. in local LAN. Between user's computer/profile and FE, wherever the user is located at moment, all info what is exchanged are keystrockes, mouse moves and clicks, and the picture displayed on screen.

Looked more closely at your post #3. The event is AfterUpdate, i.e. when this event is running, at this moment the record is saved or deleted (updated) - updating doesn't happen after this event, or when this event is running! So isn't there a BeforeUpdate event for this form too? The sequence of events when moving to next record in form is:
BeforeUpdate>AfterUpdate>Exit>LostFocus>Current
Updating of record happens between BeforeUpdate and AfterUpdate!

[CJ_London]

The first piece of code you have provided is that it looking to make sure the connection is available with the Access database server?

No - per the example it is checking if the application is available e.g. Outlook. The BE is a file used by the Access application - you would use the second code to see if you have a connection to the server where the BE is stored. If the server is not available, neither will the BE - so any interactions your FE has with the BE (even just opening a form) will fail while that connection is lost - so better to either put the FE on wait or close the app.

It all depends on your particular circumstances as to what would work for you. For example your code might go into a loop with a timer to see if the connection comes back up. Give it a few seconds and if the connection is remade continue, otherwise give the user a chance to continue waiting or close the app.