Code:
Public Function cablen(Dp As Double, lp As Single, NF As Single, Nvf1 As Integer, Nvf3 As Integer, Nvf6 As Integer, Nvf15 As Integer, Nvs1 As Integer, _ Nvs3 As Integer, Nvs6 As Integer, Nvs15 As Integer, Naps As Integer, _
Ndls As Integer, Npm As Integer, NElb45 As Integer, NElb90 As Integer, Nd As Integer, Nvt As Integer, Am As Integer, Np As Integer, Nr As Integer) As Long
On Error GoTo ExitSub
Dim Ap As Double
Dim Af As Double
Dim Avf1 As Single
Dim Avf3 As Single
Dim Avf6 As Single
Dim Avf15 As Single
Dim AvS1 As Single
Dim Avs3 As Single
Dim Avs6 As Single
Dim AvS15 As Single
Dim Aaps As Single
Dim Adls As Single
Dim Apm As Single
Dim AElb45 As Single
Dim AElb90 As Single
Dim Ad As Single
Dim Drlgth As Single
Dim Avt As Single
Dim Vlgth As Single
Dim strPipeAllowTable As Variant
' strPipeAllowTable = [Forms]![frmSingleLenCal]![subfrmProjectSingle]!txtPipeAllowTable.Value
strPipeAllowTable = DLookup("[PipeAllowTable]", "tblProject")
Dim strAllow As String
Dim RsAllow As dao.Recordset
' Debug.Print Dp, lp, nf, Nvf1, Nvf3
'set default return value
1 cablen = 0
2 strAllow = "select p_allow, Vlv_F150, Vlv_F300, Vlv_F600_900, Vlv_F1500, Vlv_S150, Vlv_S300, Vlv_S600_900, Vlv_S1500, " & _
" An_P_Supp, Dum_Leg_Supp, fl, Pump, Vt, Vlgth, Dr, Drlgth, Elb45, Elb90 from strPipeAllowTable where abs(p_size-" & Dp & ")<1e-5" 'tblpipeallow
' Debug.Print strAllow
3 Set RsAllow = CurrentDb.OpenRecordset(strAllow, dbOpenDynaset)
4 If RsAllow.BOF And RsAllow.EOF Then
5 MsgBox ("Incorrect Pipe Size")
Else
6 Ap = Nz(RsAllow.Fields("p_allow"), 0)
7 Af = Nz(RsAllow.Fields("Fl"), 0)
8 Avf1 = Nz(RsAllow.Fields("Vlv_F150"), 0)
9 Avf3 = Nz(RsAllow.Fields("Vlv_F300"), 0)
10 Avf6 = Nz(RsAllow.Fields("Vlv_F600_900"), 0)
11 Avf15 = Nz(RsAllow.Fields("Vlv_F1500"), 0)
12 AvS1 = Nz(RsAllow.Fields("Vlv_S150"), 0)
13 Avs3 = Nz(RsAllow.Fields("Vlv_S300"), 0)
14 Avs6 = Nz(RsAllow.Fields("Vlv_S600_900"), 0)
15 AvS15 = Nz(RsAllow.Fields("Vlv_S1500"), 0)
16 Aaps = Nz(RsAllow.Fields("An_P_Supp"), 0)
17 Adls = Nz(RsAllow.Fields("Dum_Leg_Supp"), 0)
18 Apm = Nz(RsAllow.Fields("Pump"), 0)
19 AElb45 = Nz(RsAllow.Fields("Elb45"), 0)
20 AElb90 = Nz(RsAllow.Fields("Elb90"), 0)
21 Ad = Nz(RsAllow.Fields("Dr"), 0) + Nz(RsAllow.Fields("Drlgth"), 0)
22 Avt = Nz(RsAllow.Fields("Vt"), 0) + Nz(RsAllow.Fields("Vlgth"), 0)
23 cablen = ((Ap * lp + Af * NF + Avf1 * Nvf1 + Avf3 * Nvf3 + Avf6 * Nvf6 + Avf15 * Nvf15 + _
AvS1 * Nvs1 + Avs3 * Nvs3 + Avs6 * Nvs6 + AvS15 * Nvs15 + _
Aaps * Naps + Adls * Ndls + Apm * Npm + AElb45 * NElb45 + AElb90 * NElb90) * Np * Nr + _
Ad * Nd + Avt * Nvt + Am)
End If
'Displayed values for allowances text boxes
24 txtVlv_F150_Allow = Avf1
25 txtVlv_F300_Allow = Avf3
26 txtVlv_F600_900_Allow = Avf6
27 txtVlv_F1500_Allow = Avf15
28 txtVlv_S150_Allow = AvS1
29 txtVlv_S300_Allow = Avs3
30 txtVlv_S600_900_Allow = Avs6
31 txtVlv_S1500_Allow = AvS15
32 txtFl_Allow = Af
33 txtVt_Allow = Avt
34 txtDr_Allow = Ad
35 txtPump_Allow = Apm
36 txtAn_Supp_Allow = Aaps
37 txtDum_Leg_Supp_Allow = Adls
38 txtElb45_Allow = AElb45
39 txtElb90_Allow = AElb90
40 txtVariance = Ap
41 RsAllow.Close
42 Set RsAllow = Nothing
'Debug.Print Ap, Af, Avf1, Avf3
Exit Function
ExitSub:
43 MsgBox Err.Description
End Function