Solved save query results to use even after criteria changes (1 Viewer)

Y

YYYY

Member
Local time
Today, 15:02
Joined
Apr 21, 2020
Messages
39
I"m using a Recordset to select records to be updated using Do ANd Loop
The Recordset gets the records based on a complex Query.
the problem is that each time the loop statment is passed - it refreshes the query criteria and some records previously part of this recordset waiting to be updated are omitted.

is there a way to create the recordset and get its data without being changed till all updates are made?
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 12:02
Joined
Oct 29, 2018
Messages
21,536
Hi. Sounds like you need two recordsets instead of one.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 12:02
Joined
Oct 29, 2018
Messages
21,536
Y

YYYY

Member
Local time
Today, 15:02
Joined
Apr 21, 2020
Messages
39
Code: 
Dim RCPTSetUp, FilterString As String
    RCPTSetUp = Me.ReceiptSetUp

  FilterString = " WHERE "
          'good trick from DBGuy to check if textbox is null or empty string. it adds an empty string to the textbox, then checks if the total = empty string.
          If Me.FundFilterAndGroup & "" <> "" Then
          FilterString = FilterString & "FundId = " & Me.FundFilterAndGroup & " And "
          Else
          End If
         
          If Me.ContactFilter & "" <> "" Then
          FilterString = FilterString & "ContactID =" & Me.ContactFilter & " And "
          Else
          End If
         
          If Me.AmountFilter & "" <> "" Then
          'sets the amount filter according to the set up chosen,
             If RCPTSetUp = 1 Then
             FilterString = FilterString & "AdditionAmount >= " & Me.AmountFilter & " And "
                ElseIf RCPTSetUp = 2 Then
                FilterString = FilterString & "RunningContactTotalDates >= " & Me.AmountFilter & " And "
                  ElseIf RCPTSetUp = 3 Then
                  FilterString = FilterString & "RunningFund_ContactTotalDates >= " & Me.AmountFilter & " And "
          Else
          End If
         
          If Me.FromFilter & "" <> "" Then
          FilterString = FilterString & "TransactionDate >= #" & Me.FromFilter & "# And "
          Else
          End If
         
          If Me.ToFilter & "" <> "" Then
          FilterString = FilterString & "TransactionDate <= #" & Me.ToFilter & "# And "
          Else
          End If
         
End If
Me.QueryString = FilterString
        'cut of the extra " And " at the end of the filterstring, use CutEndOfString Function created by YitzchokKupetz DataCharts 4/30/20
        FilterString = CutEndOfString(FilterString, 5)
              'check if filterstring is empty but has the word "WHERE", make it null.
              If FilterString = "Where" Then
              FilterString = Null
              End If
              Me.QueryString = FilterString
    ' If an error occurs, jump down to the DataAccessError section.
    On Error GoTo DataAccessError

    ' Create a SELECT command.
    Dim Query1
    Query1 = "SELECT TDID, ContactID, TransactionDate, AdditionAmount, FundId, SpecialReceipt, RunningFund_ContactTotalDates, RunningContactTotalDates, ReceiptNumber From ReceiptsToCreateTotals_Dates " _
    & FilterString _

    'for set up and testing only - shows the Query String in text box on form
    Me.QueryString = Query1
    ' Get a recordset using this command.
    Dim rs
    Set rs = fDAOGenericRst(Me.QueryString)
   
 Dim ReceiptNum, DonationID, Donor, Fund, Special
       ReceiptNum = DLookup("startNumber", "ReceiptNumbers")
       DonationID = rs("TDID")
       Donor = rs("ContactID")
       Fund = rs("FundID")
       Special = rs("SpecialReceipt")
    ' Move through the recordset, looking at each record.
    ' Each record is a separate Donation.
    Do Until rs.EOF
    'code for Seperate receipts for each fund for each contact

        If rs!ContactID <> Donor Or rs!FundId <> Fund Then
        ReceiptNum = ReceiptNum + 1
        End If
     
    
        ' For each Donation, get the Donation ID.
      'This is needed in order that the update command shouldn't update all records at once
      'after creating this variable we will be able to add cretiria to the  update  command to only update this individuale record
      'and on the next time  around the  next record
     
        DonationID = rs("TDID")
        Donor = rs("ContactID")
        Fund = rs("FundID")
        Dim UpdateCommand
        UpdateCommand = "UPDATE TransactionDetails SET ReceiptNumber =  ' " & ReceiptNum & " ' " & "WHERE TDID =" & DonationID
        ' Run the command.
        CurrentDb.Execute UpdateCommand
       
        ' Move to the next Donation (if there is one).
        rs.MoveNext
       
    Loop
   
    rs.Close
    CurrentDb.Close

    Exit Sub


DataAccessError:

    ' You only get here if an error occured.
    ' Show the error.
    MsgBox Err.Description
   
    ' Try to clean up.
    On Error Resume Next

    'Recordset.Close
    'CurrentDb.Close

