Ruturning a Value from a Function in a Module

[kieriosity]

I have the following bit of code placed in a module:

Function selectCriteria(ctl)

'Flush out the Criteria variable before adding new data
Criteria = ""

'Loop through the list box and build a string
'for all items selected
For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm
'If no items are selected in the list box then show a message
'box telling the user they need to select an item.
If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made&quot
Exit Function
End If

End Function

I'm calling the function from a form with a list box.

Set ctl = Me![DisciplineList]
Call basSelectCriteria.selectCriteria(ctl)

I can't get it to return a value to the form. I can if the function is in the same form and not in a module, but I have several forms that need to use this function. I need to put the value in the criteria variable so I can use it to build an SQL query. Any ideas? Thanks.

 

[scriverb]

The reason for your problem is that the variable Criteria is what you are using to return the string of infomation to your Form. If you want the Function to return the value so you can make an assignment from it then add a line after your Loop that says:
[COLOR=blue
SelectCriteria = Criteria[COLOR=black]
Now in your Form the call to the function should be something like this:
Dim vResults as string
Set ctl = Me![DisciplineList]
vResults=Call basSelectCriteria.selectCriteria(ctl)
Now your function is working as a true Function with a value returning from it. Prior to this you were basically using the Function call like Subroutine call and using a variable that was being created within the Function.

The reason that the variable is available within your Form if the Function was stored locally is that the form recognizes the locally created variable. But, when you moved the function to the database Module location the variable Criteria no longer is recognized. If you would want to still use Criteria then create a Global variable Criteria. Now when you store the string in Criteria it is available everywhere.

I hope this clears up your problem for you.

Bob Scriver

 

[kieriosity]

Thanks for your reply. I got it to work, but without using the vResults=Call basSelectCriteria.selectCriteria(ctl). This would not work for me. I simply declared a global variable. Thanks.

 

[MichaelRed]

being somewhat more conservative and seeing some utility in expanding the 'error' information, I did it slightly differently:

Function basSelCrit(Frm As String, Ctrl As String) As String



    'Sampe Usage:
'    Private Sub cmdCallSel_Click()
'    Dim MyCrit As String
'    MyCrit = basSelCrit(Me.Name, Me.List27.Name)        '(Me.Controls("List27"))
'    Debug.Print MyCrit
'    End Sub

    Dim MyCrit As String
    Dim Idx As Integer
    Dim MyCtrl As Control

    Dim MyMsg As String
    Dim MyTitle As String

    Set MyCtrl = Forms(Frm).Controls(Ctrl)
    Idx = 0

    While Idx <= MyCtrl.ListCount
        If (MyCtrl.Selected(Idx)) Then
            If Len(MyCrit) = 0 Then
                MyCrit = Chr(34) & MyCtrl.ItemData(Idx) & Chr(34)
            Else
                MyCrit = MyCrit & &quot;,&quot; & Chr(34) & MyCtrl.ItemData(Idx) _
                & Chr(34)
            End If
        End If
        Idx = Idx + 1
     Wend

     'If no items are selected in the list box then show a message
     'box telling the user they need to select an item.
     If Len(MyCrit) = 0 Then
         MyMsg = &quot;You must select one or more items in the ListBox&quot;
         MyTitle = Forms(Frm).Caption & &quot;No Selection Made&quot;
         MsgBox MyMsg, vbOKOnly, MyTitle
         Exit Function
     End If

    basSelCrit = MyCrit

End Function

The inclusion/use of the form is, of course, not strictly speaking necessary, however it does add to the ability to provide user feedback. The change from the "ItemsSelected" collection to the investigation of the selected property is somewhat more debateable. If the rowsource for the list box were a recordset which could return a large number of items with only a few selected, the additional processing might be objectionable -on the other hand- selection from a large list might be equally objectionable, so (for me at least) the large list is objectionable to begin with and the machine processing thereof would be less of a problem than the human inconvenience.

Of course, the entire issue begs for some error checking, and it seems more reasonable to do the check of the fact that at least one item is selected at the form level, although doing so does add some code effort to the form.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[scriverb]

Add the Red line of code to your Function as below. Now you Function will return a value to be assigned to whatever you want in your form controls or code:
Function selectCriteria(ctl)
'Flush out the Criteria variable before adding new data
Criteria = ""
'Loop through the list box and build a string
'for all items selected
For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm
selectCriteria = Criteria
'If no items are selected in the list box then show a message
'box telling the user they need to select an item.
If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made&quot
Exit Function
End If
End Function

Let me know if you can't get it to work like this. Bob Scriver

 

[wjmsm]

normally to get a function to return a value you specify the function signature as follows

Function myFunction(passedParm as string) as String
' The as String after the closing parens denotes the type of data returned by the function and the fact that the function returns something

End Function