Would like to loop

[TeeSee]

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





- Show quoted text -

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

 

[Clifford Bass]

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

 

[George Hepworth]

The entire procedure would be interesting to look at.

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





- Show quoted text -

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

 

[TeeSee]

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





- 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