End Sub
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 12:02
Joined
Oct 29, 2018
Messages
21,536
YYYY said:
Code: 
Dim RCPTSetUp, FilterString As String
    RCPTSetUp = Me.ReceiptSetUp

  FilterString = " WHERE "
          'good trick from DBGuy to check if textbox is null or empty string. it adds an empty string to the textbox, then checks if the total = empty string.
          If Me.FundFilterAndGroup & "" <> "" Then
          FilterString = FilterString & "FundId = " & Me.FundFilterAndGroup & " And "
          Else
          End If
         
          If Me.ContactFilter & "" <> "" Then
          FilterString = FilterString & "ContactID =" & Me.ContactFilter & " And "
          Else
          End If
         
          If Me.AmountFilter & "" <> "" Then
          'sets the amount filter according to the set up chosen,
             If RCPTSetUp = 1 Then
             FilterString = FilterString & "AdditionAmount >= " & Me.AmountFilter & " And "
                ElseIf RCPTSetUp = 2 Then
                FilterString = FilterString & "RunningContactTotalDates >= " & Me.AmountFilter & " And "
                  ElseIf RCPTSetUp = 3 Then
                  FilterString = FilterString & "RunningFund_ContactTotalDates >= " & Me.AmountFilter & " And "
          Else
          End If
         
          If Me.FromFilter & "" <> "" Then
          FilterString = FilterString & "TransactionDate >= #" & Me.FromFilter & "# And "
          Else
          End If
         
          If Me.ToFilter & "" <> "" Then
          FilterString = FilterString & "TransactionDate <= #" & Me.ToFilter & "# And "
          Else
          End If
         
End If
Me.QueryString = FilterString
        'cut of the extra " And " at the end of the filterstring, use CutEndOfString Function created by YitzchokKupetz DataCharts 4/30/20
        FilterString = CutEndOfString(FilterString, 5)
              'check if filterstring is empty but has the word "WHERE", make it null.
              If FilterString = "Where" Then
              FilterString = Null
              End If
              Me.QueryString = FilterString
    ' If an error occurs, jump down to the DataAccessError section.
    On Error GoTo DataAccessError

    ' Create a SELECT command.
    Dim Query1
    Query1 = "SELECT TDID, ContactID, TransactionDate, AdditionAmount, FundId, SpecialReceipt, RunningFund_ContactTotalDates, RunningContactTotalDates, ReceiptNumber From ReceiptsToCreateTotals_Dates " _
    & FilterString _

    'for set up and testing only - shows the Query String in text box on form
    Me.QueryString = Query1
    ' Get a recordset using this command.
    Dim rs
    Set rs = fDAOGenericRst(Me.QueryString)
   
 Dim ReceiptNum, DonationID, Donor, Fund, Special
       ReceiptNum = DLookup("startNumber", "ReceiptNumbers")
       DonationID = rs("TDID")
       Donor = rs("ContactID")
       Fund = rs("FundID")
       Special = rs("SpecialReceipt")
    ' Move through the recordset, looking at each record.
    ' Each record is a separate Donation.
    Do Until rs.EOF
    'code for Seperate receipts for each fund for each contact

        If rs!ContactID <> Donor Or rs!FundId <> Fund Then
        ReceiptNum = ReceiptNum + 1
        End If
     
    
        ' For each Donation, get the Donation ID.
      'This is needed in order that the update command shouldn't update all records at once
      'after creating this variable we will be able to add cretiria to the  update  command to only update this individuale record
      'and on the next time  around the  next record
     
        DonationID = rs("TDID")
        Donor = rs("ContactID")
        Fund = rs("FundID")
        Dim UpdateCommand
        UpdateCommand = "UPDATE TransactionDetails SET ReceiptNumber =  ' " & ReceiptNum & " ' " & "WHERE TDID =" & DonationID
        ' Run the command.
        CurrentDb.Execute UpdateCommand
       
        ' Move to the next Donation (if there is one).
        rs.MoveNext
       
    Loop
   
    rs.Close
    CurrentDb.Close

    Exit Sub


DataAccessError:

    ' You only get here if an error occured.
    ' Show the error.
    MsgBox Err.Description
   
    ' Try to clean up.
    On Error Resume Next

    'Recordset.Close
    'CurrentDb.Close

End Sub
Click to expand...
Hi. Thanks for posting the code. Now please tell us what's happening and what's supposed to happen instead.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 12:02
Joined
Oct 29, 2018
Messages
21,536
theDBguy said:
Hi. Thanks for posting the code. Now please tell us what's happening and what's supposed to happen instead.
Click to expand...
By the way, it's good practice to add Option Explicit at the top of your modules (if you haven't already).
 
Y

YYYY

