Passing string variable to function

[HS_1]

I am trying to grab [table name] I store in one of the table fields and pass it to the function but with no luck (The table has only one record).
I am getting an error message "The Microsoft Access database engine cannot find the input table or query 'StrPipeAllowTable'. Make sure it exists and that its name is spelled correctly." I have checked the spelling, and it is correct.
I think the problem is that when I declare in select statement the table name as a string "Dim strPipeAllowTable As Variant" the function doesn't recognize it, probably the double "" unquoting the string has something to do with it.
If I copy the table name directly from the table and replace "strPipeAllowTable" with actual table name in select statement, the function works just fine. Wonder if someone would be able to help me with this one.


Here is the function:

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

[June7]

Have to concatenate variables - as you did with Dp.

from " & strPipeAllowTable & " where

[HS_1]

June7, thank you.

[Micron]

Did you compile that code? I would expect it would not compile, or at least if line 4 is true, line 5 should raise an error. I think here should be no parentheses around the message string, otherwise the function is expecting to pass a return value and there is no variable assigned to it.