Function doesn't work

**HS_1** · 05-06-2018, 12:54 AM

Wonder if somebody can help me, I am beginner in programming and trying to execute function I located in Module which doesn't work. Basically it should grab some data from table "tb_pipeallow", which matches field "p_size" ="size" in table "TB_Main_Length", multiply quantity by allowance and display the sum on a form in a filed "txtLen" by a click of the button.

I can't figure it out what I am doing wrong, thanks in advance HS_1

' Procedure Name: CabLen
Public Function cablen(Dp As Single, lp As Single, nf As Integer, Nvf1 As Integer, Nvf3 As Integer) As Single

Dp = nz(Size, 0)
lp = nz(P_lgth, 0)
nf = nz(Fl_Qty, 0)
Nvf1 = nz(Vlv_F150_Qty, 0)
Nvf3 = nz(Vlv_F300_Qty, 0)
Debug.Print Dp, lp, nf, Nvf1, Nvf3

Dim Ap As Single
Dim Af As Single
Dim Avf1 As Single
Dim Avf3 As Single
Dim strAllow As String
Dim RsAllow As Recordset

Ap = nz(RsAllow.Fields("p_allow"), 0)
Af = nz(RsAllow.Fields("Fl"), 0)
Avf1 = nz(RsAllow.Fields("Vlv_F150"), 0)
Avf3 = nz(RsAllow.Fields("Vlv_F300"), 0)
strAllow = "select p_allow, Vlv_F150, Vlv_F300, fl, from tb_pipeallow where abs(p_size-" & Dp & ")<1e-5"

Set RsAllow = db.OpenRecordset(strAllow)
If RsAllow.EOF Then
MsgBox ("No data selected from tb_pipeallow")
Exit Function
End If

RsAllow.Close

Set RsAllow = Nothing
Debug.Print Ap, Af, Avf1, Avf3

cablen = (Ap * lp + Af * nf + Avf1 * Nvf1 + Avf3 * Nvf3)

Exit Function
exitsub:
MsgBox Err.Description
End Function

Private Sub btnCalc_Click()
Dim cablen As Integer
Me.txtHLen = cablen

End Sub

**ssanfu** · 05-06-2018, 01:28 AM

You have

Code:

Private Sub btnCalc_Click()
    Dim cablen As Integer   '<<<<--- Not needed/ Shouldn't be there
    Me.txtHLen = cablen      '<<<<--- This is the name of a function, but you are NOT entering the required arguments!
End Sub

Provided these are the names of controls on a form: "Size, P_lgth, Fl_Qty, Vlv_F150_Qty, Vlv_F300_Qty", The button click would be

Code:

Private Sub btnCalc_Click()
    Me.txtHLen = cablen(Nz(Size, 0), Nz(P_lgth, 0), Nz(Fl_Qty, 0), Nz(Vlv_F150_Qty, 0), Nz(Vlv_F300_Qty, 0))
End Sub

Be aware that "Size" is a reserved word in Access and shouldn't be used as an object name.
It also looks like you have repeating fields : "Vlv_F150_Qty, Vlv_F300_Qty".

The function (rearranged/rewritten) would be something like:

Code:

Public Function cablen(Dp As Single, lp As Single, nf As Integer, Nvf1 As Integer, Nvf3 As Integer) As Single
    On Error GoTo exitsub

    Dim Ap As Single
    Dim Af As Single
    Dim Avf1 As Single
    Dim Avf3 As Single
    Dim strAllow As String
    Dim RsAllow As DAO.Recordset

    Debug.Print Dp, lp, nf, Nvf1, Nvf3

    'set default return value
    cablen = 0

    strAllow = "select p_allow, Vlv_F150, Vlv_F300, fl, from tb_pipeallow where abs(p_size-" & Dp & ")<1e-5" '<< I'm not sure about this SQL - think it will error
    Debug.Print strAllow

    Set RsAllow = db.OpenRecordset(strAllow)
    If RsAllow.BOF And RsAllow.EOF Then
        MsgBox ("No data selected from tb_pipeallow")
    Else
        Ap = Nz(RsAllow.Fields("p_allow"), 0)
        Af = Nz(RsAllow.Fields("Fl"), 0)
        Avf1 = Nz(RsAllow.Fields("Vlv_F150"), 0)
        Avf3 = Nz(RsAllow.Fields("Vlv_F300"), 0)
        cablen = (Ap * lp + Af * nf + Avf1 * Nvf1 + Avf3 * Nvf3)
    End If

    RsAllow.Close
    Set RsAllow = Nothing
    Debug.Print Ap, Af, Avf1, Avf3

    Exit Function