Member
Local time
Today, 15:02
Joined
Apr 21, 2020
Messages
39
theDBguy said:
Hi. Thanks for posting the code. Now please tell us what's happening and what's supposed to happen instead.
Click to expand...
The goal is to assign Receipt Numbers to donations
With the option of combining donations and only printing receipts if total = $xx.xx or more.
the query does just that it selects all donations that don't have a receipt number, that their donor's total donation amount = $xx.xx or more
the problem is when it loops thru the records and assigns receipt numbers to donations, the total of the remaining records aren't totaling the amount selected, so they are omitted from the query results and from the update.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 12:02
Joined
Oct 29, 2018
Messages
21,536
YYYY said:
The goal is to assign Receipt Numbers to donations
With the option of combining donations and only printing receipts if total = $xx.xx or more.
the query does just that it selects all donations that don't have a receipt number, that their donor's total donation amount = $xx.xx or more
the problem is when it loops thru the records and assigns receipt numbers to donations, the total of the remaining records aren't totaling the amount selected, so they are omitted from the query results and from the update.
Click to expand...
Okay, so assuming "rs" contains the records/donations without receipt numbers. You loop through the records using Do Until rs.EOF. For each record, you check something to increment ReceiptNum. Also, you update a table to store the value of ReceiptNum. Is this table the same as the one from the query? Assuming I understood the above code, that means your recordset might contain more than one record and it should update all of them. Correct? But, are you saying the recordset might contain more than one record but only updates one or some of them? If so, maybe the next thing we'll need is a demo copy of your db with test data. Make sure it has some records that meet the criteria and will be included in the updates.
 
Y

YYYY

Member
Local time
Today, 15:02
Joined
Apr 21, 2020
Messages
39
theDBguy said:
Okay, so assuming "rs" contains the records/donations without receipt numbers. You loop through the records using Do Until rs.EOF. For each record, you check something to increment ReceiptNum. Also, you update a table to store the value of ReceiptNum. Is this table the same as the one from the query?
Click to expand...
no.
theDBguy said:
Assuming I understood the above code, that means your recordset might contain more than one record and it should update all of them. Correct?
Click to expand...
yes.
theDBguy said:
But, are you saying the recordset might contain more than one record but only updates one or some of them?
Click to expand...
yes.

Do all Dim 's create a temporary storage with data? it looks like this Variable- Recordset changes every time the underlying data is changed,
which if i understand correctly dose not occur with Dim As String etc.
I saw there a few types of recordsets out there maybe i need an other type?
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 12:02
Joined
Oct 29, 2018
Messages
21,536
YYYY said:
no.

yes.

yes.

Do all Dim 's create a temporary storage with data? it looks like this Variable- Recordset changes every time the underlying data is changed,
which if i understand correctly dose not occur with Dim As String etc.
I saw there a few types of recordsets out there maybe i need an other type?
Click to expand...
Hi. I just tested it. The recordset doesn't "shrink" when you change the data in it; however, it does reflect the new data. For example, if you create a recordset with 10 records in it and then modify one of the records that would eliminate it from the recordset in the first place, it will still stay within the recordset until you reset the recordset.
 
Y

YYYY

Member
Local time
Today, 15:02
Joined
Apr 21, 2020
Messages
39
theDBguy said:
Hi. I just tested it. The recordset doesn't "shrink" when you change the data in it; however, it does reflect the new data. For example, if you create a recordset with 10 records in it and then modify one of the records that would eliminate it from the recordset in the first place, it will still stay within the recordset until you reset the recordset.
Click to expand...
how can i test it by me?
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 12:02
Joined
Oct 29, 2018
Messages
21,536
YYYY said:
how can i test it by me?
Click to expand...
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.
 
Y

YYYY

Member
Local time
Today, 15:02
Joined
Apr 21, 2020
Messages
39
theDBguy said:
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.
Click to expand...
by me it definitely realizes the differnce, could be because by me the way it works is,
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. I don't know a way around this.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 12:02
Joined
Oct 29, 2018
Messages
21,536
YYYY said:
by me it definitely realizes the differnce, could be because by me the way it works is,
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. I don't know a way around this.
Click to expand...
You might consider posting a demo copy of your db with test data showing the problem.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 20:02
Joined
Sep 21, 2011
Messages
14,445
theDBguy said:
Hi. I just tested it. The recordset doesn't "shrink" when you change the data in it; however, it does reflect the new data. For example, if you create a recordset with 10 records in it and then modify one of the records that would eliminate it from the recordset in the first place, it will still stay within the recordset until you reset the recordset.
Click to expand...
@theDBguy What would happen if you used dbOpenSnapShot ?
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 20:02
Joined
Sep 21, 2011
Messages
14,445
No seriously I do not. That is why I asked? I can guess, hence my question, but TBH I thought the query would just read the data, and even if any data was changed, you would need to rerun the query to get the changes? So in reverse, once you read the data, that would remain static?
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 12:02
Joined
Oct 29, 2018
Messages
21,536
Gasman said:
No seriously I do not. That is why I asked? I can guess, hence my question, but TBH I thought the query would just read the data, and even if any data was changed, you would need to rerun the query to get the changes? So in reverse, once you read the data, that would remain static?
Click to expand...
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.
 
You must log in or register to reply here.

Similar threads

T
Solved Using multiple criteria in DCOUNT function using a comparison
Replies
1
Views
212
June7
June7
H
Using a query that creates a random result as the criteria for the a field in another table
Replies
3
Views
342
ebs17
E
B
VBA problem relating to SQL linked table
Replies
6
Views
649
CJ_London
C
J
error in procedure
Replies
12
Views
382
Pat Hartman
P
B
Retrieve 6 numbers from string
Replies
2
Views
299
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom