Solved save query results to use even after criteria changes

[Gasman]

Okay, sorry. I thought you were kidding. dbOpenSnapshot creates a "read-only" recordset. So, you won't be able to make changes to the data in the recordset. As far as "viewing" the externaly changed data, the result is the same. The read-only recordset keeps the old data and does not display the new one.

So would that option solve the o/p's problem? Worth a try perhaps?

 

[theDBguy]

So would that option solve the o/p's problem? Worth a try perhaps?

That wasn't the question. It wasn't about changing the data. It was about, I think, the number of records were shrinking because when the data is changed, the OP thought the other records, originally included in the recordset, are then removed before they could be processed. At least that's how I understood the concern presented.

 

[Cronk]

YYYY wrote: there is a field in the query that looks up [using dlookup or using a subquery] the total of that donor that did not get a receipt yet, and every time a receipt number is assigned to a donation, the total field changes in the recordset.

DBguy got it right in #2. Two recordsets are needed. First one (the outer loop) will contain only one instance of each person making a donation. The second recordset (the inner loop) would loop through a records containing all donations for the particular person in the outer group.

I'd put the total calculation in the code procedure, not the query.

 

[YYYY]

Here's what I did, which you should be able to copy.

1. I opened a recordset based on a query with a criteria. This gives me not all the records from the table.
2. I navigated the recordset to the last record and then printed the RecordCount to the Immediate Window to verify I have the correct number of records from the query
3. Next, I executed an UPDATE query to change only one record in the table to exclude it from the query criteria (in essence, I should have 1 less record from the original result of the query)
4. I navigated the recordset to the first record and then to the last record (this is just to make sure I still get the correct recordcount of the recordset)
5. When I printed the recordcount to the Immediate Window again after the above steps, I still get the same number as a the result from step 2. In other words, if I started with 10 records and updated the table to make it 9, the recordset still has the original 10 records in memory. Therefore, it doesn't reset automatically.

DBguy got it right in #2. Two recordsets are needed. First one (the outer loop) will contain only one instance of each person making a donation. The second recordset (the inner loop) would loop through a records containing all donations for the particular person in the outer group.

I'd put the total calculation in the code procedure, not the query.

thanks!

 

[theDBguy]

thanks!

Let us know how it goes.

 

[Gasman]

That wasn't the question. It wasn't about changing the data. It was about, I think, the number of records were shrinking because when the data is changed, the OP thought the other records, originally included in the recordset, are then removed before they could be processed. At least that's how I understood the concern presented.

That is what was confusing me. I would have expected when the recordset was opened that the query supporting it was run. After that it would not run again, even if data supporting that query was changed by the recordset? If it was like it was having some sort of chain reaction.

This is all for my benefit, not really helping the o/p, so I will not ask anymore questions.

 

[YYYY]

DBguy got it right in #2. Two recordsets are needed. First one (the outer loop) will contain only one instance of each person making a donation. The second recordset (the inner loop) would loop through a records containing all donations for the particular person in the outer group.

I'd put the total calculation in the code procedure, not the query.

its more complex then that,
because only specific donation are included in the filter string created by the forms textboxes.
[date range, amount, cause of donation]

What I did in the end is,
I added a Yes/No field CreateReceiptSelect to the Donations table
and divided the code in 2
the first part changes the CreateReceiptSelect field to true for all Donations in query filtered by the FilterString.
the 2nd part loops thru all records with CreateReceiptSelect field = true
and adds a receipt number according to the SetUp Type selected in Form.
the first part of code starts of by changing the CreateReceiptSelect field to false for all Donations.

Thank You every one
especially DBGuy
for the help & encouragement!!

 

[theDBguy]

its more complex then that,
because only specific donation are included in the filter string created by the forms textboxes.
[date range, amount, cause of donation]

What I did in the end is,
I added a Yes/No field CreateReceiptSelect to the Donations table
and divided the code in 2
the first part changes the CreateReceiptSelect field to true for all Donations in query filtered by the FilterString.
the 2nd part loops thru all records with CreateReceiptSelect field = true
and adds a receipt number according to the SetUp Type selected in Form.
the first part of code starts of by changing the CreateReceiptSelect field to false for all Donations.

Thank You every one
especially DBGuy
for the help & encouragement!!

Hi. Congratulations! Glad to hear you got it sorted out. It's hard, sometimes, to figure out something when we can't see it. Good luck with your project.

 

[Cronk]

I too am glad you got it going. However, whatever your filter was, you could have opened a recordset based on that filter.