Solved Record is deleted (multi user database) (1 Viewer)

ProgramRasta

Member
Local time
Today, 18:15
Joined
Feb 27, 2020
Messages
98
Hi all

I have a database that multiple users use simultaneously (front end and back end). Within this database the main form is a continuous form. The problem I have is when one user deletes a record, other users of the database see the #deleted and get an error “record is deleted” when they pass over the record in the table. Note that the person who deletes the record requeries the form so they don’t see it.

my question is, is there anyway to trap this error so that before the message “record is deleted” pops up it will requery the form?

many thanks
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:15
Joined
Feb 19, 2013
Messages
16,553
not really. Deleting records on an individual basis (as opposed to being part of a tidying up exercise) is generally considered poor practise. Better to have a 'deleted' flag field (typically a date so you know when it was deleted).

You could look at using the form on error event - but not sure if the deleted message is considered an error.

when they pass over the record in the table.
there is no error management in tables

only other thing I can suggest is you requery the form on a regular basis.
I have a database that multiple users use simultaneously (front end and back end).
I hope that means each user has their own copy of the front end on their local machine, otherwise you will be building much bigger problems for yourself if users are sharing the front end
 
Last edited:

Micron

AWF VIP
Local time
Today, 14:15
Joined
Oct 20, 2018
Messages
3,476
Your users are seeing the message when mousing over a form record, not a table record I hope. Besides flagging a record, you can restrict form records to those pertinent to the user in some cases, so that they don't see the ones they don't need to know about. Have to admit I don't recall seeing this behaviour just by passing over a record.

By " multiple users use simultaneously (front end and back end) " does that mean everyone has their own fe or are they sharing one?
 

namliam

The Mailman - AWF VIP
Local time
Today, 19:15
Joined
Aug 11, 2003
Messages
11,696
Best solution is to NOT delete records period... you can only delete a record once, and in all cases you are destroying potentially precious information.

The solution is to have a field "DELETED_YN" or "Deleted date/time" or simular and fill that when the record should be deleted. This also fixes your problem of other users...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:15
Joined
May 7, 2009
Messages
19,169
Whether the record got physically deleted or a field is updated signaling that the rec is deleted, you still need to Requery the Form:
Code:
Option Compare Database
Option Explicit

Private mstrID As String

Private Sub Form_Current()

    Const pkFieldName As String = "ID"
    Dim strID As String
    Dim tfNeedRequery As Boolean
    
On Error GoTo err_
    strID = Trim(Me(pkFieldName) & "")
    If Len(strID) > 0 Then
        mstrID = strID
        
    End If
    With Me.RecordsetClone
        If Not (.BOF And .EOF) Then
            .MoveFirst
            Do While Not .EOF
                If Len(Trim(.Fields(pkFieldName) & "")) > 0 Then
                Else
                    tfNeedRequery = True
                    Exit Do
                End If
                .MoveNext
            Loop
        End If
reqry:
        If tfNeedRequery Then
            .Requery
            
            If Len(mstrID) > 0 Then
                .FindFirst pkFieldName & " = " & mstrID
                Me.Bookmark = .Bookmark
            End If
        End If
    End With
Exit Sub
err_:
    If Err.Number = 3167 Then
        tfNeedRequery = True
        Resume reqry
    End If
End Sub
 

ProgramRasta

Member
Local time
Today, 18:15
Joined
Feb 27, 2020
Messages
98
Whether the record got physically deleted or a field is updated signaling that the rec is deleted, you still need to Requery the Form:
Code:
Option Compare Database
Option Explicit

Private mstrID As String

Private Sub Form_Current()

    Const pkFieldName As String = "ID"
    Dim strID As String
    Dim tfNeedRequery As Boolean
   
