Would like to loop

Would someone please offer a solution to loop through the following
code for as many times as there are records in "rs"
The code compiles and it loops through it all but only updates the
first record. I can't get my head around the loop logic.
MsA 2003 and XP only Thanks for any help!

intCount = 1
With rsListHist
For intCount = 1 To rs.RecordCount
.MoveFirst
Do While intCount = rs.RecordCount
Debug.Print Me.SISItemCode
rsListHist.AddNew
rsListHist!FieldName = strCtl
rsListHist!UserName = CurrentUser()
rsListHist!SISItemCode = SISItemCode
rsListHist!ChngeDate = Now()
rsListHist!OldDiscount = Discount.OldValue
rsListHist!NewDiscount = intDiscount
rsListHist!ControlSource = ctlSource
rsListHist.Update
.MoveNext
Loop
End With
Next

On Fri, 16 Jan 2009 15:26:50 -0800 (PST), TeeSee
wrote:

Would someone please offer a solution to loop through the following
code for as many times as there are records in "rs"
The code compiles and it loops through it all but only updates the
first record. I can't get my head around the loop logic.
MsA 2003 and XP only Thanks for any help!

intCount = 1
With rsListHist
For intCount = 1 To rs.RecordCount
.MoveFirst
Do While intCount = rs.RecordCount
Debug.Print Me.SISItemCode
rsListHist.AddNew
rsListHist!FieldName = strCtl
rsListHist!UserName = CurrentUser()
rsListHist!SISItemCode = SISItemCode
rsListHist!ChngeDate = Now()
rsListHist!OldDiscount = Discount.OldValue
rsListHist!NewDiscount = intDiscount
rsListHist!ControlSource = ctlSource
rsListHist.Update
.MoveNext
Loop
End With
Next

Erm?

Avoid the entire problem by using an Append query rather than looping through
a recordset. You're looping through rs but - as far as I can tell - never
doing anything with the records in it. What is rs, what is the context, and
why are you (apparently) trying to create rs.recordcount identical records in
reListHist?

--

John W. Vinson [MVP]

On Jan 16, 6:43*pm, John W. Vinson
wrote:
On Fri, 16 Jan 2009 15:26:50 -0800 (PST), TeeSee
wrote:





Would someone please offer a solution to loop through the following
code for as many times as there are records in "rs"
The code compiles and it loops through it all but only updates the
first record. I can't get my head around the loop logic.
MsA 2003 and XP only Thanks for any help!

intCount = 1
* * * *With rsListHist
* * * * * For intCount = 1 To rs.RecordCount
* * * * * * * * * *.MoveFirst
* * * * * Do While intCount = rs.RecordCount
* * * * * * Debug.Print Me.SISItemCode
* * * * * * rsListHist.AddNew
* * * * * *rsListHist!FieldName = strCtl
* * * * * *rsListHist!UserName = CurrentUser()
* * * * * *rsListHist!SISItemCode = SISItemCode
* * * * * *rsListHist!ChngeDate = Now()
* * * * * *rsListHist!OldDiscount = Discount.OldValue
* * * * * *rsListHist!NewDiscount = intDiscount
* * * * * *rsListHist!ControlSource = ctlSource
* * * * * *rsListHist.Update
* * * * * * * * * * .MoveNext
* * * * *Loop
* * * * End With
* * * *Next

Erm?

Avoid the entire problem by using an Append query rather than looping through
a recordset. You're looping through rs but - as far as I can tell - never
doing anything with the records in it. What is rs, what is the context, and
why are you (apparently) trying to create rs.recordcount identical records in
reListHist?

--

* * * * * * *John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

John ... Thanks for responding.
Company ABC sells to me with a list price less a discount which can be
for few or many records. Same discount for all. So depending on the
item(s) I create a recordset "rs" with the records I want to change.
Since there can be 1 or 20 or 100 records I first show the content in
"rs" in a form in order that I can verify the records prior to
updating them all. Before I actually do the update I am trying to
write the changes and the source etc as shown in the code in the post.
There is further code below this that also runs through the 'rs"
recordset and updates it. I didn't show that since I felt that if I
could get the looping logic correct I'd be off to the races. Obviously
my approach is flawed. Any help to improve understanding is always
welcome.

