Would like to loop

A few quick thoughts from reading through the code one time:

1. you seem to be mis-using common naming conventions for variables,
with a string and a decimal value being assigned to variables whose
names start with "int", which is generally used to indicate an
integer data type, which could not hold either of those values.

2. so far as I can tell, you don't need to walk a recordset for any
of this. You are adding records to rsListHist based on a set of
non-changing values, so it could just as easily be done with a
single APPEND query:

INSERT INTO tblMaterialMasterHistory ( FieldName, UserName,
SISItemCode, ChngeDate, OldDiscount, NewDiscount, ControlSource )
SELECT strCtl, CurrentUser(), SISItemCode, Now(), Discount.OldValue,
intDiscount, ctlSource FROM tblMaterialMaster"
WHERE ((tblMaterialMaster.SISItemCode) Like " & """" & strItem &
""")"

Now, obviously, that SQL as it stands won't work, as you'll need to
delimit the values in the SELECT line with quotes and so forth
(e.g., # for the date), but the point is, the operation you're doing
with rsListHist.AddNew can all be done in a single append operation.

Likewise, the update to the other recordset can be done with SQL,
thus:

UPDATE tblMaterialMaster
SET Discount = intDiscount
WHERE ((tblMaterialMaster.SISItemCode) Like " & """" & strItem &
""")"

Again, delimiters and all that, but these two SQL strings (when
properly formatted) will do the whole job for you in one fell swoop
for each. While you won't likely see a big difference in performance
with a small number of rows, once you are dealing with lots of data
or remote data that takes a while to retrieve, the difference can be
huge.

It's always better to do operations on sets of records in SQL rather
than walking through those records updating them one by one.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Hi TeeSee,

Your openness is appreciated! A couple of thoughts to add to David's
solution, which I think would work as adjusted:

Instead of using the InputBox to get the values, place some unbound
text boxes on your form. If you do that you can enforce data type through
the Format and/or Input Mask properties (date, fixed, etc.). Then in your
code you can do some othe the other checking such as discount being 1. But
also make sure it is = 0 in case someone enters something like -0.23. At
least to start with, I would create the queries using the query designer.
That will help you get them right much easier then doing it by hand. Oh, if
this is not a development database, make sure you test the queries out on a
copy before doing it in the real database. In your queries, as long as your
form is open, you can reference the text boxes on the form by using
[Forms][YourFormsName]![TextBoxName]. Instead of the Like cause, without any
wild cards, just use =.

So David's first SQL might end up looking like this:

INSERT INTO tblMaterialMasterHistory (FieldName, UserName,
SISItemCode, ChngeDate, OldDiscount, NewDiscount, ControlSource)
SELECT "Discount", CurrentUser(), SISItemCode, Now(),
[Forms]![MyFormsName]![Discount].[OldValue],
[Forms]![MyFormsName]![NewDiscount], "Discount"
FROM tblMaterialMaster
WHERE SISItemCode = [Forms]![MyFormsName]![ItemToChange]

Note that I just used the literal "Discount" in the select for the
FieldName and for the ControlSource since that is what you would get if you
used [Forms]![MyFormsName]![Discount].[Name] and
[Forms]![MyFormsName]![Discount].[ControlSource].

And the second one might be:

UPDATE tblMaterialMaster
SET Discount = [Forms]![MyFormsName]![NewDiscount]
WHERE SISItemCode = [Forms]![MyFormsName]![ItemToChange]

You will need to adjust for your actual boxes on your form.

Then to run the queries you can actually just open them:

DoCmd.OpenQuery "qryCreateHistoryRecords"
DoCmd.OpenQuery "qryUpdateDiscount"

Hope this helps,

Clifford Bass

"TeeSee" wrote:

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 Jan 18, 3:20*pm, Clifford Bass
wrote:
Hi TeeSee,

* * *Your openness is appreciated! *A couple of thoughts to add to David's
solution, which I think would work as adjusted:

* * *Instead of using the InputBox to get the values, place some unbound
text boxes on your form. *If you do that you can enforce data type through
the Format and/or Input Mask properties (date, fixed, etc.). *Then in your
code you can do some othe the other checking such as discount being 1. *But
also make sure it is = 0 in case someone enters something like -0.23. *At
least to start with, I would create the queries using the query designer. *
That will help you get them right much easier then doing it by hand. *Oh, if
this is not a development database, make sure you test the queries out on a
copy before doing it in the real database. *In your queries, as long as your
form is open, you can reference the text boxes on the form by using
[Forms][YourFormsName]![TextBoxName]. *Instead of the Like cause, without any
wild cards, just use =.

* * *So David's first SQL might end up looking like this:

INSERT INTO tblMaterialMasterHistory (FieldName, UserName,
SISItemCode, ChngeDate, OldDiscount, NewDiscount, ControlSource)
SELECT "Discount", CurrentUser(), SISItemCode, Now(),
[Forms]![MyFormsName]![Discount].[OldValue],
[Forms]![MyFormsName]![NewDiscount], "Discount"
FROM tblMaterialMaster
WHERE SISItemCode = [Forms]![MyFormsName]![ItemToChange]

* * *Note that I just used the literal "Discount" in the select for the
FieldName and for the ControlSource since that is what you would get if you
used [Forms]![MyFormsName]![Discount].[Name] and
[Forms]![MyFormsName]![Discount].[ControlSource].

* * *And the second one might be:

UPDATE tblMaterialMaster
SET Discount = [Forms]![MyFormsName]![NewDiscount]
WHERE SISItemCode = [Forms]![MyFormsName]![ItemToChange]

* * *You will need to adjust for your actual boxes on your form.

* * *Then to run the queries you can actually just open them:

DoCmd.OpenQuery "qryCreateHistoryRecords"
DoCmd.OpenQuery "qryUpdateDiscount"

* * *Hope this helps,

* * * * * * * *Clifford Bass



"TeeSee" wrote:
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- Hide quoted text -

- Show quoted text -

John David and Clifford ... Thank you for offering this great advice
and I now see the power in SQL based queries. Since Davids post I have
dumped the recordset approach and have spent some time reading up on
SQL etc. I have some of the new code in place and it compiles and
writes to the file until I added the variable intDiscount which is the
new discount thru the InputBox. When I paste the SQL output into a
blank query grid under SQL view it gives me a "Enter Parameter Value"
intDiscount box. I think this tells me my syntax is incorrect in my
SELECT clause. Any thoughts on the syntax for a variable within SQL?
Clifford I have just read your last post and I find that an
interesting approach also. For now, however, since I have gottten this
far i will pursue this avenue first.

dim intDiscount as integer

Here is the SQL

strSQL = "INSERT INTO tblMaterialMasterHistory ( ChngeDate,
SISitemCode,"
strSQL = strSQL & "OldDiscount, NewDiscount )"
strSQL = strSQL & " SELECT Date()as ChngeDate,
tblMaterialMaster.SISItemCode,"
strSQL = strSQL & "tblMaterialMaster.Discount,([intDiscount])"
strSQL = strSQL & " FROM tblMaterialMaster"
strSQL = strSQL & " WHERE ((tblMaterialMaster.SISItemCode) Like "
& """" & strItem & """)"
Debug.Print strSQL
Ive also tried [intDiscount] as Discount

