Would like to loop

On Jan 19, 12:26*am, Clifford Bass
wrote:
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.- Hide quoted text -

- Show quoted text -

What is it you'd like to know specifically??

Hi TeeSee,

George was interested in seeing the complete original code in which you
originally were having trouble. However, I myself, am not that curious.

Clifford Bass

"TeeSee" wrote:

What is it you'd like to know specifically??

The entire procedure would be interesting to look at.

"TeeSee" wrote in message
...
On Jan 19, 12:26 am, Clifford Bass
wrote:
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.- Hide quoted text -

- Show quoted text -

What is it you'd like to know specifically??

On Jan 19, 10:21*am, Clifford Bass
wrote:
Hi TeeSee,

* * *George was interested in seeing the complete original code in which you
originally were having trouble. *However, I myself, am not that curious..

* * * * * * * * * * *Clifford Bass



"TeeSee" wrote:
What is it you'd like to know specifically??- Hide quoted text -

- Show quoted text -

Hi George .... Here is my original code PLUS there might be a few
extra lines that I have added while trying to achieve what I wanted.
This code may not compile but I had a version which did but would not
move off the first record. Please give me your thoughts.

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


End If
strCtl = Me!Discount.Name
Debug.Print strCtl
Debug.Print Me!Discount.OldValue
Debug.Print Me.Discount.Value
ctlSource = Me.Discount.ControlSource
intCount = 1
With rsListHist
Debug.Print intCount
For intCount = 1 To rs.RecordCount
intCount = rs.RecordCount
.MoveFirst
Do Until intCount = 3
Debug.Print intCount
intCount = intCount + 1
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

' 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