Best rgards

On Fri, 16 Jan 2009 16:19:23 -0800 (PST), TeeSee
wrote:


John ... Thanks for responding.
Company ABC sells to me with a list price less a discount which can be
for few or many records. Same discount for all. So depending on the
item(s) I create a recordset "rs" with the records I want to change.
Since there can be 1 or 20 or 100 records I first show the content in
"rs" in a form in order that I can verify the records prior to
updating them all. Before I actually do the update I am trying to
write the changes and the source etc as shown in the code in the post.
There is further code below this that also runs through the 'rs"
recordset and updates it. I didn't show that since I felt that if I
could get the looping logic correct I'd be off to the races. Obviously
my approach is flawed. Any help to improve understanding is always
welcome.

Best rgards

Where are the two recordsets defined? What table contains the data? Does the
data from Company ABC come as an external file, a table, or what...? I'm still
not understanding the logic: you want to run the update twice, once for "show
only" and once for real?

Note that stepping through a recordset in code does not display ANYTHING on
the screen: are you assuming that it will?
--

John W. Vinson [MVP]

Hi,

I am surprised the code compiles. I do not see that you are updating
anything, only adding new records to rsListHist. You can really help
yourself by using consistent indenting:

intCount = 1
With rsListHist
For intCount = 1 To rs.RecordCount
.MoveFirst
Do While intCount = rs.RecordCount
'Debug.Print Me.SISItemCode
rsListHist.AddNew
rsListHist!FieldName = strctl
rsListHist!UserName = CurrentUser()
rsListHist!SISItemCode = SISItemCode
rsListHist!ChngeDate = Now()
rsListHist!OldDiscount = Discount.OldValue
rsListHist!NewDiscount = intDiscount
rsListHist!ControlSource = ctlSource
rsListHist.Update
.MoveNext
Loop
End With
Next

Note that you have an crossover of code groups:

With rsListHist
For intCount = 1 To rs.RecordCount
....
End With
Next

Not sure why you would set intCount to 1 just before the For loop when
the For loop is going to do that for you. There is no need to move to the
first record in rsListHist before adding any new records. Nor to move to the
next (new) record after doing the update. You use the intCount variable as a
control for both the outer For loop and the inner Do While loop. Not Good.
You never change the intCount in the inner loop--does that not cause an
infinite looping? I do not see that you ever move off of the first record in
rs. And finally, I do not see any reference to rs in the part that sets the
values of rsListHist. What is it's purpose in this part of the code? This,
even with your explanation to John Vinson, is not making any sense.