the eror is "Too Few Parameters" Expected 1

Thanks again to all.

Hi TeeSee,

There are lots of ways to deal with your need. Often many that are
equally good solutions. This is one of the interesting things of programming.

Anyway, if you move the intDiscount outside of the quoted stuff so that
its value is concatenated into the string, it should work. Additionally, if
you assign all of it at once to strSQL, it can be easier to write and read.
I often follow a set format with specific things breaking to new lines such
as different clauses or when I get to the edge of the screen. And levels of
indentation. You can use the line continuation indicator (an underscore
preceeded by a space [ _]) and the concatenation operator (&) to do both of
these things.

strSQL = _
"INSERT INTO tblMaterialMasterHistory " & _
"(ChngeDate, SISitemCode, OldDiscount, NewDiscount) " & _
"SELECT Date(), tblMaterialMaster.SISItemCode, " & _
"tblMaterialMaster.Discount, " & intDiscount & " " & _
"FROM tblMaterialMaster " & _
"WHERE tblMaterialMaster.SISItemCode = """ & strItem & """"

Note that I am presenting much smaller lines in the news group due to
its narrow windows. Finaly, since the item code in strItem could contain the
quote symbol ("), either entered accidentally, or on purpose, you need to
make sure to double up any quotes that may be in it through the use of the
Replace() function. So the Where line should actually be:

"WHERE tblMaterialMaster.SISItemCode = """ & _
Replace(strItem, """", """""") & """"

This will prevent a mismatched quotes or somesuch error.

Hope that helps,

Clifford Bass

"TeeSee" wrote:

John David and Clifford ... Thank you for offering this great advice
and I now see the power in SQL based queries. Since Davids post I have
dumped the recordset approach and have spent some time reading up on
SQL etc. I have some of the new code in place and it compiles and
writes to the file until I added the variable intDiscount which is the
new discount thru the InputBox. When I paste the SQL output into a
blank query grid under SQL view it gives me a "Enter Parameter Value"
intDiscount box. I think this tells me my syntax is incorrect in my
SELECT clause. Any thoughts on the syntax for a variable within SQL?
Clifford I have just read your last post and I find that an
interesting approach also. For now, however, since I have gottten this
far i will pursue this avenue first.

dim intDiscount as integer

Here is the SQL

strSQL = "INSERT INTO tblMaterialMasterHistory ( ChngeDate,
SISitemCode,"
strSQL = strSQL & "OldDiscount, NewDiscount )"
strSQL = strSQL & " SELECT Date()as ChngeDate,
tblMaterialMaster.SISItemCode,"
strSQL = strSQL & "tblMaterialMaster.Discount,([intDiscount])"
strSQL = strSQL & " FROM tblMaterialMaster"
strSQL = strSQL & " WHERE ((tblMaterialMaster.SISItemCode) Like "
& """" & strItem & """)"
Debug.Print strSQL
Ive also tried [intDiscount] as Discount

