CODEWITHSUNDEEP
ms-accessvba
vuyy1182
vuyy1182

Reputation: 1696

How do I use a variable from a different function in another function?

I have one method 

Public CurrentFileNameNoExtension As String
Public Sub importexcelfile()

CurrentFileNameNoExtension ="Filename"
'do something 

End Sub

I want to use CurrentFileNameNoExtension value in onEnter event of the dropdown list(cmvalues) event. That Value use in sql query. My code is 

Private Sub cmvalues_Enter()
Dim qstng As String
qstng = CurrentFileNameNoExtension
Me.cmvalues.RowSourceType = "Table/Query"
Me.cmvalues.RowSource = "Select F1 from " & qstng & " WHERE F1 <> 'Control Model';"

End Sub

But qstng value is empty. it is not giving the value in the importexcelfile() function.

Upvotes: 0

Views: 99

Answers (2)

vuyy1182
vuyy1182

Reputation: 1696

Place this function under Microsoft Access Class Objects Form control,Where cmvalues dropdown exists 

    Public CurrentFileNameNoExtension As String
    Public Sub importexcelfile()

    CurrentFileNameNoExtension ="Filename"
     'do something 

    End Sub

Upvotes: 0

WGS
WGS

Reputation: 14179

EDIT: As I've just noticed, thanks to @simoco, that this is indeed for a userform, there are actually a couple of things to pull this off. One is using globals, which is quite tricky, and another is to use a function to get the string you want.

Function CurrentFileNameNoExtension() As String
    'Do some FSO or GetOpenFileName here.
    CurrentFileNameNoExtension = "Filename"
End Sub

Private Sub cmvalues_Enter()
    qstng = CurrentFileNameNoExtension
    Me.cmvalues.RowSourceType = "Table/Query"
    Me.cmvalues.RowSource = "Select F1 from " & strFileName & " WHERE F1 <> 'Control Model';"
End Sub

There is not much of an issue using the code you have, really. You just have to make sure that the first sub is called before the second one so that cmvalues_Enter has a valid string to process.

Upvotes: 1

Related Questions