Apologies for sounding harsh :-(

Perhaps a much better way to deal with this is more or less what John
suggested. Use an append query to record the current state and that it was
(really is about to be) changed in the history table. Then use one or more
update queries to do the necessary updates. If you could give a concrete
example of a row of data that comes in, what you want placed in the history
table for/from it, and what changes you then make to it, someone probably can
suggest a better way.

Clifford Bass

"TeeSee" wrote:

Would someone please offer a solution to loop through the following
code for as many times as there are records in "rs"
The code compiles and it loops through it all but only updates the
first record. I can't get my head around the loop logic.
MsA 2003 and XP only Thanks for any help!

intCount = 1
With rsListHist
For intCount = 1 To rs.RecordCount
.MoveFirst
Do While intCount = rs.RecordCount
Debug.Print Me.SISItemCode
rsListHist.AddNew
rsListHist!FieldName = strCtl
rsListHist!UserName = CurrentUser()
rsListHist!SISItemCode = SISItemCode
rsListHist!ChngeDate = Now()
rsListHist!OldDiscount = Discount.OldValue
rsListHist!NewDiscount = intDiscount
rsListHist!ControlSource = ctlSource
rsListHist.Update
.MoveNext
Loop
End With
Next

On Jan 16, 7:54*pm, John W. Vinson
wrote:
On Fri, 16 Jan 2009 16:19:23 -0800 (PST), TeeSee
wrote:

John ... Thanks for responding.
Company ABC sells to me with a list price less a discount which can be
for few or many records. Same discount for all. So depending on the
item(s) I create a recordset "rs" with the records I want to change.
Since there can be 1 or 20 or 100 records I first show the content in
"rs" in a form in order that I can verify the records prior to
updating them all. Before I actually do the update I am trying to
write the changes and the source etc as shown in the code in the post.
There is further code below this that also runs through the 'rs"
recordset and updates it. I didn't show that since I felt that if I
could get the looping logic correct I'd be off to the races. Obviously
my approach is flawed. Any help to improve understanding is always
welcome.

Best rgards

Where are the two recordsets defined? What table contains the data? Does the
data from Company ABC come as an external file, a table, or what...? I'm still
not understanding the logic: you want to run the update twice, once for "show
only" and once for real?

Note that stepping through a recordset in code does not display ANYTHING on
the screen: are you assuming that it will?
--

* * * * * * *John W. Vinson [MVP]

No John ... Sorry if I'm not making this clear. I'll try again. I'm
not trying to be a smart XXX when I suggest that I'll list the
"process" step by step
1 Vendor sends written notification of a price increase which could
mean that instead of 50% off list I would only get 40% off.
2) I am in the process of developing a cmd button on my switchboard to
"Update Purchase Discount"
3) The initial click opens a form with three fields one of which is
the discount field. This is SQL named "rs"
4) That form opens and displays the records I want to change. Reason
is I want to ensure that the records are exactly what I want before
going further.
5) Once satisfied, I have a button on that form that then gets to
where we are now. Both recordsets are defined on this click. "rs" is
SQL and "rsListHist" is based on the underlying history table

So all I'm trying to do is to add the change history with rsListHist
for as many records are within recordset "rs"

As mentioned previously there is code that follows this that writes
the changes to the "rs" recordset as well thereby updating the working
BE.
Hope this makes more sense.
Thanks again.

On Fri, 16 Jan 2009 17:18:01 -0800 (PST), TeeSee
wrote:


No John ... Sorry if I'm not making this clear. I'll try again. I'm
not trying to be a smart XXX when I suggest that I'll list the
"process" step by step

Good idea.

1 Vendor sends written notification of a price increase which could
mean that instead of 50% off list I would only get 40% off.
2) I am in the process of developing a cmd button on my switchboard to
"Update Purchase Discount"
3) The initial click opens a form with three fields one of which is
the discount field. This is SQL named "rs"

A Form is not SQL, and SQL is not a recordset.

You're basing the form on something - I presume a query. What query?

4) That form opens and displays the records I want to change. Reason
is I want to ensure that the records are exactly what I want before
going further.

So you want to see which records will be updated - fine. You could also
display the old and new prices on the form if you wish... right?

5) Once satisfied, I have a button on that form that then gets to
where we are now. Both recordsets are defined on this click. "rs" is
SQL and "rsListHist" is based on the underlying history table

So all I'm trying to do is to add the change history with rsListHist
for as many records are within recordset "rs"

As mentioned previously there is code that follows this that writes
the changes to the "rs" recordset as well thereby updating the working
BE.

Please post the rest of the code - in particular the code which defines rs and
rsListHist.

Again to clarify: you want to do two things - append the records prior to the
change into a history table, and then update those records to the new price?
Did you *intentionally and purposefully* choose to use the (relatively
inefficient) recordset crawling rather than an Append query followed by an
Update query?
--

John W. Vinson [MVP]

On Jan 16, 8:54*pm, John W. Vinson
wrote:
On Fri, 16 Jan 2009 17:18:01 -0800 (PST), TeeSee
wrote:

No John ... Sorry if I'm not making this clear. I'll try again. I'm
not trying to be a smart XXX when I suggest that I'll list the
"process" step by step

Good idea.

1 Vendor sends written notification of a price increase which could
mean that instead of 50% off list I would only get 40% off.
2) I am in the process of developing a cmd button on my switchboard to
"Update Purchase Discount"
3) The initial click opens a form with three fields one of which is
the discount field. This is SQL named "rs"

A Form is not SQL, and SQL is not a recordset.

You're basing the form on something - I presume a query. What query?