exitsub:
    MsgBox Err.Description
End Function

Alert!! Code is untested!!

**CJ_London** · 05-06-2018, 05:28 AM

<< I'm not sure about this SQL - think it will error

1e-5 is scientific notation - as a value it is 0.00001

**HS_1** · 05-07-2018, 10:42 AM

ssanfu, thank you for your help, I have made all the changes as you suggested but I am getting an error "Object required" when I click on the button and calculated value on a form defaults to "0"
I have no idea how to correct it.

Originally Posted by HS_1

Wonder if somebody can help me, I am beginner in programming and trying to execute function I located in Module which doesn't work. Basically it should grab some data from table "tb_pipeallow", which matches field "p_size" ="size" in table "TB_Main_Length", multiply quantity by allowance and display the sum on a form in a filed "txtLen" by a click of the button.

I can't figure it out what I am doing wrong, thanks in advance HS_1

' Procedure Name: CabLen
Public Function cablen(Dp As Single, lp As Single, nf As Integer, Nvf1 As Integer, Nvf3 As Integer) As Single

Dp = nz(Size, 0)
lp = nz(P_lgth, 0)
nf = nz(Fl_Qty, 0)
Nvf1 = nz(Vlv_F150_Qty, 0)
Nvf3 = nz(Vlv_F300_Qty, 0)
Debug.Print Dp, lp, nf, Nvf1, Nvf3

Dim Ap As Single
Dim Af As Single
Dim Avf1 As Single
Dim Avf3 As Single
Dim strAllow As String
Dim RsAllow As Recordset

Ap = nz(RsAllow.Fields("p_allow"), 0)
Af = nz(RsAllow.Fields("Fl"), 0)
Avf1 = nz(RsAllow.Fields("Vlv_F150"), 0)
Avf3 = nz(RsAllow.Fields("Vlv_F300"), 0)
strAllow = "select p_allow, Vlv_F150, Vlv_F300, fl, from tb_pipeallow where abs(p_size-" & Dp & ")<1e-5"

Set RsAllow = db.OpenRecordset(strAllow)
If RsAllow.EOF Then
MsgBox ("No data selected from tb_pipeallow")
Exit Function
End If

RsAllow.Close
Set RsAllow = Nothing
Debug.Print Ap, Af, Avf1, Avf3

cablen = (Ap * lp + Af * nf + Avf1 * Nvf1 + Avf3 * Nvf3)

Exit Function
exitsub:
MsgBox Err.Description
End Function

Private Sub btnCalc_Click()
Dim cablen As Integer
Me.txtHLen = cablen

End Sub

**isladogs** · 05-07-2018, 10:53 AM

Suggest you post the code you are using now

**Micron** · 05-07-2018, 12:08 PM

I think he did, and it's a mish-mash of the 2 functions. Error is probably due to trying to access the recordset before it has been declared (discernable in the code). Even if that wasn't the case, it's fundamentally flawed to pass variables to a function, then re-assign them right off the bat:
Public Function cablen(Dp As Single, lp As Single,...
then
Dp = nz(Size, 0)
lp = nz(P_lgth, 0)
...

**isladogs** · 05-07-2018, 12:59 PM

If that's the case, the OP has ignored many/all of ssanfu's suggestions

**HS_1** · 05-07-2018, 02:28 PM

Originally Posted by ridders52

Suggest you post the code you are using now

Ridders52
Below is the code I am using now. To be completely honest this function was written by somebody else and I am trying to modify it to work for my needs with very limited vba knowledge. I understand how the constant values are extracted from tb_pipeallow table and passed to function but I have problem understanding how the variable quantity and length from tb_main_length are related and red by the function.

First table and column names: (stores quanity and length for each line_no)
tb_Main_Length
Line_No, L_Size, M_lgth, Vlv_F150_Qty, Vlv_F300_Qty, Fl_Qty, HLngth

Second table
Tb_PipeAllow
p_allow, Vlv_F150, Vlv_F300, fl
There is relationship: one to many between p_all (Tb_PipeAllow) and L_size (tb_Main_Length)

Public Function cablen(Dp As Single, lp As Single, nf As Integer, Nvf1 As Integer, Nvf3 As Integer) As Single
On Error GoTo exitsub

Dim Ap As Single
Dim Af As Single
Dim Avf1 As Single
Dim Avf3 As Single
Dim strAllow As String
Dim RsAllow As DAO.Recordset

Debug.Print Dp, lp, nf, Nvf1, Nvf3

'set default return value
cablen = 0

strAllow = "select p_allow, Vlv_F150, Vlv_F300, fl, from tb_pipeallow where abs(p_size-" & Dp & ")<1e-5" '<< I'm not sure about this SQL - think it will error
Debug.Print strAllow

Set RsAllow = db.OpenRecordset(strAllow)
If RsAllow.BOF And RsAllow.EOF Then
MsgBox ("No data selected from tb_pipeallow")
Else
Ap = Nz(RsAllow.Fields("p_allow"), 0)
Af = Nz(RsAllow.Fields("Fl"), 0)
Avf1 = Nz(RsAllow.Fields("Vlv_F150"), 0)
Avf3 = Nz(RsAllow.Fields("Vlv_F300"), 0)
cablen = (Ap * lp + Af * nf + Avf1 * Nvf1 + Avf3 * Nvf3)
End If

RsAllow.Close
Set RsAllow = Nothing
Debug.Print Ap, Af, Avf1, Avf3

Exit Function
exitsub:
MsgBox Err.Description
End Function

Private Sub btnCalc_Click()
Me.txtHLen = cablen(Nz(L_size, 0), Nz(M_lgth, 0), Nz(Fl_Qty, 0), Nz(Vlv_F150_Qty, 0), Nz(Vlv_F300_Qty, 0))
End Sub

**HS_1** · 05-07-2018, 02:39 PM

Sorry I have made few typing mistakes marked in red when writing the response,
here is the "correct one".

Ridders52
Below is the code I am using now. To be completely honest this function was written by somebody else and I am trying to modify it to work for my needs with very limited vba knowledge. I understand how the constant values are extracted from tb_pipeallow table and passed to function but I have problem understanding how the variable quantity and length from tb_main_length are related and red by the function.

First table and column names: (stores quanity and length for each line_no)
tb_Main_Length
Line_No, L_Size, M_lgth, Vlv_F150_Qty, Vlv_F300_Qty, Fl_Qty, HLngth

Second table
Tb_PipeAllow
P_size, p_allow, Vlv_F150, Vlv_F300, fl
There is relationship: one to many between p_size (Tb_PipeAllow) and L_size (tb_Main_Length)

Public Function cablen(Dp As Single, lp As Single, nf As Integer, Nvf1 As Integer, Nvf3 As Integer) As Single
On Error GoTo exitsub

Dim Ap As Single
Dim Af As Single
Dim Avf1 As Single
Dim Avf3 As Single
Dim strAllow As String
Dim RsAllow As DAO.Recordset

Debug.Print Dp, lp, nf, Nvf1, Nvf3

'set default return value
cablen = 0

strAllow = "select p_allow, Vlv_F150, Vlv_F300, fl, from tb_pipeallow where abs(p_size-" & Dp & ")<1e-5" '<< I'm not sure about this SQL - think it will error
Debug.Print strAllow

Set RsAllow = db.OpenRecordset(strAllow)
If RsAllow.BOF And RsAllow.EOF Then
MsgBox ("No data selected from tb_pipeallow")
Else
Ap = Nz(RsAllow.Fields("p_allow"), 0)
Af = Nz(RsAllow.Fields("Fl"), 0)
Avf1 = Nz(RsAllow.Fields("Vlv_F150"), 0)
Avf3 = Nz(RsAllow.Fields("Vlv_F300"), 0)
cablen = (Ap * lp + Af * nf + Avf1 * Nvf1 + Avf3 * Nvf3)
End If

RsAllow.Close
Set RsAllow = Nothing
Debug.Print Ap, Af, Avf1, Avf3

Exit Function
exitsub:
MsgBox Err.Description
End Function

Private Sub btnCalc_Click()
Me. HLngth = cablen(Nz(L_size, 0), Nz(M_lgth, 0), Nz(Fl_Qty, 0), Nz(Vlv_F150_Qty, 0), Nz(Vlv_F300_Qty, 0))
End Sub

Originally Posted by HS_1

Ridders52
Below is the code I am using now. To be completely honest this function was written by somebody else and I am trying to modify it to work for my needs with very limited vba knowledge. I understand how the constant values are extracted from tb_pipeallow table and passed to function but I have problem understanding how the variable quantity and length from tb_main_length are related and red by the function.

First table and column names: (stores quanity and length for each line_no)
tb_Main_Length
Line_No, L_Size, M_lgth, Vlv_F150_Qty, Vlv_F300_Qty, Fl_Qty, HLngth

Second table
Tb_PipeAllow
p_allow, Vlv_F150, Vlv_F300, fl
There is relationship: one to many between p_all (Tb_PipeAllow) and L_size (tb_Main_Length)

Public Function cablen(Dp As Single, lp As Single, nf As Integer, Nvf1 As Integer, Nvf3 As Integer) As Single
On Error GoTo exitsub

Dim Ap As Single
Dim Af As Single
Dim Avf1 As Single
Dim Avf3 As Single
Dim strAllow As String
Dim RsAllow As DAO.Recordset

Debug.Print Dp, lp, nf, Nvf1, Nvf3

'set default return value
cablen = 0

strAllow = "select p_allow, Vlv_F150, Vlv_F300, fl, from tb_pipeallow where abs(p_size-" & Dp & ")<1e-5" '<< I'm not sure about this SQL - think it will error
Debug.Print strAllow

Set RsAllow = db.OpenRecordset(strAllow)
If RsAllow.BOF And RsAllow.EOF Then
MsgBox ("No data selected from tb_pipeallow")
Else
Ap = Nz(RsAllow.Fields("p_allow"), 0)
Af = Nz(RsAllow.Fields("Fl"), 0)
Avf1 = Nz(RsAllow.Fields("Vlv_F150"), 0)
Avf3 = Nz(RsAllow.Fields("Vlv_F300"), 0)
cablen = (Ap * lp + Af * nf + Avf1 * Nvf1 + Avf3 * Nvf3)
End If

RsAllow.Close
Set RsAllow = Nothing
Debug.Print Ap, Af, Avf1, Avf3

Exit Function
exitsub:
MsgBox Err.Description
End Function

Private Sub btnCalc_Click()
Me.txtHLen = cablen(Nz(L_size, 0), Nz(M_lgth, 0), Nz(Fl_Qty, 0), Nz(Vlv_F150_Qty, 0), Nz(Vlv_F300_Qty, 0))
End Sub

**ssanfu** · 05-07-2018, 03:22 PM

Whoops!!

I just noticed an error in the SQL line.

There should NOT be a comma between fl and FROM.

Code:

strAllow = "SELECT p_allow, Vlv_F150, Vlv_F300, fl, FROM tb_pipeallow WHERE abs(p_size-" & Dp & ") < 0.00001"

There is relationship: one to many between p_size (Tb_PipeAllow) and L_size (tb_Main_Length)

So "p_size" is the PK field in "Tb_PipeAllow"? (Each value in "p_allow" is unique?)

You could post your dB to make it easier to troubleshoot. Just need the form, tables/queries involved and some examples of what you expect to get...

**Micron** · 05-07-2018, 07:17 PM

HS_1: If you want to make your posts easier for us to read:
- don't quote every bit of every prior reply
- use code tags and proper indentation in and around your code.
Both of these things will make your posts less arduous to read.

**HS_1** · 05-07-2018, 09:32 PM

Ssanfu, here is the database for the code.

Originally Posted by ssanfu

Whoops!!

I just noticed an error in the SQL line.

There should NOT be a comma between fl and FROM.

Code:

strAllow = "SELECT p_allow, Vlv_F150, Vlv_F300, fl, FROM tb_pipeallow WHERE abs(p_size-" & Dp & ") < 0.00001"

So "p_size" is the PK field in "Tb_PipeAllow"? (Each value in "p_allow" is unique?)

You could post your dB to make it easier to troubleshoot. Just need the form, tables/queries involved and some examples of what you expect to get...

**Micron** · 05-07-2018, 10:12 PM

Well, so much for not quoting everything.
Ciao.

**HS_1** · 05-08-2018, 02:17 PM

What do you mean by not quoting everything? I don’t have lots of interaction on this forum so I don’t know what’s expected from me when asking for help

Originally Posted by Micron

Well, so much for not quoting everything.
Ciao.

**ssanfu** · 05-08-2018, 04:37 PM

Originally Posted by HS_1

What do you mean by not quoting everything?

See how I cut down your quote?

Look at Micron's post # 13. Nothing quoted. It is not necessary to quote the entire post - look at your Post #9.

When you post code, click the '#' in the reply header... put the code between the code tags.

So I looked at your dB - corrected a few things. You were getting an error because you were using "L_Size" instead of "txtL_Size".
I added the "cablelen" calculation to the query. Now, anytime you change one of the values, the traced length is automatically calculated.

See if this is what you want.....

Function doesn't work

Thread Tools

Function doesn't work

code I am using now

dbase

Similar Threads

IIF Function Doesn't work in a control souce expression.

Custom Function to Create Quarter Date Range Doesn't Work In Query

sending email through Access function doesn't work when client is on Exchange

Function Shell&Wait, used to work with access 2007, doesn't with 2013

Fields function doesn't work for primary key/null field

Posting Permissions