VBA Loop to update recordset only reaches 1st record

[BJF]

Hi,
i found this code and have substituted parameters to suit my own needs however the loop is not working. Only the first record in my recordset (which is a test recordset of only 3 records) is being updated.

also, for testing only, the edit or update being applied is trivial: Description = "WHITE RESIN". If i can get the loop to work I want to substitute higher functionality to the module.

Please help, I basically understand when i read through the code but It just wont work yet.




Private Sub Update_Click()

Dim dbs As DAO.Database
Dim rsQuery As DAO.Recordset

Set dbs = CurrentDb

Set rsQuery = dbs.OpenRecordset("qryRmResin", dbOpenDynaset)

'Check to see if the recordset actually contains rows
If Not (rsQuery.EOF And rsQuery.BOF) Then
rsQuery.MoveFirst 'Unnecessary in this case, but still a good habit
Do Until rsQuery.EOF = True
'Perform an edit

Description = "WHITE RESIN"

'Move to the next record. Don't ever forget to do this.
rsQuery.MoveNext

Loop

Else
MsgBox "There are no records in the recordset."
End If

MsgBox "Finished looping through records."

rsQuery.Close 'Close the recordset
Set rsQuery = Nothing 'Clean up

End Sub



Thanks,
BJF

 

[pbaldy]

The loop is working fine.

To update records of the recordset:

rsQuery.Edit
rsQuery!Description = "WHITE RESIN"
rsQuery.Update

 

[BJF]

Thanks for responding Paul,

Do you mean I need to add this to the existing code?

 

[pbaldy]

Those 3 lines would replace this one:

Description = "WHITE RESIN"

That one would be changing the value on the form, not the recordset.

 

[BJF]

Paul thanks very much,

that worked for me, however what i am trying to actually do is not working:

i am needing to call an event which does some math and it doesnt seem to be happening:

rsQuery.Edit
Call CalculateStdCost_Click
rsQuery.Update


is there other syntax i need to use to use the 'call event'


This is what im calling which works unto itself for single records:

Public Sub CalculateStdCost_Click()

If IsNull(R1) Then Exit Sub Else ResinCostWithFreight = (R1.Column(2) * (PercentR1)) + Nz((R2.Column(2) * (PercentR2)), 0)

End Sub

 

[pbaldy]

Well, you're mixing apples and oranges. That code uses data off the form, the recordset uses data from the query. If the query contains the relevant data, you wouldn't call the event, you'd recreate it using data from the recordset, like:

rsQuery!Description = (rsQuery!OneField * (rsQuery!PercentR1)) + Nz((rsQuery!OtherField * (rsQuery!PercentR2)), 0)

Which includes a lot of "pseudo-code", but hopefully demonstrates getting values from the recordset rather than the form.

 

[BJF]

I'm definitely confused now-

I sort of understand.

I thought i needed to update my records through my form because the functionality i want to perform on all records as a global update is the same functionality that i can perform on any single record while in the form. Are you telling me that i need to update my records with a query instead? Can i post my test db to you so you can see what i am trying to do?

 

[BJF]

Paul, thanks so much for enlightening me to this:

I understand what i need to do now and got some functionality going but how do I make the update conditional?

I changed the code so far to:

rsQuery.Edit
rsQuery!ResinCostWithFreight = Nz((R1.Column(2) * (PercentR1)), 0) + Nz((R2.Column(2) * (PercentR2)), 0)
rsQuery.Update

but i need to make that a conditional statement based on if a yes/no field is true or not?

please help!
Thanks,
BJF

 

[pbaldy]

You understand that's going to use values from the form in the formula, not the query? That means each update will use the same values. To make it conditional you'd wrap those 3 lines in an If/Then block, but I'm not clear on whether you're testing a value from the form or from the recordset.

To address the earlier comment, if you want user input or whatever on the data, I'd use a form where they can change the data. If you want to do it without user interaction, I wouldn't use a form.

 

[BJF]

I'm not sure what the difference would be, isnt my form based on the recordset?

I think i understand where you are getting confused with what i am doing: To clarify hopefully!....

The form will be used to input Resin data along with pricing
Some records(Blends) will be conglomerates of other records (single resins)

I am trying to run code at the press of a button on the form that will cycle through and update all records in the table because when the price of a single resin changes it will affect the cost of a blend resin . However this wont happen automatically because the form involves percentages of each resin in order to recalculate the total. After the user inputs the proper percentages of resins involved in the blend, they must click a button which recalculates the total.

I was trying to trigger this event of recalculation for all records in the recordset at the press of an Update button.

 

[pbaldy]

I'm not sure what the difference would be, isnt my form based on the recordset?