On Error GoTo err_
    strID = Trim(Me(pkFieldName) & "")
    If Len(strID) > 0 Then
        mstrID = strID
       
    End If
    With Me.RecordsetClone
        If Not (.BOF And .EOF) Then
            .MoveFirst
            Do While Not .EOF
                If Len(Trim(.Fields(pkFieldName) & "")) > 0 Then
                Else
                    tfNeedRequery = True
                    Exit Do
                End If
                .MoveNext
            Loop
        End If
reqry:
        If tfNeedRequery Then
            .Requery
           
            If Len(mstrID) > 0 Then
                .FindFirst pkFieldName & " = " & mstrID
                Me.Bookmark = .Bookmark
            End If
        End If
    End With
Exit Sub
err_:
    If Err.Number = 3167 Then
        tfNeedRequery = True
        Resume reqry
    End If
End Sub
Thanks for this, is “ID” a name of a field in my table? Sorry I’m not an advanced programmer by any stretch. I set it to the primary key on the table on which the form operates and added this code to the on current event. It still doesn’t work.

Thanks Rasta
 

ProgramRasta

Member
Local time
Today, 18:15
Joined
Feb 27, 2020
Messages
98
Your users are seeing the message when mousing over a form record, not a table record I hope. Besides flagging a record, you can restrict form records to those pertinent to the user in some cases, so that they don't see the ones they don't need to know about. Have to admit I don't recall seeing this behaviour just by passing over a record.

By " multiple users use simultaneously (front end and back end) " does that mean everyone has their own fe or are they sharing one?
Everyone has their own front end. It’s a continuous form with bound controls. The data is only needed for a short time and will be of no use after a certain period. It’s for this reason it’s deleted (and to keep the database running fast).
if you go into the back end and delete the record you’re currently viewing on the form. You can requery and move onto the next record as part of the delete operation. However, I’m asking if other users can somehow requery the form based on the error or some other mechanism.

thanks Rasta
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:15
Joined
Feb 19, 2002
Messages
42,971
Deleted records do not get purged and the space recovered until you do a Compact of the BE so "keeping it fast" isn't happening because you allow deletes.

When you open a form, Access retrieves a recordset to populate that form and the recordset remains in memory. So, what you are seeing is caused because Access recognizes that someone else actually modified/deleted a record that it retrieved for you and is keeping in a local recordset.

A good way to eliminate this problem is to use a RecordSource bound to a query with criteria rather than to a raw table. If your form opens to only the record, the current user wants to view/modify, you will not likely be retrieving records that are being used by other users.

Old style Access applications frequently bind forms to tables or sometimes queries with no criteria. They then allow users to filter the data to get to the record they actually want to operate on. A better method that also happens to be more efficient if your BE is SQL Server, is to ask the user to filter first, then you retrieve what he asked for rather than the current method of retrieving everything and then filtering later.

If that convoluted explanation makes any sense and you want to try it, we can offer suggestions. When i have simple selection criteria that will return only a single record, I use an unbound combo or text box directly on the form used for maintenance to be used to pick the record the user wants to view or edit. If the search criteria is more complex and might result in retrieving several records, I use a separate form with lots of fields. In this case, I frequently build the SQL on the fly rather than using a querydef.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:15
Joined
Feb 19, 2013
Messages
16,553
To add to Pat's comments using the where parameter of docmd.openform only filters the data, it does not apply a criteria - easy to check. Just have the form you are opening display the navigation buttons - you will see the data is filtered
 

ProgramRasta

Member
Local time
Today, 18:15
Joined
Feb 27, 2020
Messages
98
Thanks all for the replies. I would welcome any suggestions. The problem i have is that, there are 200,000 records in the database table. My solution was to split the table in two (current records and archived records). Once the current records where of no longer use, I would copy them to the archived records and delete them from the current. This seems to speed up the process of navigating the form as there are some calculated fields on the current form.

is there any way I can Maintain this speed but by keeping all the data in one table?

many thanks for your time.
 

ProgramRasta

Member
Local time
Today, 18:15
Joined
Feb 27, 2020
Messages
98
My back end is not an SQL server, the main form has a record source that’s a query with some calculated fields in the query that display on the form. The controls on the form are bound to a table so when users make updates they are saved etc.

thanks again
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:15
Joined
Feb 19, 2013
Messages
16,553
200000 records is not a lot - providing you have proper indexing. put an 'archivedDate' (indexed) field in your table and populate it instead of deleting, ensure the index is set to ignore nulls (see indexes option on the ribbon) and modify your queries to exclude records where this field is populated. As an end of day process you can then if you want run a routine to populate your archive table and delete them from your 'main table'

you may also want to consider moving your calculated values from the form to the underlying query (depends what the calculations are) - ensuring the query remains updateable of course (look at the recordset type property and try changing to dynaset - inconsistent updates)

A better solution is to not bring all 200,000 records through in the first place

In design view set the form recordsource to something like

SELECT * FROM myQuery WHERE False

which will return 0 records

then as has been suggested, apply some criteria either before opening the form - passing the criteria string as an openarg and in the form open event put something like

me.recordsource="SELECT * FROM myQuery WHERE " & me.openargs

Or on the form enable the user to apply some criteria - perhaps user, a date, an action or similar and when they click the 'filter' button you would have code something like

criteriaStr="[myField]=" & combo1
me.recordsource="SELECT * FROM myQuery WHERE " & criteriaStr
 
Last edited:

ProgramRasta

Member
Local time
Today, 18:15
Joined
Feb 27, 2020
Messages
98
Thanks for the reply.

I just have one follow on question if I may. Why do we put the index to null. Does the index only search for null values then? Any idea how I would index a checkbox (only return false values) to speed up the index?

thanks again
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:15
Joined
Feb 19, 2013
Messages
16,553
Why do we put the index to null.
simplistically an index consists of two properties, the value and a pointer to the physical location of the record. If you index nulls then you have a lot of superfluous pointers in your index. If you don't, then for where you have a lot of nulls and a few 'not nulls' the index is much shorter and faster - and the database engine can make an assumption - if not in the index then it is null.

you might find this link of interest

Note with booleans, values are 0 or -1, no nulls, so where you have a situation such as yours and you have an archive flag as a yes/no field the you will have many 0's and a few -1's (or the other way round) - so not so efficient. Which is why it is better to use a date field - populated=yes, not populated=no and it gives you more information i.e. when.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:15
Joined
Feb 19, 2002
Messages
42,971
Just FYI, the OpenForm method can take a Filter or a Where condition. I always use Where to limit the rows returned. Both will show a filter value but if you use the Filter option, the entire recordset will be populated but the form will be positioned on the selected record. If you use the Where option, Access actually temporarily modifies the where clause to select only the single record. If the form you open has a navigation bar, you can see the difference there. In the Where case, you will see record 1 of 1 but in the Filter case, you will see record 1 of n.

So. These two statements produce DIFFERENT results as you would expect.

Private Sub cmdFilter_Click()
DoCmd.OpenForm "frmPeople", acNormal, "PersonID = " & Me.cboPersonID
End Sub

Private Sub cmdWhere_Click()
DoCmd.OpenForm "frmPeople", acNormal, , "PersonID = " & Me.cboPersonID
End Sub



DoCmd.OpenForm method (Access)
The OpenForm method carries out the OpenForm action in Visual Basic.