4) That form opens and displays the records I want to change. Reason
is I want to ensure that the records are exactly what I want before
going further.

So you want to see which records will be updated - fine. You could also
display the old and new prices on the form if you wish... right?

5) Once satisfied, I have a button on that form that then gets to
where we are now. Both recordsets are defined on this click. "rs" is
SQL and "rsListHist" is based on the underlying history table
So all I'm trying to do is to add the change history with rsListHist
for as many records are within recordset "rs"

As mentioned previously there is code that follows this that writes
the changes to the "rs" recordset as well thereby updating the working
BE.

Please post the rest of the code - in particular the code which defines rs and
rsListHist.

Again to clarify: you want to do two things - append the records prior to the
change into a history table, and then update those records to the new price?
Did you *intentionally and purposefully* choose to use the (relatively
inefficient) recordset crawling rather than an Append query followed by an
Update query?
--

* * * * * * *John W. Vinson [MVP]

John ... Here is the entire code after the CLICK on the switchboard.
Believe me there are many other flaws that need cleanup but am leaving
it as is for you to critique which I truly appreciate.
T answer your specific question "Did you intentionally and ....." Yes!
Based on a post within the group on the same history file topic which
seemed to make sense to me and was not shot down in flames as an
inefficient way to go. Thanks again John.

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrorTrap
'************************************************* ***************
Dim db As DAO.Database
Dim rs As DAO.Recordset

'************************************************* ***************
Dim intResponse As Integer
Dim intDiscount As Single 'New discount input thru
InputBox
Dim intMessage, Title As String 'Message for new discount
InputBox
Dim strInput As String 'Contains the new discount
Dim strSQL As String 'String containing SQL for
recordset
Dim strItem As String 'Contains the new SQL input
SIS code String
Dim strMessage, strTitle As String 'Pertains to the InputBox
requesting the SIS code String for the SQL
Dim response As String
'************************************************* *************
Set db = CurrentDb()

strMessage = "Please input the SIS code filter string"
strTitle = "PURCHASE DISCOUNT UPDATE."
response = InputBox(strMessage, strTitle, Default, 5000, 3000)

If Trim(response) = "" Or IsNull(response) Then ' Check for empty
return
DoCmd.Close acForm, "frmDiscountUpdate", acSaveNo
Exit Sub
Else
strItem = response
End If

strSQL = "SELECT tblMaterialMaster.SISitemCode,"
strSQL = strSQL & "tblMaterialMaster.MaterialDescription,"
strSQL = strSQL & "tblMaterialMaster.Discount"
strSQL = strSQL & " FROM tblMaterialMaster"
strSQL = strSQL & " WHERE ((tblMaterialMaster.SISItemCode) Like "
& """" & strItem & """)"
strSQL = strSQL & " ORDER BY tblMaterialMaster.SISitemCode"


'Set rs = db.OpenRecordset(strSQL)
Me.RecordSource = strSQL

'************************************************* *****************

NormalExit:
Exit Sub ' or Exit Function
ErrorTrap:
If Err = 2501 Then
Resume Next
Else
MsgBox Err.Description, , Str(Err)
Resume NormalExit
End If
End Sub

***** The following is the code behind the button on the form *******

Private Sub cmdListUpdate_Click()
On Error GoTo ErrorTrap

'************************************************* ***************
Dim rsListHist As DAO.Recordset
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim intResponse As Integer
Dim intDiscount As Single 'New discount input thru
InputBox
Dim intMessage As String 'Message for new discount
InputBox
Dim Title As String
Dim strInput As String 'Contains the new discount
Dim strSQL As String 'String containing SQL for
recordset
Dim strItem As String 'Contains the new SQL input
SIS code String
Dim strMessage As String 'Pertains to the InputBox requesting
the SIS code String for the SQL
Dim strTitle As String
Dim response As String
Dim strCtl As String
Dim ctlSource As String
Dim intCount As Integer
Dim intRecCount As Integer
'************************************************* *************
Set db = CurrentDb()

strMessage = "Please input the SIS code filter string"
strTitle = "PURCHASE DISCOUNT UPDATE."
response = InputBox(strMessage, strTitle, Default, 5000, 3000)