To avoid confusion let's use the word data instead of recordset when referring to the data. In that regard, the form and the recordset are both looking at the same data, yes. However, they are 2 separate "windows" on that data. When you work with the recordset and do this:

rsQuery.MoveNext

you are advancing the recordset to the next record, but you are NOT changing the record displayed on the form, thus you are NOT changing what record the form references in your formula are referring to. That's why when you had this:

Description = "WHITE RESIN"

I said the loop was working fine. What was happening is that the same record kept getting updated in the loop; the record displayed on the form.

 

[BJF]

Hi Paul,

Thanks for all the replies.
I think I understand what you are saying, but I did get the desired result after changing my original code to what you added

rsQuery.Edit
rsQuery!ResinCostWithFreight = Nz((R1.Column(2) * (PercentR1)), 0) + Nz((R2.Column(2) * (PercentR2)), 0)
rsQuery.Update

now my only problem seems to be how to specify to apply the change to only certain records which have a field checked.

I dont understand what syntax to use. The above code in my loop is changing all records but I only want it to to the line:

rsQuery!ResinCostWithFreight = Nz((R1.Column(2) * (PercentR1)), 0) + Nz((R2.Column(2) * (PercentR2)), 0)

when a field I have called 'blendcheck' = true

can I add that condition to that particular line of code?

I tried:

If blendcheck = true then rsQuery!ResinCostWithFreight = Nz((R1.Column(2) * (PercentR1)), 0) + Nz((R2.Column(2) * (PercentR2)), 0)

but to no avail.

How do I accomplish this?

thanks,
BJF

 

[BJF]

also after putting the code in as you suggested, it did not change the records until I closed and reopened the form but thats OK for now.

I just want to change all the data on records based on the condition if blendcheck = true

I'm not sure where to put this condition.

 

[pbaldy]

Presuming it's a field in the recordset rather than the form (in other words it may be different for each record in the query):

If rsQuery!blendcheck = true Then
  rsQuery.Edit
  rsQuery!ResinCostWithFreight = Nz((R1.Column(2) * (PercentR1)), 0) + Nz((R2.Column(2) * (PercentR2)), 0)
  rsQuery.Update
End If

 

[pbaldy]

To update the form:

Me.Requery

at the end

 

[BJF]

Hi Paul,
thanks for all your help

I think this may be what im looking for but im away from my desk until tomorrow
I wll apply the code in the morning and get back to you

Im still familiarizing myself with addressing the query in code as youve shown me.
makes sense much more now.

Thanks again
BJF

 

[BJF]

Paul I replied but dont see it here so here it goes again...

Thanks so much for all your help,
I have to try the code in the morning becuase im away from my desk now,
Im still familiarizing myself with addressing the query thru vba code as youve shown me

Ill get back to you, thanks very much again
BJF

 

[pbaldy]

No problem; post back if you're still stuck.

 

[BJF]

Hi Paul-

The if statement works however the code doesn't function as I need it to.

Can you help me understand why?


as I told you in an earlier post, I've got a controlled test set up with only three records.
R001
R002

and R003 are my three records with R003 being a composite of the costs of R001 and R002 at certain percentages.

I am changing the cost of R001 and then clicking an update button with the following code behind the button:


Private Sub Update_Click()

Dim dbs As DAO.Database
Dim rsQuery As DAO.Recordset

Set dbs = CurrentDb

Set rsQuery = dbs.OpenRecordset("qryRmResin", dbOpenDynaset)

'Check to see if the recordset actually contains rows
If Not (rsQuery.EOF And rsQuery.BOF) Then
rsQuery.MoveFirst 'Unnecessary in this case, but still a good habit
Do Until rsQuery.EOF = True
'Perform an edit


If rsQuery!Blendcheck = True Then
rsQuery.Edit
rsQuery!ResinCostWithFreight = Nz((R1.Column(2) * (PercentR1)), 0) + Nz((R2.Column(2) * (PercentR2)), 0)
rsQuery.Update

End If
'Move to the next record. Don't ever forget to do this.
rsQuery.MoveNext

Loop

Else
MsgBox "There are no records in the recordset."
End If

MsgBox "Update complete."

rsQuery.Close 'Close the recordset
Set rsQuery = Nothing 'Clean up

Me.Requery

End Sub



Whats happening is when i click the update button, the code is in fact affecting the correct record as it does not alter R001 or R002, however it is making the cost of R003 $0.00.

I found that it does recalculate correctly if and only if I click the update button while my form is on record R003. When i do that i see the change occur immediately after I click out of the msgbox that is displayed saying "Update Complete"

I'm analyzing the order of operation in the code but I could really use your help again

Thanks,
BJF