Syntax
expression.OpenForm (FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

expression A variable that represents a DoCmd object.

Parameters
PARAMETERS
NameRequired/OptionalData typeDescription
FormNameRequiredVariantA string expression that's the valid name of a form in the current database. If you execute Visual Basic code containing the OpenForm method in a library database, Access looks for the form with this name first in the library database, and then in the current database.
ViewOptionalAcFormViewAn AcFormView constant that specifies the view in which the form will open. The default value is acNormal.
FilterNameOptionalVariantA string expression that's the valid name of a query in the current database.
WhereConditionOptionalVariantA string expression that's a valid SQL WHERE clause without the word WHERE.
DataModeOptionalAcFormOpenDataModeAn AcFormOpenDataMode constant that specifies the data entry mode for the form. This applies only to forms opened in Form view or Datasheet view. The default value is acFormPropertySettings.
WindowModeOptionalAcWindowModeAn AcWindowMode constant that specifies the window mode in which the form opens. The default value is acWindowNormal.
OpenArgsOptionalVariantA string expression. This expression is used to set the form's OpenArgs property. This setting can then be used by code in a form module, such as the Open event procedure. The OpenArgs property can also be referred to in macros and expressions.

For example, suppose that the form that you open is a continuous-form list of clients. If you want the focus to move to a specific client record when the form opens, you can specify the client name with the OpenArgs argument, and then use the FindRecord method to move the focus to the record for the client with the specified name.
Remarks
You can use the OpenForm method to open a form in Form view, form Design view, Print Preview, or Datasheet view. You can select data entry and window modes for the form and restrict the records that the form displays.

The maximum length of the WhereCondition argument is 32,768 characters (unlike the WhereCondition action argument in the Macro window, whose maximum length is 256 characters).

Example
The following example opens the Employees form in Form view and displays only records with King in the LastName field. The displayed records can be edited, and new records can be added.

VBCopy

DoCmd.OpenForm "Employees", , ,"LastName = 'King'"


The following example opens the frmMainEmployees form in Form view and displays only records that apply to the department chosen in the cboDept combo box. The displayed records can be edited, and new records can be added.

VBCopy

Private Sub cmdFilter_Click()
DoCmd.OpenForm "frmMainEmployees", , , "DepartmentID=" & cboDept.Value
End Sub

The following example shows how to use the WhereCondition argument of the OpenForm method to filter the records displayed on a form as it is opened.

VBCopy

Private Sub cmdShowOrders_Click()
If Not Me.NewRecord Then
DoCmd.OpenForm "frmOrder", _
WhereCondition:="CustomerID=" & Me.txtCustomerID
End If
End Sub
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:15
Joined
Feb 19, 2013
Messages
16,553
@Pat
Not sure I agree

My code
DoCmd.OpenForm "frmactions", , , "ActionPK=1"

displays this
1593103771778.png


If I unfilter I get this - all records showing

1593103813289.png


The WHERE parameter is poorly named - it should be called the FILTER parameter

and this is ignored ( FilterName ) - all records display and the form opens on the first record

DoCmd.OpenForm "frmactions", , "ActionPK=3"
1593104562036.png


if it works for you, perhaps it is a setting in access somewhere?
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:15
Joined
Sep 21, 2011
Messages
14,046
@CJ)London
I always thought it worked the way Pat describes, but after seeing your previous post and trying it out, it did exactly as you state, hence the like.? :unsure:
 

ProgramRasta

Member
Local time
Today, 18:15
Joined
Feb 27, 2020
Messages
98
Thanks all for the time taking to reply and for sharing your knowledge! It’s people like you that make these forums an excellent place to come.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:15
Joined
Feb 19, 2002
Messages
42,971
CJ, do you know for a fact that the Filter and Where both bring in the entire recordset? I don't have time to test this but the BE would need to be SQL server so you could see the query being sent to the server to determine what exactly Access is doing. It doesn't make any sense to have both a Filter and a Where argument if the same thing happens for both.

I'm pretty sure that Access is doing something behind the scenes to "help" a novice and it is running a requery without the where argument when you press the filter icon to toggle the filter.

But we're only guessing unless someone has the time to do this with a SQL Server BE.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:15
Joined
Feb 19, 2002
Messages
42,971
is there any way I can Maintain this speed but by keeping all the data in one table?
I gave you the solution in my original comment. Filter FIRST. If you need help with this, we'll be happy to help you.
 

Users who are viewing this thread

Top Bottom