If Trim(response) = "" Or IsNull(response) Then ' Check for empty
return
DoCmd.Close acForm, "frmDiscountUpdate", acSaveNo
Exit Sub
Else
strItem = response
End If


strSQL = "SELECT tblMaterialMaster.Funds,"
strSQL = strSQL & "tblMaterialMaster.SISItemCode,"
strSQL = strSQL & "tblMaterialMaster.CostPerInvUnit,"
strSQL = strSQL & "tblMaterialMaster.Supplier,"
strSQL = strSQL & "tblMaterialMaster.Contents,"
strSQL = strSQL & "tblMaterialMaster.ManufacturerName,"
strSQL = strSQL & "tblMaterialMaster.LocalGroup,"
strSQL = strSQL & "tblMaterialMaster.LocalSubGroup,"
strSQL = strSQL & "tblMaterialMaster.ManufacturerNo,"
strSQL = strSQL & "tblMaterialMaster.MaterialDescription,"
strSQL = strSQL & "tblMaterialMaster.MaterialNote,"
strSQL = strSQL & "tblMaterialMaster.CorpMatlGrp,"
strSQL = strSQL & "tblMaterialMaster.InvUnit,"
strSQL = strSQL & "tblMaterialMaster.ListPrice,"
strSQL = strSQL & "tblMaterialMaster.Discount,"
strSQL = strSQL & "tblMaterialMaster.CostDateNote"
strSQL = strSQL & " FROM tblMaterialMaster"
strSQL = strSQL & " WHERE ((tblMaterialMaster.SISItemCode) Like "
& """" & strItem & """)"
strSQL = strSQL & " ORDER BY tblMaterialMaster.SISitemCode"

Set rs = db.OpenRecordset(strSQL)
Debug.Print " RecordCount = " & _
rs.RecordCount
With rs
.MoveLast
.MoveFirst
End With
'Me.RecordSource = strSQL
Set rsListHist = db.OpenRecordset("tblMaterialMasterHistory")
'Me.RecordSource = "tblMaterialMasterHistory"
Debug.Print " RecordCount = " & _
rs.RecordCount
intRecCount = rs.RecordCount
Debug.Print intRecCount

Destination:

intMessage = "Please input the new discount as a decimal"
Title = "Discount Update"
strInput = InputBox(intMessage, Title, Default, 5000,
3000)


intDiscount = Val(strInput)

