Collect the value from a private sub and use it in another sub on the same form 2

[MrMode]

This is embarrassing, I am having brain freeze...

I have collected a string of values in a sub.

Private Sub CollectString()
'Collect those selected in the list

    Dim strSQL As String
    Dim strwhere As String
    Dim ctl As Control
    Dim varItem As Variant
       
    'make sure a selection has been made
    If Me.ListOfValues.ItemsSelected.Count = 0 Then
    Exit Sub
    End If

    'add selected values to string
    Set ctl = Me.ListOfValues
    For Each varItem In ctl.ItemsSelected
    'Use this line if the value is text
        'strWhere = strWhere & Chr(34) & ctl.ItemData(varItem) & Chr(34) & ","
    'Use this line if your value is numeric
         strwhere = strwhere & ctl.Column(5, varItem) & ","
    Next varItem
      
    'trim trailing comma
    strwhere = Left(strwhere, Len(strwhere) - 1)
            
End Sub

I want to use that string in another sub, rather than using the same block of code again and again.

When I 'Call' it

Private Sub Command39_Click()
Call CollectString
Msgbox CollectString
End Sub

I get an error "Compile Error: Expected Function or variable

If I try to add a variable and call it ...

Private Sub Command39_Click()
Call CollectString(strWhere)
Msgbox strwhere
End Sub

I get the error. "Compile Error: Variable not defined"

If I try and define the variable and then call it

Private Sub Command39_Click()

Dim strValues

strValues = CollectString(strWhere)
MsgBox strValues

End Sub

I get the "Compile Error: Variable not defined"


I know this is entry level stuff, I am just not able to work this one out.

Any help, most appreciated

 

[softhemc]

I haven't programmed in Access for more years than I can remember, but there are a number of solutions one of which would be to make CollectString a Function returning a String and have it return the appropriate value. Then your Command39_Click will work as in your first example above.

NB the Call CollectString would not be necessary as the Function would be called directly by MsgBox.

 

[softhemc]

Another simple approach would be to declare [tt]Private CollectedString As String[/tt] outside of any Sub or Function and then set this value in your CollectString Sub and use it directly in your Command39_Click Event Handler. This would enable you to keep CollectString as a Sub.

 

[MrMode]

Fantastic

Many thanks

 

[Andrzejek]

I think what softhemc wanted to say was: "declare [tt]Private [blue]strwhere[/blue] As String[/tt] outside of any Sub or Function", like in the General Declaration section of your Form. I guess...

But I like his first idea better myself.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MrMode]

Got it, I was just having fuzzy brain day.

Thanks

 

[softhemc]

Andrzejek, no I didn't.

I simply meant create a Form visible variable and at the end of the CollectString sub assign the appropriate value to this Form visible variable. It is not clear that the entire sub was shown here as there is another string variable within the sub. It is for that reason that I didn't specify what to assign to the Form visible variable and left that to MrMode to decide based on the final outcome of the Sub.

 

[1DMF]

As the declaration is 'Private' isn't the only place it is in scope would be the header of where the function is, which being a click event would make it 'top of the form'?

I'm intrigued!


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[softhemc]

Yes, as I said a Form visible (or scope) variable - but what I didn't specify was the specific string to pass. Of course you could go even further and declare the variable in a Module which would have global scope - but I don't think that that would be necessary here.