I am trying to do calculations through VBA, although I am not a veteran coder. I am doing it through the form itself because when Production goes to create one of these Job Tickets a number of very important fields can change on the fly, such as which Unit of Measure we are running in, which type of Wood, how we Wrap it, etc. All of these changes would affect the total footage or amount of pieces we have to run, which is why I have been making If-Then-Else statements for them, and it has worked out well so far until I get to Wrap SQ Footage. I am continually getting a zero inserted into my field, but when I do the calculations on my own I have never gotten 0. Code is below, any help or suggestions are very much appreciated.
I have created this expression in the control source of one of the Wrap SQ Footages, and it comes out correctly. I can't see what is the difference between these in terms of the math they are doing.
=Abs(Int( (([Wrap_Slit1]/12) * [Quantity_Ordered] ) * ( [RIP_Scrap_Rate] 1))))
Private Sub FTG_Calculations()
'Declare Variable
Dim L As Double
Dim Length As Double
Dim OrderFTG As Double
Dim UoM As String
Dim W As Double
Dim frm As Access.Form
Set frm = Forms!Frm_JobTicket
'Set L equal to Length from Tbl_JobTicketMould
L = DLookup("Length", "Tbl_JobTicketMould", "Access_ID =" & Forms!Frm_JobTicket!Part_Number)
'Convert Length to Feet
Length = (L \ 12)
'Find Unit of Measure for this part
UoM = DLookup("Unit_of_Measure", "Tbl_JobTicketUoM", "Access_ID =" & Forms!Frm_JobTicket!Part_Number)
'Mupltiply Length times Quantity to get Order Footage
OrderFTG = Int((Length * Me.Txt_Pcs_JobTicket))
'If UoM is PCS then insert that number. Otherwise set equal to Quantity Ordered divided by Length of piece(in FT)
If UoM = "PCS" Then Me.Txt_Pcs_JobTicket = Me.Quantity_Ordered Else: Me.Txt_Pcs_JobTicket = Abs(Int(Me.Quantity_Ordered \ Length))
'Define limits of the loop. Then runs through all Wrap SQ FTG fields and inputs calculation
For W = 1 To 3
'If UoM is PCS then calculate Order Footage to find Wrap Sqaure Footage. Otherwise take slit size in FT and multiply by Order Quantity and Scrap Rate
If UoM = "PCS" Then
frm("Txt_Wrap" & W & "SQFTG_JobTicket") = (((frm("Wrap_Slit" & W) \ 12) * OrderFTG) * (Round((frm("RIP_Scrap_Rate")), 3) 1))
Else: frm("Txt_Wrap" & W & "SQFTG_JobTicket") = (((frm("Wrap_Slit" & W) \ 12) * frm(Quantity_Ordered)) * (frm(RIP_Scrap_Rate 1)))
End If
Next W
I have figured out so far that the issue is in the (frm("Wrap_Slit" & W) \ 12)
area. For some reason Wrap_Slit1 shows a value of 2 in the data tips, but when I divide by 12 it comes out to 0. I have all of my data points set to double, and the variables I have declared to double so I have no clue why it is rounding it down when it should come out to .16667
CodePudding user response:
Place the following code before the:
If UoM = "PCS" Then
Msgbox code:
MsgBox("Current State:" & vbCrLf & _
"UoM:" & vbTab & UoM & vbCrlf & _
"OrderFTGL" & vbTab & OrderFTG & _
"Wrap_Slit1:" & vbTab & Me.Wrap_Slit1 & _
... continue pattern for other desired values in calculation...
"Continue...", vbOK)