Solved Record is deleted (multi user database)

[ProgramRasta]

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]

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

 

[Deleted member 147267]

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]

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]

Whether the record got physically deleted or a field is updated signaling that the rec is deleted, you still need to Requery the Form:

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]

Whether the record got physically deleted or a field is updated signaling that the rec is deleted, you still need to Requery the Form:

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]

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

 

[CJ_London]

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]

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]

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]

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

 

[ProgramRasta]

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]

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

Why indexing is important for good performance

Indexing is essential for the efficient retrieval of data from a db. Some may be concerned about the additional disk space required for the index(es) - but disk space is cheap and they do not take up that much space. For small db's - a few '000 records per table and simple queries it is...

www.access-programmers.co.uk

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.

 

[CJ_London]

@Pat
Not sure I agree

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

displays this

If I unfilter I get this - all records showing

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"

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

 

[Gasman]

@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.?

 

[ProgramRasta]

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.

 

[CJ_London]

CJ, do you know for a fact that the Filter and Where both bring in the entire recordset?

for a fact, no - but when unfiltering a very large recordset where the WHERE parameter has been utilised it takes no time at all which implies the whole recordset has been loaded. I believe the access feature which allows the first 'screen' of records to be displayed whilst the rest of the recordset to be loaded is still working - but prioritises the selected criteria to display first.

I hadn't picked up on a sql server backend being in use. The filter argument is not a filter, it is an alternative query

A string expression that's the valid name of a query in the current database