Returning a value from a form to a function???

[PaulSpell]

Can anyone help with this problem? I have a form with a listbox populated with a number of spreadsheet names and want to pass the value selected from the listbox back to a function.

But the problem is that the calling function does not wait for an item to be selected from the list and the form to close, it keeps running.

How do I call a form from a function and then suspend the function from continuing until a selection is made and the form closed?

 

[Bat17]

You need to open the form as popup/modal and then after selection hide the form not close it.
after your function has its value it can then close the form for you.

HTH

Peter

 

[PaulSpell]

Yeah but how do I get the function to wait for the value to be returned?

At present the code opens the form, then sets a variable to the value of a form control and then goes off and does something else. The problem I have is getting the function to wait for a value to be selected on the form before it goes off and does something else. At the moment it sets a null value and continues processing before any selection has been made on the form and the form has been closed.

Is there a way of using class modules to answer to this perhaps?

 

[Bat17]

opening the form as popup/modal will suspend the code until the form is closed or hidden.

Peter

 

[PaulSpell]

The code is in another module not in the form module. Opening the form as a popup and/or modal doesn't make any difference, the code still continues to execute without waiting for a response.

 

[Bat17]

The code should not run while the pop up has the focus. but you will need to hide the form to get your value from it. can you post the code that you are using to do this

peter

 

[PaulSpell]

This code is in the Class_Initialize event of a class module that I have written to capture details about files that I want to link to my database:

DoCmd.OpenForm "frmGetWrkSheets"
Form_frmGetWrkSheets.lstSheets.RowSource = sheetsLst(strFilePath)
Form_frmGetWrkSheets.SetFocus

The above opens the form and populates a listbox with a list of worksheets available in a specific Excel workbook.

I then have this function to test the class module and form:

--------------------------------------------------------------------------
Dim lnkFileObj As clsGetLinkedFilePaths
Set lnkFileObj = New clsGetLinkedFilePaths

MsgBox "File type: " & lnkFileObj.fileType & Chr(13) & "Filename: " & lnkFileObj.fName & Chr(13) & "Filepath: " & lnkFileObj.strFilePath

Set lnkFileObj = Nothing
--------------------------------------------------------------------------

The class module works fine to a point, it opens the file open dialogue, records details of the selected file, stores the path, file name and file type. Then if the type is Excel it opens the form and populates the listbox with a list of spreadsheets. This is where I have a problem.

I want to be able to select a spreadsheet name from the listbox on the form and pass it back to the calling function. But as I mentioned the calling function doesn't wait for a response, it just keeps processing. The instance of the class object terminates before the form returns a value. Making the form popup, non-popup visible, non-visible or anything else does not resolve this.

Any ideas anyone?

 

[pono1]

Paul,

In your class...

'This is pseudo code...
Public Function MyFunctionName(sFrmName as string) as boolean

'Halt code execution until the form is closed 
'or its visible property is set to No...
   DoCmd.OpenForm sFrmName,,,,acDialog
'Note: Stopping code execution midstream in a class is generally 
'considered a no-no -- here at least, since you're passing in
'a form name, you might be able to reuse the class elsewhere.

'Code to execute after the form closes...
' ipso...
' facto...

Call this from outside the class as follows...

   Dim MyInstance as New MyClassName
   MyInstance.MyFunctionName "MyFormName"

Regards,
Tim

 

[PaulSpell]

Tim,

Thanks for the reply. Can you explain how this works though?

If I call this function through a new instance of the class won't it just open the form, return True and continue processing? How does it suspend execution of the remaining code?

Is there a better way to do this?

 

[pono1]

Paul,

I would have to see and know a lot more about your project -- which I'm sure is fairly complex -- to know if there's another way of doing this... So, speaking generally, opening a form using the acDialog argument will pause code in the calling module until the form opened is either closed or made invisible...

Assume this simple scenario: You have a standard or form module in which you will be writing code to open a form. The code inside the "master" module, after opening the form, will wait for the form to become -- in this case -- invisible before it carries on executing its code. Code below and simple example attached...

Dim MyLstBoxChoice

'Open form and wait...
   DoCmd.OpenForm "FormName",,,,acDialog

'Waiting...

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Code to execute after the form just opened is made *invisible*
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Fetch the value on the form
   MyLstBoxChoice = Forms!FormName.ListBoxName.Value

'Close the form because we've saved the value 
'from the listbox into a local variable.
  DoCmd.close acform, sFrmName

'Do something with MyListBoxChoice variable...
   msgbox cstr(MyLstBoxChoice)
' ipso...
' facto...

Regards,
Tim

 

[PaulSpell]

Excellent,

Thanks Tim, this is the answer I was looking for.