Solved Freeze the recordset of a search form

[KitaYama]

Sorry for the title. No matter how many variation I enter, google translate gives me the same "Freeze..."

Objective:

• Open a search result form with a specific filter
• Edit one of the records that makes the record to go out of the scope of the filter
• Requery the the form, and still show the edited record (even if the filter is not true for this record anymore)

Example :
Open the attached file. Select a ShipperID from combo, click "Normal Search" Button. In result form, Edit a shipperID and move to another record to save the changes. The edited record won't be in the list anymore. I want to keep it there, even if the filter for this record is not true anymore.

My current solution: (If you click My Current method button)

• I've added a SearchedBy field to the table.
• Based on the search keyword , I update the SerchedBy field in the table to something like : ",1," ----------> (1 being the UserPK)
  UPDATE tbl SET SearchedBy = ",1," WHERE (the filter that user has selected)
• Show the result form with new filter : "Where SearchedBy Like "'*,1,*'"
• On form's close event, run a query and Replace ",1,"----> to ""

Can you suggest a better method for this situation?
Thanks.

Edit: Why I'm looking for some other methods:
My method has several problems. The worst being if the database closes in an unexpected method, the SearchBy for each user is not cleared.

 

[GPGeorge]

Sorry for the title. No matter how many variation I enter, google translate gives me the same "Freeze..."

Objective:

• Open a search result form with a specific filter
• Edit one of the records that makes the record to go out of the scope of the filter
• Requery the the form, and still show the edited record (even if the filter is not true for this record anymore)

Example :
Open the attached file. Select a ShipperID from combo, click "Normal Search" Button. In result form, Edit a shipperID and move to another record to save the changes. The edited record won't be in the list anymore. I want to keep it there, even if the filter for this record is not true anymore.

My current solution: (If you click My Current method button)

• I've added a SearchedBy field to the table.
• Based on the search keyword , I update the SerchedBy field in the table to something like : ",1," ----------> (1 being the UserPK)
• Show the result form with a new filter : "Where SearchedBy Like "'*,1,*'"

Can you suggest a better method for this situation?
Thanks.

Edit: Why I'm looking for some other methods:
My method has several problems. The worst being if the database closes in an unexpected method, the SearchBy for each user is not cleared.

If I boil this down--as I understand it.

• You want to select a record in a recordset.
• Then apply a different filter to removes that record from the recordset.
• But still have that record available in the same form?

That doesn't appear to be feasible in that formulation. The record is either in the filtered recordset or it's not.

I think you might have success with a second object, i.e. a subform, with its recordset being that previously selected record by itself.

Could that work for this requirement?

 

[The_Doc_Man]

If you are going to use the filter criteria as you describe, that is outside the boundaries of bound forms. However, you COULD do something ugly like add a field to the record to support this operation, and then have the filter be "... WHERE (<original criteria>) OR (<special-field> =TRUE) ..." and then you have to remember to erase the special field when you start the edit and set it TRUE for the current record.

 

[KitaYama]

The record is either in the filtered recordset or it's not.

Most of the times, a search on a table has several parts concatenated by ORs and ANDs.

The moment you edit a record that doesn't satisfy one of those ORs and ANDs, it will disappear from the result. (even if some other part of the filter is still true). I need to have the result on screen, because I have other jobs to do with them, even if one of those ANDs is not true anymore.

For Example you search for all the orders that
(Are not yet delivered) AND (confirmation mail has not been sent)

The form shows the result. You use a button to run a code and send a confirmation mail for only one record, and update the table that mail has been sent.
You still need to do some other tasks with this record, but it's not in the form anymore, because the second part of filter (confirmation mail has not been sent) is not true anymore.

Could that work for this requirement?

Let me do some experiments to see how it looks.

Thanks.

 

[KitaYama]

If you are going to use the filter criteria as you describe, that is outside the boundaries of bound forms.

I'm terribly sorry, but I don't understand this.

However, you COULD do something ugly like add a field to the record to support this operation, and then have the filter be "... WHERE (<original criteria>) OR (<special-field> =TRUE) ..." and then you have to remember to erase the special field when you start the edit and set it TRUE for the current record

I don't know why it's ugly. Less and more, my solution is doing this. But not with true and false. With userPK.
If you want to see how it's done, I attached a db. But I know you don't download files. You can still read the section in my first post above to see how I have done it.

Thanks for your time.

 

