Code:
Public Sub btnCheckBatch_Click()Dim ChemRst As dao.Recordset
Dim SRst As dao.Recordset
Dim ScrBatchCode As String
Dim ChemistID As String
Dim ChemCodeOnly As String
Dim SBID As Long
Dim ChemInputDate As Date
Dim DateDisp As Date
Dim ClaimID As Long
Dim ItemID As Long
Dim Qty As Integer
Dim d As String, ext, x
Dim srcPath As String, destPath As String, srcFile As String
Dim SRstString As String
Dim invamt As Double
Dim DF As Double
Dim TP As Double
Dim DD As Double
Dim MUp As Double
Dim BB As Double
Dim bb2 As Double
Dim bbf As Double
Dim CostPrice As Double
Dim Fboxes As Integer
SRstString = "SELECT scripts.ScrBatchID, items.ManualInvoicing, scripts.InvAmount, scripts.PharmAmount, chemist.DispensingFee, chemist.AllowableExtraFee, chemist.DDFee, chemist.DDFee, chemist.Markup, items.Schedule, items.StdQty, customers.FirstName, customers.LastName, items.ItemID, items.ItemPrice,scripts.Qty, scripts.txtCustomer2 " & vbCrLf & _
"FROM customers INNER JOIN (((scripts INNER JOIN items ON scripts.ItemID = items.ItemID) INNER JOIN chemist ON scripts.ChemistID = chemist.ChemShortCode) INNER JOIN claims ON scripts.ClaimID = claims.ClaimID) ON customers.CustomerID = claims.CustomerID " & vbCrLf & _
"WHERE (((scripts.ScrBatchID)= " & Me.ScrBatchID & "));"
Set SRst = CurrentDb.OpenRecordset(SRstString, dbOpenDynaset, dbSeeChanges)
SRst.MoveFirst
SBID = SRst!ScrBatchID
Do Until SRst.EOF
ItemID = SRst!ItemID
If SRst!ManualInvoicing = 1 Then
SRst.Edit
SRst!InvAmount = SRst!PharmAmount
SRst.Update
GoTo NextScript
End If
If SRst!Schedule = 3 Then
bbf = 0
DF = 0
TP = 0
Fboxes = Qty
MUp = 1
GoTo Sched3
End If
'set additional charges
DF = SRst!DispensingFee
TP = SRst!AllowableExtraFee
MUp = (SRst!Markup / 100) + 1
CostPrice = SRst!itemprice
'check if it is a DD script and add DD fee if so
If SRst!Schedule = 8 Then
DD = SRst!DDFee
Else
DD = 0
End If
'work out Broken box percent to nearest 0.05
BB = 0.05 * -Int(-(SRst!Qty / SRst!StdQty) / 0.05)
'BB = 0.05 * Round((Me.Qty / DLookup("stdqty", "items", "[itemid]=" & Me.[ItemID])) / 0.05, 0) THIS ROUNDS TO NEAREST 0.05, NOT UP
'check to see if percent is MORE than 100% (eg more than one box given)
If BB > 1 Then
'If BB (non decimal integer) is the same as BB then Full Boxes is the same as BB
If Int(BB) = BB Then
bbf = BB
Else
'Otherwise save Full boxes amount (int(bb))
'Calculate broken boxes minus the full boxes to get the percent broken still
Fboxes = Int(BB)
'bb2 = 0.05 * Round((BB - Fboxes) / 0.05, 0) THIS ROUNDS TO NEAREST 0.05, NOT UP
bb2 = 0.05 * -Int(-(BB - Fboxes) / 0.05)
bbf = DLookup("PercentCharged", "BrokenBoxes", "[PercentUsed] = " & bb2)
End If
Else
Fboxes = 0
bbf = DLookup("PercentCharged", "BrokenBoxes", "[PercentUsed] = " & BB)
End If
If SRst!Schedule = 2 Then
DF = 0
End If
Sched3:
'Round UP
invamt = -Int(-(((CostPrice * (bbf + Fboxes)) * MUp) + TP + DF + DD) / 0.05) * 0.05
'savelater = Me.Qty / DLookup("stdqty", "items", "[itemid]=" & Me.[ItemID])
SRst.Edit
SRst!InvAmount = invamt
SRst!txtCustomer2 = SRst!FirstName & " " & SRst!LastName
SRst.Update
NextScript:
SRst.MoveNext
Loop
'Me.StaffID = DLookup("SettingValue", "SettingsLocal", "SettingID = 1")
'
'DoCmd.OpenReport "rptscriptbatch", acViewNormal, , "[scrbatchid]=" & SBID
Forms!Navigationpage!NavigationSubform.Form!FrmOnlineBatches.Requery
DoCmd.OpenForm "FrmScriptBatch", , , "[ScrBatchID] = " & SBID, acFormEdit
End Sub