the eror is "Too Few Parameters" Expected 1

Thanks again to all.

For intCount = 1 To rs.RecordCount
.MoveFirst


So you move to the first record as the first step each time through the
loop....



"TeeSee" wrote in message
...
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 Sun, 18 Jan 2009 18:05:20 -0800, "George Hepworth"
wrote:

For intCount = 1 To rs.RecordCount
.MoveFirst


So you move to the first record as the first step each time through the
loop....

d'oh!!

Thanks George.
--

John W. Vinson [MVP]

Been there, done that.


"John W. Vinson" wrote in message
...
On Sun, 18 Jan 2009 18:05:20 -0800, "George Hepworth"
wrote:

For intCount = 1 To rs.RecordCount
.MoveFirst


So you move to the first record as the first step each time through the
loop....

d'oh!!

Thanks George.
--

John W. Vinson [MVP]

Hi George,

That was what I thought first, but the .MoveFirst goes with the "With
rsListHist". Regardless of that, TeeSee has changed to using an append and
an update query.

Clifford Bass

"George Hepworth" wrote:

For intCount = 1 To rs.RecordCount
.MoveFirst


So you move to the first record as the first step each time through the
loop....

The sympton described by the OP "The code compiles and it loops through it
all but only updates the first record. "

is actually consistent with moving the cursor to the first record on each
pass and operating on it repeatedly.

I agree with the use of queries to manage the process as being preferable to
looping a recordset, I'd actually like to see this record walking function
in action anyway.




"Clifford Bass" wrote in message
...
Hi George,

That was what I thought first, but the .MoveFirst goes with the "With
rsListHist". Regardless of that, TeeSee has changed to using an append
and
an update query.

Clifford Bass

"George Hepworth" wrote:

For intCount = 1 To rs.RecordCount
.MoveFirst


So you move to the first record as the first step each time through the
loop....

Hi George,

Agreed. It was only a snippet of the code TeeSee originally
posted--could only go on what was there. The full posting had already been
changed due to the feedback. So we will probably never know.

Clifford Bass

"George Hepworth" wrote:

The sympton described by the OP "The code compiles and it loops through it
all but only updates the first record. "
is actually consistent with moving the cursor to the first record on each
pass and operating on it repeatedly.