If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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?? |
#22
|
|||
|
|||
Would like to loop
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?? |
#23
|
|||
|
|||
Would like to loop
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?? |
#24
|
|||
|
|||
Would like to loop
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 |
Thread Tools | |
Display Modes | |
|
|