[MarkK]

Just don't requery the result form on AfterUpdate, like...

Private Sub Form_AfterUpdate()

'    Me.Requery    ' don't do this
    
End Sub

... and *poof* the edited record doesn't go away.

 

[KitaYama]

Just don't requery the result form on AfterUpdate, like...

Private Sub Form_AfterUpdate()

'    Me.Requery    ' don't do this
 
End Sub

... and *poof* the edited record doesn't go away.

That's only to show my question. Imagine there are several background update queries that is done and you want to also view the results in the form.

 

[MarkK]

Or you can do this, where you save the filtered IDs only to a different table. Then drive the search result form off a query that joins those selected IDs back to the order table. In that case the search results are not actually driven by the filter, they are driven by the IDs that initially matched the filter, like...

 

[KitaYama]

Or you can do this, where you save the filtered IDs only to a different table. Then drive the search result form off a query that joins those selected IDs back to the order table. In that case the search results are not actually driven by the filter, they are driven by the IDs that initially matched the filter, like...

Thanks. I believe it's better than my current method.
Give me a day to test and see how it goes.

Million thanks.

 

[MarkK]

Yeah, you don't want to pollute your Orders table with what is essentially user interface state information. Here's how I'd clean up the code...

 

[KitaYama]

you don't want to pollute your Orders table

That was another reason I was looking for seeing how others may do this.
Just to add to this, (in case someone else looks into this) I have to add a UserFK to your table to be able to use it in a multi-user environment, and possibly a TableName field to be able to use it on different tables.
I can continue from here and do the rest on my own. The idea was just more than enough.

Thanks again.

 

[ebs17]

Most of the times, a search on a table has several parts concatenated by ORs and ANDs.

The moment you edit a record that doesn't satisfy one of those ORs and ANDs, it will disappear from the result. (even if some other part of the filter is still true). I need to have the result on screen, because I have other jobs to do with them, even if one of those ANDs is not true anymore.

For Example you search for all the orders that
(Are not yet delivered) AND (confirmation mail has not been sent)

Processing all the steps obviously takes a long time, beyond a single user session. Therefore, you should also consider whether another user can continue the work of a previous user who is temporarily unavailable due to illness or vacation.

I have various thoughts on this process.

1) You don't have to commit to one filter, you can change them.

condition1 fulfilled or condition2 fulfilled or condition3 fulfilled
- versus -
condition1 fulfilled and condition2 fulfilled and condition3 fulfilled

Here you can differentiate whether an order still has necessary processing steps or is completely finished. You could also define intermediate stages. Filters can be created dynamically or saved in a separate table after they have been created for the first time and then only called up from there.

2) It may be that there are, for example, 10 necessary steps in a fixed sequence (first step smallest key, keys ascending in a fixed order). Then you can document the key in a field of the record and then know, for example, that with a value of 50 the fifth step was carried out. 100 would document the tenth and final step, so everything <100 would have to be resubmitted for further processing.

If you then need more documentation (when was an individual step completed, which user carried it out), you could expand this into a many-to-many relationship (order - processing step). As already indicated above, I would not assign the key for processing steps by auto-value, but rather self-defined for a certain systematic order and with gaps in order to leave this management open for subsequent additions and for a broader application to different things.

 

[gemma-the-husky]

@KitaYama

I've not read the whole thread, but
It's "undesirable/ugly" because it's dealing with records in a non-relational way, and creating special cases.

So what if instead, a user can "save" a record, and the record settings for the current record save into a table with the date, user, and all the details.
now you have a free-standing table where you can select and view previous records for comparison. Maybe just the same user's records, maybe any users records, but now it's more relational and not quite so "undesirable". This idea may have been proposed already.

I use this sort of idea to store modifications to quotes, for example, so a user can see all previous versions of the quote. .

I've looked back, and I think this is similar to what @MarkK suggested.

 

[KitaYama]

It's "undesirable/ugly" because it's dealing with records in a non-relational way

I definitely can say you and Doc have misunderstood my question.
My attached sample file has only one table and 2 fields. How can a table like this be non-relational?
My question has nothing to the table structure. It is only about how to search data, show the result in a form, and not loose the list after an edit.
You may want to see @MarkK's variation too in #10.

Thanks for stepping in though.

 

[cheekybuddha]

I haven't read the whole thread either, but it seems that you don't need to store the ID's of the updated records in a table, since this is essentially ephemeral information for the purpose of immediate display. Just grab the ID's and add them to the filter after the update.

