2013-11-20, 20:43 #1
- Join Date
- Aug 2002
- Adelaide, South Australia, Australia
- Thanked 5 Times in 5 Posts
Passing a variable from an Access form back to the calling subroutine
I am always reading that Global Variables, like GoTo, are programming pariahs requiring the digital equivalent of a string of garlic and a wooden stake to keep at bay under all circumstances.
However, I am probably cementing my amateur status by declaring that I haven't found a better option for passing variables collected in an Access form back to the calling routine.
I'm quite capable of passing data into the form via OpenArgs and then parsing it out if there are several variables involved (although, for the life of me, I can't figure out why a few well-managed global variables aren't a more efficient alternative to all that fiddling around).
What I'm stuck on is the method of getting data collected in the form's fields back to the original subroutine after clicking OK.
Am I missing the bleeding obvious here?
Feeling stupid and will doubtless feel even more so when someone gives me a "Duh..." response, but I can take it in the name of Good Programming!
Thanks and regards
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!
2014-03-06, 08:27 #2
- Join Date
- Mar 2014
- Thanked 0 Times in 0 Posts
Hide your form instead of closing it
You can hide the data-collection form instead of closing it, so all form fields are available to your application. If the form is opened in Dialog mode, then code execution in the calling routine will pause until the data form is closed or hidden.
In the data-collection form, when the user clicks 'OK' you should hide the form (Me.Visible = False); otherwise, close the form as usual.
Here's sample code I use in the calling routine:
strOpenArgs = "Dept=" & strDept & ";Target=" & strTarget 'close the form if it is already open If CurrentProject.AllForms("frmFileProperties").IsLoaded Then DoCmd.Close acForm, "frmFileProperties", acSaveNo End If 'open the form with the appropriate OpenArgs value DoCmd.OpenForm "frmFileProperties", acNormal, , , , acDialog, strOpenArgs 'if the form is still open then the user clicked 'OK' If CurrentProject.AllForms("frmFileProperties").IsLoaded Then With Forms("frmFileProperties") strTarget = .cboTarget.Value strDocType = .cboDocType.Value strDept = .txtDept End With End If