Red comments need new design considerations...
Delete all the code in SuppLen module. The code belongs behind the form like this:
Code:
Option Compare Database
Option Explicit
Private Sub btnCalcVLngth_Click()
' syntax errors here, commented out.
' Me.txtVLngth = Round(VLen(Nz([Vlv1_Qty], 0), Nz([Vlv2_Qty], 0), Nz([Vlv3_Qty], 0), Nz([Passes], 0)), 0)
' Me.TxtVlv1Allow = AVlv1 & "/" & AVlv1 * txtVlv1_Qty
End Sub
Private Sub btnCalcSLngth_Click()
'made up value for Nr..
txtSLngth = fcnSLen(4, txtSup3_Qty, txtSup2_Qty, txtSup1_Qty)
End Sub
Public Function fcnSLen(Nr As Integer, Ns1 As Integer, Ns2 As Integer, Ns3 As Integer) As Single
On Error GoTo exitFunction
Dim Sup1 As String
Dim Sup2 As String
Dim Sup3 As String
Dim AS1 As Single
Dim AS2 As Single
Dim AS3 As Single
Dim strSupAllow As String
Dim RsSupAllow As DAO.Recordset
'set default return value
fcnSLen = 0
'NOTE - only the third string will be evaluated..
strSupAllow = "select SupLngt from tb_Sup where SupType = '" & CmbSup1 & "';"
strSupAllow = "select SupLngt from tb_Sup where SupType = '" & CmbSup2 & "';"
strSupAllow = "select SupLngt from tb_Sup where SupType = '" & CmbSup3 & "';"
'Debug.Print strAllow
Set RsSupAllow = CurrentDb.OpenRecordset(strSupAllow)
If RsSupAllow.BOF And RsSupAllow.EOF Then
MsgBox ("No data selected from tb_Sup")
Else
AS1 = Nz(RsSupAllow.Fields("SupLngt"), 0)
AS2 = Nz(RsSupAllow.Fields("SupLngt"), 0)
AS3 = Nz(RsSupAllow.Fields("SupLngt"), 0)
fcnSLen = (AS1 * Ns1 + AS2 * Ns2 + AS3 * Ns3) * Nr
'SLen = (AS1 * Ns1) * Nr
End If
RsSupAllow.Close
Set RsSupAllow = Nothing
Exit Function
exitFunction:
MsgBox Err.Description
End Function