Along the lines of:

Private Sub DoUpdate_Click()

  Dim strUpdatedIDs As String, strCriteria As String
  Const adClipString As Integer = 2
 
' This is the criteria used for your update query 
  strCriteria = " WHERE fld1 = 'x' AND fld2 = 'y'"

' Grab the ID's of the records that will be updated
' use ADODB recordset to easily build them in to a string
  With CurrentProject.Connection.Execute("SELECT ID FROM YourTable" & strCriteria)
    strUpdatedIDs = .GetRowString(adClipString, , , ",")
    .Close
  End With

' Do the update
  CurrentDb.Execute "UPDATE YourTable SET fld1 = 0" & strCriteria, dbFailOnError

' Reset the form's filter - this should effectively requery at the same time
  If Len(strUpdatedIDs) Then
    Me.Filter = Me.Filter & " OR ID IN (" & stUpdatedIDs & ")"
  End If

End Sub

 

[KitaYama]

Just grab the ID's and add them to the filter after the update.

This was one of my first thought, but because of my lack of information on sql, I gave up this method.

The tables have more than several million records. Imagine a user seraches and the result is 100 records.
It means I have 100 IDs x 6 digit for each ID, plus 99 ",".
So my WHERE clause would be some 700 characters, I really don't know if it's possible or not, or even if it is, will I have any performance issus or not.
That was the main reason to go to adding the field and updating it, (later trying markk's suggestion by saving IDs in a table.

 

[cheekybuddha]

So my where clause would be some 700 characters, I really don't know if it's possible or not, or even if it is, will I have any performance issus.

Perhaps give it a try?

( I doubt it will have massive performance issues, but you never know. At least the ID's are indexed)

 

[KitaYama]

Perhaps give it a try?

This weekend for sure.

thanks.

 

[KitaYama]

Just checking before experimenting.

According to ChatGPT :

• The maximum length of an SQL statement in Microsoft Access is 64,000 characters.
  This limit applies to the total number of characters in the SQL statement, including spaces, comments, and formatting.
• Very long WHERE clauses in an SQL statement can affect performance, especially in databases like Microsoft Access.
• Complex and lengthy WHERE clauses can make it harder for the optimizer to generate an efficient execution plan.
• Very long or complex WHERE clauses can sometimes prevent the effective use of indexes.
• In summary, while the performance impact of a long WHERE clause can vary depending on the specific query and database structure, it's generally advisable to keep queries as simple and efficient as possible.

 

[The_Doc_Man]

My thoughts in the response you didn't like were not about relational structure (re: comments in your post #14 of this thread), but were instead about functional design. I suggested one way that might do it, and in fact you commented in post #5 that you were doing something similar anyway. But since you still had the problem then my thoughts might still be relevant.

You described an Access behavior that you didn't like, but that behavior is correct for Access. It occurs because the design of your app didn't take into account that an action would cause your desired record to "fall off the form" before you were ready for it to do so. My suggestion was for you to include a way to include one extra selection (filter?) criterion in order to allow you to keep that record "current" because it appeared that you were talking about retrofitting a form that DIDN'T keep that record current.

This is a design issue (I'll be kind and call it an oversight) that, to fix it, needed something to change, and I made a suggestion on how you might change it. You didn't like a Y/N field and that's OK. There are other ways to approach it. You might have a control to hold the record ID that you wanted to keep even though the other criteria would disqualify it. But on further examination, you have a serious logic issue here that might require an IIF or IF function in the SQL - and it WOULD be awkward or ugly.

If you want to retain record X even after its content has been edited in a way that would no longer match your basic WHERE clause, you need to make that WHERE clause have two distinct branches. It would not be enough to simply add a control to retain the record ID of what you wanted to keep and add a clause "... OR [RecordID]=[Forms]![some-form-name].[some-control-name] ...". You could still lose the record you wanted since other records might still match the other part of the WHERE clause with that simple OR structure. You need something like this (as VERY generic code that depends on which SQL you are actually using):

"SELECT .... FROM .... WHERE IF( control name <> 0, [RecordID]= control name, all other criteria )..."

Then when you are ready to release the desired record you have to take an affirmative action to reset the special control name to 0. This structure is what you want because you actually have a strict dichotomy of records you want to keep. EITHER you want one specific record or you want any record from a bunch of records.