Not Updating
in [Forms]
|
Prev: stellenangebote stellenanzeigen stellenangebot muenchen stellenangebote immobilien job stellenangebote karriere deutschland
Next: Case statement Excellent Idea
From: DS on 6 Jul 2008 12:36 I have one record that I would like to divide into any number of records that the client chooses as long as it's not a negative number. Everything works fine except the check total part. Lets say a check is $8.39 and you want to end up with 4 checks or records in the end, each records total should be 2.09, 2.10, 2.10, 2.10 What I am getting is the first record becomes 2.10 the second record 2.09 and the third and fourth record are blank. I'm not sure if its in the third part or the fourth part, my guess is the third. Perhaps it's running to may times? Any direction is appreciated. Thanks DS For Divider = 1 To Nz(Me.TxtDivideCheck - 1, 0) Forms!frmPadDivider!TxtDPNewSalesID = Nz(DMax("[ChkTop]", "tblTop"), 0) + 1 '1 GET HIGHEST SALES ID AVAILABLE FROM tblTop DoCmd.SetWarnings False TopSQL = "UPDATE tblTop SET [ChkTop] = Forms!frmPadDivider!TxtDPNewSalesID;" DoCmd.RunSQL (TopSQL) DoCmd.SetWarnings True '2 MAKE NEW RECORD CurrentDb.Execute "INSERT Into tblChecksTMP(CheckID,ChkCustomerID,ChkServer,ChkTabID,ChkGuests, " & _ "ChkTypeID,ChkSepCheck,ChkDividedCheck,ChkDivideBy,ChkOldCheckID) " & _ "VALUES(" & Forms!frmPadDivider!TxtDPNewSalesID & ",1," & Forms!frmPadDivider!TxtDPServer & ", " & _ "" & Forms!frmPadDivider!TxtDPTableNumber & "," & 1 & "," & 1 & "," & 0 & ", " & _ "" & -1 & "," & Forms!frmPadDivider!TxtDivideCheck & ", " & _ "" & Forms!frmPadDivider!TxtDPOldSalesID & ")" '3 MAKE NEW TOTA:L Dim myNumDivByX As Currency Dim myNums As String Dim i As Currency Dim leftover As Currency Dim absleftover As Currency Dim addon As Currency Dim portion(100) As Currency myNumDivByX = Round(Me.TxtDPTotal / Me.TxtDivideCheck, 2) If (myNumDivByX * Me.TxtDivideCheck) = Me.TxtDPTotal Then For i = 1 To Me.TxtDivideCheck portion(i) = myNumDivByX myNums = myNums & portion(i) Next i ElseIf (myNumDivByX * Me.TxtDivideCheck) <> Me.TxtDPTotal Then leftover = Me.TxtDPTotal - (myNumDivByX * Me.TxtDivideCheck) absleftover = Abs(leftover) If leftover < 0 Then addon = -0.01 Else addon = 0.01 End If If i <= absleftover * 100 Then portion(i) = myNumDivByX + addon Else portion(i) = myNumDivByX End If myNums = myNums & portion(i) End If Me.TxtNewTotal = myNums '4 UPDATE Check Total If Me.TxtDPOldSalesID = Me.TxtDPNewSalesID Then DoCmd.SetWarnings False CheckSQL = "UPDATE tblChecksTMP SET [ChkTotal]=Forms!frmPadDivider!TxtNewTotal " & _ "WHERE tblChecksTMP.[CheckID] = Forms!frmPadDivider!TxtDPOldSalesID;" DoCmd.RunSQL (CheckSQL) DoCmd.SetWarnings True Else DoCmd.SetWarnings False CheckSQL = "UPDATE tblChecksTMP SET [ChkTotal]=Forms!frmPadDivider!TxtNewTotal " & _ "WHERE tblChecksTMP.[CheckID] = Forms!frmPadDivider!TxtDPNewSalesID;" DoCmd.RunSQL (CheckSQL) DoCmd.SetWarnings True End If Next Divider
From: DS on 6 Jul 2008 12:52 This is at least filling in the chck totals, the only problem is... instead of being 2.09, 2.10, 2.10, 2.10 its 2.10, 2.09, 2.09, 2.09 DS Dim myNumDivByX As Currency Dim myNums As String Dim i As Currency Dim leftover As Currency Dim absleftover As Currency Dim addon As Currency Dim portion(100) As Currency myNumDivByX = Round(Me.TxtDPTotal / Me.TxtDivideCheck, 2) If (myNumDivByX * Me.TxtDivideCheck) = Me.TxtDPTotal Then 'For i = 1 To Me.TxtDivideCheck portion(i) = myNumDivByX Me.TxtNewTotal = myNums & portion(i) 'Next i 'UPDATE Check Total If Me.TxtDPOldSalesID = Me.TxtDPNewSalesID Then DoCmd.SetWarnings False CheckSQL = "UPDATE tblChecksTMP SET [ChkTotal]=Forms!frmPadDivider!TxtNewTotal " & _ "WHERE tblChecksTMP.[CheckID] = Forms!frmPadDivider!TxtDPOldSalesID;" DoCmd.RunSQL (CheckSQL) DoCmd.SetWarnings True Else DoCmd.SetWarnings False CheckSQL = "UPDATE tblChecksTMP SET [ChkTotal]=Forms!frmPadDivider!TxtNewTotal " & _ "WHERE tblChecksTMP.[CheckID] = Forms!frmPadDivider!TxtDPNewSalesID;" DoCmd.RunSQL (CheckSQL) DoCmd.SetWarnings True End If ElseIf (myNumDivByX * Me.TxtDivideCheck) <> Me.TxtDPTotal Then leftover = Me.TxtDPTotal - (myNumDivByX * Me.TxtDivideCheck) absleftover = Abs(leftover) MsgBox absleftover If leftover < 0 Then addon = -0.01 Else addon = 0.01 End If If i <= absleftover * 100 Then portion(i) = myNumDivByX + addon Else portion(i) = myNumDivByX End If Me.TxtNewTotal = myNums & portion(i) 'UPDATE Check Total If Me.TxtDPOldSalesID = Me.TxtDPNewSalesID Then DoCmd.SetWarnings False CheckSQL = "UPDATE tblChecksTMP SET [ChkTotal]=Forms!frmPadDivider!TxtNewTotal " & _ "WHERE tblChecksTMP.[CheckID] = Forms!frmPadDivider!TxtDPOldSalesID;" DoCmd.RunSQL (CheckSQL) DoCmd.SetWarnings True Else DoCmd.SetWarnings False CheckSQL = "UPDATE tblChecksTMP SET [ChkTotal]=Forms!frmPadDivider!TxtNewTotal " & _ "WHERE tblChecksTMP.[CheckID] = Forms!frmPadDivider!TxtDPNewSalesID;" DoCmd.RunSQL (CheckSQL) DoCmd.SetWarnings True End If End If Next Divider
From: DS on 6 Jul 2008 13:08 The problem is that the code is adding .01 to all of the records instead of minus on the first or whatever then minus after that. any suggestions on how to fix this? Thanks DS
From: David Glienna on 6 Jul 2008 16:55 that's known as Banker's Rounding, which is the preferred method of dealing with currency. Currency is valid to 4 decimal points. -- David Glienna MVP - Visual Developer (Visual Basic) 2006 thru 2008 "DS" <bootybox(a)optonline.net> wrote in message news:eS4Sjr43IHA.1200(a)TK2MSFTNGP04.phx.gbl... > The problem is that the code is adding .01 to all of the records instead > of minus on the first or whatever then minus after that. any suggestions > on how to fix this? > Thanks > DS >
From: Marshall Barton on 6 Jul 2008 17:57
DS wrote: >This is at least filling in the chck totals, the only problem is... >instead of being 2.09, 2.10, 2.10, 2.10 >its 2.10, 2.09, 2.09, 2.09 >DS > >Dim myNumDivByX As Currency > Dim myNums As String > Dim i As Currency > Dim leftover As Currency > Dim absleftover As Currency > Dim addon As Currency > Dim portion(100) As Currency > myNumDivByX = Round(Me.TxtDPTotal / Me.TxtDivideCheck, 2) > > If (myNumDivByX * Me.TxtDivideCheck) = Me.TxtDPTotal Then > 'For i = 1 To Me.TxtDivideCheck > portion(i) = myNumDivByX > Me.TxtNewTotal = myNums & portion(i) > 'Next i > > 'UPDATE Check Total > If Me.TxtDPOldSalesID = Me.TxtDPNewSalesID Then > DoCmd.SetWarnings False > CheckSQL = "UPDATE tblChecksTMP SET >[ChkTotal]=Forms!frmPadDivider!TxtNewTotal " & _ > "WHERE tblChecksTMP.[CheckID] = >Forms!frmPadDivider!TxtDPOldSalesID;" > DoCmd.RunSQL (CheckSQL) > DoCmd.SetWarnings True > Else > DoCmd.SetWarnings False > CheckSQL = "UPDATE tblChecksTMP SET >[ChkTotal]=Forms!frmPadDivider!TxtNewTotal " & _ > "WHERE tblChecksTMP.[CheckID] = >Forms!frmPadDivider!TxtDPNewSalesID;" > DoCmd.RunSQL (CheckSQL) > DoCmd.SetWarnings True > End If > ElseIf (myNumDivByX * Me.TxtDivideCheck) <> Me.TxtDPTotal Then > leftover = Me.TxtDPTotal - (myNumDivByX * Me.TxtDivideCheck) > absleftover = Abs(leftover) > MsgBox absleftover > If leftover < 0 Then > addon = -0.01 > Else > addon = 0.01 > End If > > If i <= absleftover * 100 Then > portion(i) = myNumDivByX + addon > Else > portion(i) = myNumDivByX > End If > Me.TxtNewTotal = myNums & portion(i) > > 'UPDATE Check Total > If Me.TxtDPOldSalesID = Me.TxtDPNewSalesID Then > DoCmd.SetWarnings False > CheckSQL = "UPDATE tblChecksTMP SET >[ChkTotal]=Forms!frmPadDivider!TxtNewTotal " & _ > "WHERE tblChecksTMP.[CheckID] = >Forms!frmPadDivider!TxtDPOldSalesID;" > DoCmd.RunSQL (CheckSQL) > DoCmd.SetWarnings True > Else > DoCmd.SetWarnings False > CheckSQL = "UPDATE tblChecksTMP SET >[ChkTotal]=Forms!frmPadDivider!TxtNewTotal " & _ > "WHERE tblChecksTMP.[CheckID] = >Forms!frmPadDivider!TxtDPNewSalesID;" > DoCmd.RunSQL (CheckSQL) > DoCmd.SetWarnings True > End If > End If >Next Divider Sorry, but I can't figure out what that code is doing. OTOH, I don't see why a few cents makes a difference to the order of the portions. If that's acceptable, the only code needed could be: PortionAll = Round(Me.TxtDPTotal / Me.TxtDivideCheck, 2) PortionLast = Me.TxtDPTotal - (Me.TxtDivideCheck - 1) * PortionAll -- Marsh MVP [MS Access] |