Would like to loop

T

TeeSee

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
 
J

John W. Vinson

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?
 
T

TeeSee

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
 
J

John W. Vinson

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?
 
C

Clifford Bass

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
 
T

TeeSee

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?

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.
 
J

John W. Vinson

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?
 
T

TeeSee

Good idea.


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

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


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?



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 tothe
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 ... 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
 
T

TeeSee

Hi,

     I am surprised the code compiles.  I do not see that you areupdating
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 itwas
(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 probablycan
suggest a better way.

                Clifford Bass






- 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
 
J

John W. Vinson

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.
 
D

David W. Fenton

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.
 
C

Clifford Bass

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
 
T

TeeSee

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 ona
copy before doing it in the real database.  In your queries, as long asyour
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 forthe
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 said:
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.
 
C

Clifford Bass

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 said:
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.
 
G

George Hepworth

For intCount = 1 To rs.RecordCount
.MoveFirst


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



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
 
C

Clifford Bass

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
 
G

George Hepworth

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.
 
C

Clifford Bass

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
 
Top