Solved Freeze the recordset of a search form

[KitaYama]

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.

Thanks for the details. But I need a little time to digest them and relate your explanation to my problem.
I really appreciate your help and time.

Million thanks.

 

[KitaYama]

You described an Access behavior that you didn't like, but that behavior is correct for Access.

I re-read your reply several times, and if I understand it correctly, I still think I wasn't able to explain my case.
As I explained that question was translated by Google, So there may be some misunderstanding here.

There's nothing that I don't like about Access, neither I said I think that behaviour is not correct.
I know how Access works and I'm sure that behaviour is correct. I also don't want it to behave differently.

Here's what I want, Maybe in a simpler way.

1. I want to filter a table and receive the result of my search. let's say the result is 10 record.
2. Edit a record in the result that makes this record out of the scope of filter (Not Delivered ----> Changes to ----> Delivered)
3. Now the result of the search is 9 records.
4. Change the filter of the form and show the previous 10 records.

The solution I used :
Added a field to the table to track which records initially were found (adding userPK to this field)
Steps for this :

1. Result forms opens.
2. It has 10 records.
3. Add UserPK to this additional field by an update query.
4. Now when the records is edited and the result of the first filter is 9, I change the filter to see the previous 10 records.

I asked if anyone has a better idea.

Markk suggested to use a table and save the IDs in a new table (instead of a field)
Cheekybuddha suggested to simply create a list of IDs and reload the previous 10 records by these IDs.

I'm really sorry if you are telling me the same thing and I don't understand.
I need to learn English better.

Thanks again.

 

[MajP]

Like @cheekybuddha suggest but with a slight modification. I only save the IDs of those records that have been modified and they modify a field affected in the filter. So even if you return thousands of records your string only grows by those IDs that would drop out of the filter.

Private m_Filter As String
Private RemovedIDs As String
Const tbl As String = "Orders"

Public Property Let fltr(str As String)
    m_Filter = str
End Property
Public Property Get fltr() As String
    If RemovedIDs = "" Then
      fltr = m_Filter
    Else
      fltr = m_Filter & " OR ORDERID IN (" & RemovedIDs & ")"
    End If
End Property
Public Sub SetFormRs()
   Me.RecordSource = "SELECT * FROM " & tbl & " WHERE " & fltr
End Sub

Private Sub btnCloseMe_Click()
    UpdateSearchedBy tbl, fltr, "Remove"
    DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_AfterUpdate()
    SetFormRs
End Sub
Public Function Infilter()
  If InStr(Me.fltr, Me.ActiveControl.ControlSource) > 0 Then
    AddToRemoved Me.OrderID
  End If
End Function
Public Sub AddToRemoved(ID As Long)
  If RemovedIDs = "" Then
    RemovedIDs = ID
  Else
    RemovedIDs = RemovedIDs & "," & ID
  End If
End Sub

 

[gemma-the-husky]

I think you could have a table field called "selected", but if you have a million records you would have to update all of the records to false, then update the current records to be saved to true.

Also you might then have problems if multiple users were doing the same thing simultaneously.

Do you need to be able to distinguish the "normal" records from the "preserved" records?

 

[cheekybuddha]

I think you could have a table field called "selected"

This is not a good idea - this is UI issue and the selected field does not belong with the data in tables.

My suggestion is the same as @MajP's, except that I hadn't read the original post carefully enough; I thought the update was being performed on multiple records at the same time via an UPDATE query, rather than by editing individual records.

Only the ID's of the edited records need be added to the form filter to make sure they're included when the filter criteria would otherwise filter them out. It's a very small number, and can be done as each record is edited.

Once the user re-filters the form with different criteria, those ID's can be discarded because they are now stale info.

 

[cheekybuddha]

You may want to add code to @MajP's suggestion to ensure you remain on the correct record after re-setting the form's RecordSource.

 

[KitaYama]

I think you could have a table field called "selected",

As I explained in #1, I have a field named SearchedBy, to use for this purpose.

but if you have a million records you would have to update all of the records to false

No, I don't need to do so. When user searches, an update query populates SearchedBy fields with userPK. If the search returns 10 records, these 10 records' SearchedBy field is populated with UserPK. When user closes the form, an update query deletes UserPK from the same 10 records.
So the update will be done, only on the count of the result.

Also you might then have problems if multiple users were doing the same thing simultaneously.

Not really. Because the field SearchedBy is populated with UserPK. So different users can have the same record in their forms. At some point, SearchedBy field of a record maybe something like : ,30,100,85,
This means UserPK 30 & 100 & 85 have searched for the same record and have it in their forms. If for example user 85 closed the form, the update query deletes 85 and it becoms ,30,100,
If you download the file in #1, you'll see how I've done it.

Do you need to be able to distinguish the "normal" records from the "preserved" records?

No. As far as they are included, that's fine.

except that I hadn't read the original post carefully enough; I thought the update was being performed on multiple records at the same time via an UPDATE query, rather than by editing individual records.