Debug.Print intDiscount
If intDiscount = 1 Then
intResponse = MsgBox("Please input as a decimal as
asked!", vbOKOnly + vbCritical, "WHOOOPS!")
GoTo Destination
Else

' Write changes to the history file


strCtl = Me!Discount.Name
ctlSource = Me.Discount.ControlSource
With rsListHist
'For intCount = 1 To rs.RecordCount
intCount = rs.RecordCount
'.MoveFirst
Do Until / while ????
'Debug.Print intCount
Debug.Print Me.SISItemCode
rsListHist.AddNew
rsListHist!FieldName = strCtl
rsListHist!UserName = CurrentUser()
rsListHist!SISItemCode = SISItemCode
rsListHist!ChngeDate = Now()
rsListHist!OldDiscount = Discount.OldValue
rsListHist!NewDiscount = intDiscount
rsListHist!ControlSource = ctlSource
rsListHist.Update
.MoveNext
Loop
End With
'Next
****** The following code seems to do what I
expect ******
' Change all discount values
With rs
.MoveFirst
Do While Not .EOF
.Edit
rs!Discount = intDiscount
If intDiscount = 0 Then
Exit Sub
End If
.Update
.MoveNext
Loop
End With
End If

rs.Close
rsListHist.Close
db.Close

Set rs = Nothing
Set rsListHist = Nothing
Set db = Nothing
NormalExit:
Exit Sub ' or Exit Function


ErrorTrap:
If Err = 2501 Then
Resume Next
Else
MsgBox Err.Description, , Str(Err)
Resume NormalExit
End If

End Sub

On Jan 16, 8:04*pm, Clifford Bass
wrote:
Hi,

* * *I am surprised the code compiles. *I do not see that you are updating
anything, only adding new records to rsListHist. *You can really help
yourself by using consistent indenting:

* * intCount = 1
* * With rsListHist
* * * * For intCount = 1 To rs.RecordCount
* * * * * * .MoveFirst
* * * * * * Do While intCount = rs.RecordCount
* * * * * * * * 'Debug.Print Me.SISItemCode
* * * * * * * * rsListHist.AddNew
* * * * * * * * rsListHist!FieldName = strctl
* * * * * * * * rsListHist!UserName = CurrentUser()
* * * * * * * * rsListHist!SISItemCode = SISItemCode
* * * * * * * * rsListHist!ChngeDate = Now()
* * * * * * * * rsListHist!OldDiscount = Discount.OldValue
* * * * * * * * rsListHist!NewDiscount = intDiscount
* * * * * * * * rsListHist!ControlSource = ctlSource
* * * * * * * * rsListHist.Update
* * * * * * * * .MoveNext
* * * * * * Loop
* * End With
* * * * Next

* * *Note that you have an crossover of code groups:

* * With rsListHist
* * * * For intCount = 1 To rs.RecordCount
...
* * End With
* * * * Next

* * *Not sure why you would set intCount to 1 just before the For loop when
the For loop is going to do that for you. *There is no need to move to the
first record in rsListHist before adding any new records. *Nor to move to the
next (new) record after doing the update. *You use the intCount variable as a
control for both the outer For loop and the inner Do While loop. *Not Good. *
You never change the intCount in the inner loop--does that not cause an
infinite looping? *I do not see that you ever move off of the first record in
rs. *And finally, I do not see any reference to rs in the part that sets the
values of rsListHist. *What is it's purpose in this part of the code? *This,
even with your explanation to John Vinson, is not making any sense.

* * * Apologies for sounding harsh :-(

* * *Perhaps a much better way to deal with this is more or less what John
suggested. *Use an append query to record the current state and that it was
(really is about to be) changed in the history table. *Then use one or more
update queries to do the necessary updates. *If you could give a concrete
example of a row of data that comes in, what you want placed in the history
table for/from it, and what changes you then make to it, someone probably can
suggest a better way.

* * * * * * * * Clifford Bass



"TeeSee" wrote:
Would someone please offer a solution to loop through the following
code for as many times as there are records in "rs"
The code compiles and it loops through it all but only updates the
first record. I can't get my head around the loop logic.
MsA 2003 and XP only Thanks for any help!

intCount = 1
* * * * With rsListHist
* * * * * *For intCount = 1 To rs.RecordCount
* * * * * * * * * * .MoveFirst
* * * * * *Do While intCount = rs.RecordCount
* * * * * * *Debug.Print Me.SISItemCode
* * * * * * *rsListHist.AddNew
* * * * * * rsListHist!FieldName = strCtl
* * * * * * rsListHist!UserName = CurrentUser()
* * * * * * rsListHist!SISItemCode = SISItemCode
* * * * * * rsListHist!ChngeDate = Now()
* * * * * * rsListHist!OldDiscount = Discount.OldValue
* * * * * * rsListHist!NewDiscount = intDiscount
* * * * * * rsListHist!ControlSource = ctlSource
* * * * * * rsListHist.Update
* * * * * * * * * * *.MoveNext
* * * * * Loop
* * * * *End With
* * * * Next- Hide quoted text -

- Show quoted text -

I would just like to thank Clifford for his response as well. There
are comments in there that I can learn from. Clifford if I couldn't
accept the critique I wouldn't ask in the first place so please keep
critiquing. Best regards

On Sat, 17 Jan 2009 07:27:59 -0800 (PST), TeeSee
wrote:

John ... Here is the entire code after the CLICK on the switchboard.
Believe me there are many other flaws that need cleanup but am leaving
it as is for you to critique which I truly appreciate.
T answer your specific question "Did you intentionally and ....." Yes!
Based on a post within the group on the same history file topic which
seemed to make sense to me and was not shot down in flames as an
inefficient way to go. Thanks again John.

That's a lot of code... and I need to go out for most of the afternoon, I'll
try to look it over tomorrow or Monday.

--

John W. Vinson [MVP]