Unanswered: Not waiting for modal form: Find not available
OK, so the title has two apparently totally unrelated problems. However, I find myself where I can get rid of one or the other, but not both! The attached database (Access 2007) is a highly simplified illustration.
I have a main data entry form with many fields, represented by "frmMain" here.
There's one field which I want to select from a list - but I want to show the user a whole bunch of information about the selection options, so a simple Combo won't do. So, on the Main form you click "Fetch Value" and it brings up a secondary form, "frmOption".
Here you can see all the details (again, highly simplified in the attached DB). You click "Select" next to the one you want - the form closes and the selected value is passed back via a global variable.
The main form checks to see if the global variable has changed, and if it has then it updates itself.
This all nearly works ......... but I have two scenarios here.
Case 1: If in my main form I invoke the options form with the command
then the form opens, you can make the selection, and the main form updates itself as you'd expect.
Case 2: In this case, change the open command to:
OK, the form opens, but execution in the calling routine does NOT wait until the form closes, even though the form properties include Pop Up = Yes and Modal = Yes. This means that the main form never updates itself.
Why doesn't execution wait in case 2?
Soooooo ... you might say, well if that's your problem then why not stick to Case 1: the update always works?
Here is where the other problem comes in. On the Options Form I have a Find button - in the real form there are more options than fit on the screen, so I want the user to be able to search for key words in any of the fields to help them select.
In Case 2 the Find button always works - the Find dialog pops up and I can search.
BUT in Case 1 the Find button never works - you get "the command or action 'find' isn't available now."
Case 1 - The update works but Find doesn't
Case 2 - The updates doesn't work but Find does
Any clues much appreciated. I'd be happy with a solution that tackled this from either direction.....
The Dialog parameter is the culprit. In Case 1, it's disallowing the Find from operating, but forcing the VBA to wait 'til the Options form gets closed. That's why the updates work. In Case 2, since Dialog is False, the VBA continues to work after you open the Options form, so the updates are being done prematurely, or in other words, not working at all. On the other hand, you can do anything you want while the Options form is open, including Find.
Open the Options form without setting Dialog to True. At that point in the VBA, close the calling form and end the subroutine. Transfer any VBA after that (including the update code) to the called form.
I'll betcha that both the updates and the find will work.