Results 1 to 6 of 6
  1. #1
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56

    Post Can I do this in a function call itself

    I don't understand why my function loses it's value before returning it. When it find the return value instead of returning i. It goes back up to the else part of the statement one last time and sets itself to nothing


    Public Function qryPwrPL(keyEquipUp As Long) As String


    Dim strSQL As String
    Dim QD As QueryDef
    Dim Db As DAO.Database
    Dim rs As DAO.Recordset
    Dim PreviousKey As Long
    Dim PowerPanel As String


    'KeyEquipUp = 10081836
    Set Db = CurrentDb
    PreviousKey = keyEquipUp


    strSQL = ""
    strSQL = "qryPwrPL"
    Set QD = Db.QueryDefs(strSQL)


    QD.Parameters("ParamKeyEquipUp") = keyEquipUp


    Set rs = QD.OpenRecordset


    If InStr(rs!strFPN, "PWR-PL-") Then
    'Found the Power Panel Name so return it.
    PowerPanel = rs!strFPN ' PreviousKey "PWR-PL-0082"
    qryPwrPL = PowerPanel


    Exit Function
    Else

    If IsNull(rs!keyEquipUp) Then
    qryPwrPL = "0"
    Exit Function
    Else
    ' Keeps searching until we find the PowerPnael Name
    PowerPanel = qryPwrPL(rs!keyEquipUp)
    End If
    End If




    End Function

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Did you try setting a break point and stepping thru the code to see what the actual flow is?

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I would edit the function to use the InStr function properly:
    Code:
    Public Function qryPwrPL(keyEquipUp As Long) As String
    
    
    Dim strSQL As String
    Dim QD As QueryDef
    Dim Db As DAO.Database
    Dim rs As DAO.Recordset
    Dim PreviousKey As Long
    Dim PowerPanel As String
    
    
    'KeyEquipUp = 10081836
    Set Db = CurrentDb
    PreviousKey = keyEquipUp
    
    
    strSQL = ""
    strSQL = "qryPwrPL"
    Set QD = Db.QueryDefs(strSQL)
    QD.Parameters("ParamKeyEquipUp") = keyEquipUp
    
    
    Set rs = QD.OpenRecordset
    
    
    If InStr(rs!strFPN, "PWR-PL-") > 0 Then
         'Found the Power Panel Name so return it.
         PowerPanel = rs!strFPN ' PreviousKey "PWR-PL-0082"
         qryPwrPL = PowerPanel
         Exit Function
    Else
        If IsNull(rs!keyEquipUp) Then
           qryPwrPL = "0"
           Exit Function
        Else
           ' Keeps searching until we find the PowerPnael Name
          PowerPanel = qryPwrPL(rs!keyEquipUp)
         End If
    End If
    End Function
    Also not quite sure about your data, does the query (qryPwrPL) return multiple records for the key? Why use function and not simply a dlookup?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56
    I fixed the Instr as suggested.

    When the value is found it leaves the if statement hit's End function and than for some strange reason goes back to the else statement than exits with no value anymore


    If InStr(rs!strFPN, "PWR-PL-") Then
    'Found the Power Panel Name so return it.
    PowerPanel = rs!strFPN ' PreviousKey "PWR-PL-0082"
    qryPwrPL = PowerPanel


    Exit Function
    Else

    If IsNull(rs!keyEquipUp) Then
    qryPwrPL = "0"
    Exit Function
    Else
    ' Keeps searching until we find the PowerPnael Name
    PowerPanel = qryPwrPL(rs!keyEquipUp)
    End If
    End If




    End Function

  5. #5
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56
    So meant to say it goes back to the end if below PowerPanel = PowerPanel = qryPwrPL(rs!keyEquipUp)

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry but I don't see where you fixed it in your second example. Anyway without the data your function doesn't make much sense , why do you have the recursive loop on the bottom. Why not use a dlookup like this:
    Nz(dlookup("[strFPN]","[qryPwrPL]"),"0") after changing the query to take the key from your form .
    Maybe you can post a small sample db with your code and a couple sample records.
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Listbox Call Function
    By New.User in forum Forms
    Replies: 9
    Last Post: 04-20-2018, 10:23 AM
  2. Replies: 4
    Last Post: 05-04-2017, 02:05 PM
  3. Call function is not executed
    By fluffyvampirekitten in forum Access
    Replies: 12
    Last Post: 11-04-2015, 10:11 AM
  4. can't trace function call
    By visions in forum Programming
    Replies: 14
    Last Post: 12-28-2014, 01:24 PM
  5. Call A function
    By aspen in forum Programming
    Replies: 10
    Last Post: 03-16-2014, 12:57 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