Results 1 to 14 of 14
  1. #1
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107

    How to pass records to function from unrelated tables

    I am trying to modify program ssanfu helped me to develop on this forum by limiting number of columns on continues form (33). After countless hours is time to ask experts for help, please find attached dBase. I am trying to pass allowance values to functions using sql select statement but no luck so far. Here is what I am trying to accomplish:
    1. “Sup” Combo boxes ‘CmbSup1/2/3’ should read table [tb_Sup] and based on user selection pass [suplen] value to function SuppLen, at the same time display allowance in txtboxes ‘TxtSup1Allow1/2/3’ (right below ‘CmbSup1/2/3’) in the following format: [tb_sup].[suplen]’/’[tb_sup].[suplen] * ’txtSup1_Qty’





    1. “Vlv” Combo boxes CmbSup1/2/3 read table [tb_pipeallow] which contains names of columns in table [tb_Main_length] and based on vlv type selection and [L_size] sql function passes the right allowance to function ‘ValveLen’


    For example, if user chooses ‘Vlv_F300’ and [L_size] is 8” the allowance passed to function should be 2438 mm.
    Thanks hs_1
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you make an update query, the field that gets updated will get the function:
    [tb_sup].[suplen]/[tb_sup].[suplen] * forms!myForm!txtSup1_Qty

  3. #3
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107

    dbase

    I made few corrections to SLen function and now I am getting an error "No data selected from tb_Sup" Can somebody tell me what I am doing wrong that the function can't select the value:
    Code:
    Public Function SLen(Nr As Integer, Ns1 As Integer, Ns2 As Integer, Ns3 As Integer) As Single
    
    
    
    
    
        On Error GoTo exitsub
        
        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
    
    
        'Debug.Print Dp, Lp, Nr,
    
    
        'set default return value
        SLen = 0
    
    
        strSupAllow = "select SupLngt from tb_Sup where SupType = '" & Sup1 & "';"
        strSupAllow = "select SupLngt from tb_Sup where SupType = '" & Sup2 & "';"
        strSupAllow = "select SupLngt from tb_Sup where SupType = '" & Sup3 & "';"
        '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)
            
            SLen = (AS1 * Ns1 + AS2 * Ns2 + AS3 * Ns3) * Nr
            'SLen = (AS1 * Ns1) * Nr
        End If
    
    
        RsSupAllow.Close
        Set RsSupAllow = Nothing
        'Debug.Print Ap, S, Nr
    
    
      
        
        Exit Function
    exitsub:
        MsgBox Err.Description
    End Function
    Quote Originally Posted by HS_1 View Post
    I am trying to modify program ssanfu helped me to develop on this forum by limiting number of columns on continues form (33). After countless hours is time to ask experts for help, please find attached dBase. I am trying to pass allowance values to functions using sql select statement but no luck so far. Here is what I am trying to accomplish:
    1. “Sup” Combo boxes ‘CmbSup1/2/3’ should read table [tb_Sup] and based on user selection pass [suplen] value to function SuppLen, at the same time display allowance in txtboxes ‘TxtSup1Allow1/2/3’ (right below ‘CmbSup1/2/3’) in the following format: [tb_sup].[suplen]’/’[tb_sup].[suplen] * ’txtSup1_Qty’



    1. “Vlv” Combo boxes CmbSup1/2/3 read table [tb_pipeallow] which contains names of columns in table [tb_Main_length] and based on vlv type selection and [L_size] sql function passes the right allowance to function ‘ValveLen’


    For example, if user chooses ‘Vlv_F300’ and [L_size] is 8” the allowance passed to function should be 2438 mm.
    Thanks hs_1

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The 3 Sup variables are never set, so the recordset will be empty. You can't open a recordset on 3 strings like that; it will open on the third string. Each one is replacing the previous.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    “Sup” Combo boxes ‘CmbSup1/2/3’ should read table [tb_Sup] and based on user selection pass [suplen] value to function SuppLen, at the same time display allowance in txtboxes ‘TxtSup1Allow1/2/3’ (right below ‘CmbSup1/2/3’) in the following format: [tb_sup].[suplen]’/’[tb_sup].[suplen] * ’txtSup1_Qty’
    Need some clarification here. First, tb_sup does not contain the field "suplen".
    You do not have a function named SuppLen.
    Second, a number divided by itself is always equal to one, so if this is meant to be a math expression, [tb_sup].[suplen]’/’[tb_sup].[suplen] * ’txtSup1_Qty’ the value would always be simply txtSup1_Qty.

    I cannot see anywhere that you call function sLen, so it's a mystery what the 4 arguments are.

  6. #6
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    Davegri,
    1. The function is called SLen and is located in Modules under SuppLen (I am going to correct it and rename it to match the function name)
    2. What was my intention is to show in txtSupAll1 box two values: single allowance [SupLngt] and the total: allowance * quantity ([SupLngt]* txtSup1_Qty) separated by "/" character

  7. #7
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    pbaldy, does it mean I have to declare 3 separate strings and Rs, one for each Sup or there is other way around?

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Either that, 3 DLookup()'s or perhaps a single recordset with criteria that pulled all 3 records, and you loop that recordset.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    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

  10. #10
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107

    looping code doesn't loop

    Code:
    Public Function fcnSLen(Nr As Double, 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 & "';"
       
    
    
        Set RsSupAllow = CurrentDb.OpenRecordset(strSupAllow)
        'If RsSupAllow.BOF And RsSupAllow.EOF Then
           
            With RsSupAllow
            If Not .BOF And Not .EOF Then
            
            .MoveLast
            .MoveFirst
            'Debug.Print RsSupAllow("SupLngt")
            
            While (Not .EOF)
            
            
            AS1 = Nz(RsSupAllow.Fields("SupLngt"), 0)
            AS2 = Nz(RsSupAllow.Fields("SupLngt"), 0)
            AS3 = Nz(RsSupAllow.Fields("SupLngt"), 0)
            .MoveNext
            
            Wend
            End If
            
            fcnSLen = ((AS1 * Ns1 + AS2 * Ns2 + AS3 * Ns3) * Nr)
           
         Me.TxtSup1Allow = AS1
         Me.TxtSup2Allow = AS2
         Me.TxtSup3Allow = AS3
        
        RsSupAllow.Close
        End With
        Set RsSupAllow = Nothing
        Exit Function
    exitFunction:
        MsgBox Err.Description
    End Function
    HTML Code:
    This is my first looping code and what's a surprise, it doesn't work, could you tell me what I am doing wrong that it  doesn't loop?

  11. #11
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    How do you know? You'll only see the result of the last record. Put

    debug.print fcnSLen

    inside the loop and see what happens.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This is where you'll see the result:

    http://www.baldyweb.com/ImmediateWindow.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Take a look at this:
    Attachment 34281
    I Don't think you want to loop or even use a query.
    Cleaned up the code and deleted all the dross.

    Use the dropdowns to select the sup types and enter sup quantities, then click CALC under sLen.
    If that is what you want, you can for the most part duplicate the code for calculating vLen.
    Last edited by davegri; 06-04-2018 at 07:32 AM.

  14. #14
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    Code:
    Code:
    Public Function fcnSLen(Nr As Double, 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 strSup1Allow As String
        Dim RsSup1Allow As DAO.Recordset
        
        Dim RsSup2Allow As DAO.Recordset
        Dim strSup2Allow As String
        
        Dim strSup3Allow As String
        Dim RsSup3Allow As DAO.Recordset
        'set default return value
        fcnSLen = 0
        'NOTE - only the third string will be evaluated..
            strSup1Allow = "select SupLngt from tb_Sup where SupType = '" & CmbSup1 & "';"
            'Debug.Print strSupAllow
            strSup2Allow = "select SupLngt from tb_Sup where SupType = '" & CmbSup2 & "';"
            'Debug.Print strSupAllow
            strSup3Allow = " select SupLngt from tb_Sup where SupType = '" & CmbSup3 & "';"
            'Debug.Print strSupAllow
       
    
    
        Set RsSup1Allow = CurrentDb.OpenRecordset(strSup1Allow)
           
            With RsSup1Allow
            If Not .BOF And Not .EOF Then
            .MoveLast
            .MoveFirst
            While (Not .EOF)
            AS1 = Nz(RsSup1Allow.Fields("SupLngt"), 0)
            Debug.Print AS1
            .MoveNext
            Wend
            End If
            End With
            
            
            Set RsSup2Allow = CurrentDb.OpenRecordset(strSup2Allow)
            With RsSup2Allow
            If Not .BOF And Not .EOF Then
            .MoveLast
            .MoveFirst
            While (Not .EOF)
           AS2 = Nz(RsSup2Allow.Fields("SupLngt"), 0)
            .MoveNext
            Wend
            End If
            End With
            Set RsSup3Allow = CurrentDb.OpenRecordset(strSup3Allow)
            With RsSup3Allow
            If Not .BOF And Not .EOF Then
            .MoveLast
            .MoveFirst
            While (Not .EOF)
            AS3 = Nz(RsSup3Allow.Fields("SupLngt"), 0)
            .MoveNext
            Wend
            End If
            End With
            
            fcnSLen = ((AS1 * Ns1 + AS2 * Ns2 + AS3 * Ns3) * Nr)
              'Debug.Print fcnSLen
         
         Me.TxtSup1Allow = AS1
         Me.TxtSup2Allow = AS2
         Me.TxtSup3Allow = AS3
        
        RsSup1Allow.Close
        RsSup2Allow.Close
        RsSup3Allow.Close
        
        Set RsSup1Allow = Nothing
        Set RsSup2Allow = Nothing
        Set RsSup3Allow = Nothing
        Exit Function
    exitFunction:
        MsgBox Err.Description
    End Function
    Code:
    HTML Code:
    Thanks for the advice, it works, the debug.print code is a good function to know. The code I ended up with seams long, wonder if there is a easiest way to do it?
    Quote Originally Posted by pbaldy View Post
    This is where you'll see the result:

    http://www.baldyweb.com/ImmediateWindow.htm

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Two Unrelated Tables and Have to Add together
    By yamalady in forum Queries
    Replies: 3
    Last Post: 11-22-2013, 09:19 AM
  2. Replies: 7
    Last Post: 03-27-2013, 02:26 PM
  3. two unrelated tables and parameter query
    By Seito in forum Queries
    Replies: 2
    Last Post: 09-12-2011, 03:01 PM
  4. Combining results from unrelated tables
    By jwreding in forum Queries
    Replies: 7
    Last Post: 08-12-2011, 01:19 PM
  5. Combine two unrelated tables with the same fields
    By lghovden06 in forum Queries
    Replies: 4
    Last Post: 07-27-2010, 03:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums