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

Question

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.

Kindly see this answer for an alternative. Reason is, globals are kind of tricky to use, so passing in to another sub is also a viable idea. :) — WGS
– WGS, Commented Feb 21, 2014 at 19:23
what is the value of CurrentFileNameNoExtension? Are you sure it isn't empty when cmvalues_Enter is called? I.e. are you sure importexcelfile() has been called first? — Cor_Blimey
– Cor_Blimey, Commented Feb 21, 2014 at 19:53
Yes importexcelfile() called first from where i get CurrentFileNameNoExtension value. I checked it has filename. — vuyy1182
– vuyy1182, Commented Feb 21, 2014 at 20:02

WGS · Accepted Answer · 2014-02-21 20:04:11Z

1

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.

edited Feb 21, 2014 at 20:04

answered Feb 21, 2014 at 19:26

WGS

14.2k5 gold badges50 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

vuyy1182 Over a year ago

It is throwing an exception says Procedur declaration does not match or procedure having the samename when form is loading.

Dmitry Pavliv Over a year ago

@BK201, cmvalues_Enter is event - you can't add parametr to it:) But you can Call importexcelfile just before qstng = CurrentFileNameNoExtension in Op's code to initialize variable:)

WGS Over a year ago

Argggh! WTH. Long shift. This is indeed a USERFORM SUB! Editing.

vuyy1182 Over a year ago

i cannot call **importexcelfile ** method because it will open dialogue to select excel file., from where i get filename :)

Wayne G. Dunn Over a year ago

Since you have a Catch-22 with the sequence of calls / dialogue, can you add a listbox that contains all of the allowed TABLE names, then have the user select one entry BEFORE going into the 'Enter' event? Or ask the user BEFORE the 'Enter' event?

|

vuyy1182 · Accepted Answer · 2014-02-24 19:30:30Z

0

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

answered Feb 24, 2014 at 19:30

vuyy1182

1,69610 gold badges33 silver badges48 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related