I noticed that, but I saw where you heading. The idea was clear and easy to understand.

@MajP It's 3 in the morning and too late to test your solution. I'll check it first thing in the morning.
I really appreciate your time and help.

Thanks to all for the help and advices.

 

[MajP]

Here is the demo with @cheekybuddha recommendation to remain on the edited record.

 

[KitaYama]

@MajP
This morning, going through your code in #23, I realized the depth of my stupidity. I was so close to the goal, and never thought of what you did.
What you did there is simply impressive.

I really appreciate your help.
Million thanks.

I think I have the best possible way to do what I was looking for. No updates, no additional fields, no side database.
Thanks again.

 

[MajP]

What you did there is simply impressive.

I do not know about that, but sometimes you do not see the forest because of the trees and you need a fresh set of eyes to find a simpler approach. This idea is probably based on a similar idea that @CJ_London used in this thread to create checkboxes on a continuous form

Manipulating an independent check box field in a form

Hello! I have a form that its details is fill with fields from a query. I would like to have an independent check box field for each detail that I would like to change. I create the field in the form but when I change it on one record it chages all the records. I am thinkig to create a temporal...

www.access-programmers.co.uk

 

[gemma-the-husky]

As I explained in #1, I have a field named SearchedBy, to use for this purpose.


No, I don't need to do so. When user searches, an update query populates SearchedBy fields with userPK. If the search returns 10 records, these 10 records' SearchedBy field is populated with UserPK. When user closes the form, an update query deletes UserPK from the same 10 records.
So the update will be done, only on the count of the result.



Not really. Because the field SearchedBy is populated with UserPK. So different users can have the same record in their forms. At some point, SearchedBy field of a record maybe something like : ,30,100,85,
This means UserPK 30 & 100 & 85 have searched for the same record and have it in their forms. If for example user 85 closed the form, the update query deletes 85 and it becoms ,30,100,
If you download the file in #1, you'll see how I've done it.



No. As far as they are included, that's fine.


I noticed that, but I saw where you heading. The idea was clear and easy to understand.

@MajP It's 3 in the morning and too late to test your solution. I'll check it first thing in the morning.
I really appreciate your time and help.

Thanks to all for the help and advices.

Good points about the search by field.

So you get your query by simply adding "searchby = " & currentuser , slightly modified depending whether you have a string or a number for the currentuser. Why do you need to add all the record IDs manually ?

It's just another row (or) in the access query pane, isn't it?

 

[KitaYama]

Why do you need to add all the record IDs manually ?

Please see #22. I explained there what I have done. Or download the file in #1 to see how it works.

User searches. I assign the filter string to a property of form to use it later.
I use this filter and update the table (only the result of search, Not all record):

CurrentDb.Execute "Update Table Set SearchedBy = SearchedBy & " & """," & UserPK & ","" WHERE " & userFilter

This simple line of code, add's userPK to SearchedBy field.
Then, I open a form to show the search result.

Docmd.OpenForm "myForm",,,"SearchedBy=," & userPK & ","

So, even a field is edited, the record stays in the form.
When I close the form, I clear the SearchedBy

CurrentDb.Execute "Update Table Set SearchedBy = Replace(SearchedBy,'" & User & "','') WHERE " & userFilter

Note: Now I don't user this method anymore. @MajP suggestion is much better than this method.
He simply adds the PK of the record to the filter (cancatenated by OR)

 

[KitaYama]

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

@cheekybuddha
Though @MajP solved my problem, But I didn't understand your code at the time. Too busy with my job and wasn't able to ask sooner.
Now its the weekend and I think it's never too late to learn something new.

I couldn't find any tutorial/documents on .GetRowString you suggested above.
Asked ChatGPT, and the answer was neither vba, nor sql server nor management studio, comes with any native .GetRowString function.
Can you explain a little bit on this? or maybe some links to tutorials or documentation please?

I really appreciate it.
Thanks.

 

[cheekybuddha]

Sorry, it should have been GetString(), not GetRowString()

Access (probably for legacy reasons) maintains its own ADO internal reference, so you can use the CurrentProject.Connection (which is an ADODB.Connection object) without setting an explicit reference.

When used with a SELECT sql query, the ADODB.Connection.Execute() method will return a read-only, forward-only ADODB recordset.

ADODB recordsets have a useful method GetString() which returns a delimited string of the recordset's results (in addition to GetRows() which returns an array).

DAO recordsets have an equivalent of GetRows() but not GetString().

So the code I posted (if I had named the method correctly ) uses the ADODB CurrentProject.Connection.Execute to open a recordset returning just the ID's based on your criteria and quickly build them in to a comma delimited string.

(The GetString method requires passing of the constant value adClipString (=2), but since it's unlikely you have an ADO reference set, I declared the constant locally)

Then it builds the UPDATE query using the same criteria and executes using DAO this time, since apparently it may be [imperceptibly] faster than ADO!

Once the update has been performed it modifies the form's Filter property to also include the ID's that would no longer be returned as a result of the update.

I hope I have explained that clearly!