Problem with listing recent opened records

[Joe Y]

In a multi user environment I am trying to add a combo box to a form that lists all records recently been opened and the list is sorted from newest to older date/time. User can open a record with this combo box.

The date/time stamp of the record needs to reflect the open time either by search results or by go to record with another drop down list of the same form.

The reason I wanted to do this is because I have hundreds of record. With this feature, users can easily open frequent/recently opened records by clicking this drop down list.

My current approach is to create a data field in the table called RecentDate. Then a bound text field named txtRecentDate (hidden) on the form.

I have below VBA line in these form events: On Load and On Current.

Me![ txtRecentDate] = Now() 

Here is the problem that I encountered. Error message ‘cannot assign value to this object’ will popup twice when trying to open a record that is already being opened by another user.

I believe this is due to vba trying to overwrite current date/time to the data field “RecentDate” in the On Load and On Current events.

I would like to know is there a better/appropriate way to handle this?

Thanks in advance.

[zmbd]

If the record is already opened for editing then you are most likely running in to record locking issues - this will happen with any RDMS.

I personally wouldn't use the on_current to edit a record as soon as the user selects it. This creates all sorts of issues, as you've seen.

With a split database each user should ideally have their own copy of the front-end on their client pc. Within this client's copy of the front-end you could have a table that records the information that is then used to feed the combo-box. This arrangement will avoid locking issues and personalizes the front-end for each user.

There are also some kludge methods to determine if a record is locked... basically you try to edit the record and trap the resulting error if the record is locked.

[jforbes]

I would agree with ZMBD that you should use a different table to track your hot Items. The only dates I would maintain on this type of record would be a create date and maybe a last modified date.

This may not be the exact situation you are running into, but it may help you. We maintain a running list of recent items on our Main Menu for our Users and their short memories, despite the pen and paper sitting right next to them.

This an example of the list:

It's almost cartoony, but with a single click, it will launch the appropriate Form and Filter the Form to the Item they are looking for. The colors come from the three different types of items that they could open... with three different Forms to view them. There is also a button to remove an item from the list.

It uses a Common Table that has the User as part of the Key. This way each user gets their own list of Items:

Then there is the following code that is used to maintain the list. The addRecentItem() function is automatically called whenever the user creates a new Item. The other way to get an Item on their list of Recents is when they have the Item open, they can click a button on the Form that runs the addRecentItem() function.

    'addRecentItem
Public Function addRecentItem(ByRef sUserID As String, ByRef sItemNumber As String, ByRef sSource As String) As Boolean
 
    Dim sSQL As String
 
    If DCount("ItemNumber", "RecentItems", "UserID='" & sUserID & "' AND ItemNumber='" & sItemNumber & "' AND Source='" & sSource & "'") > 0 Then
        sSQL = "UPDATE RecentItems SET [DateTime]='" & Now() & "' WHERE UserID='" & sUserID & "' AND ItemNumber='" & sItemNumber & "' AND Source='" & sSource & "'"
        CurrentDB.Execute sSQL
    Else
        sSQL = "INSERT INTO RecentItems (UserID, ItemNumber, Source) VALUES ('" & sUserID & "','" & sItemNumber & "','" & sSource & "')"
        CurrentDB.Execute sSQL
    End If
 
End Function
 
    'removeRecentItem
Public Function removeRecentItem(ByRef sUserID As String, ByRef sItemNumber As String) As Boolean
 
    Dim sSQL As String
 
    sSQL = "DELETE FROM RecentItems WHERE UserID='" & sUserID & "' AND ItemNumber='" & sItemNumber & "'"
    CurrentDB.Execute sSQL
 
End Function

This setup is not 100% automatic. If the User didn't create the Item, they have to put forth the effort to add it to their list.

You may not want to display your list in the same way as it sounds like you are already using a comboBox, but maybe this will give you some ideas.

[Joe Y]

Thanks zmbd and jforbes. Storing the date/time stamp in separate table is a good idea. User specific date/time stamp is even better.

However, without involving OnCurrent event, record opened by key word search won't be recorded. This is still something I wanted to include.

I am reading articles about Pessimistic locking vs optimistic locking. Is this the direction that I should research about that may be a solution for this issue?

Thanks,
Joe

[zmbd]

Neither pessimistic locking or optimistic locking is going to help you in this case. Once the on_current event touches the record then at least that record and any associated records are going to be locked.

You can extend the secondary table(s) to include the keywords used for the search.

[Joe Y]

Okay, thanks for the advice.