Open new, same form and pause current form execution

[mcalex]

Hi all, I have a form that is used to vacate member and office positions (on resignation etc) for a number of different bodies. Rules are that when a member vacates their member position, they must vacate their office position, if they have one. However, a member can vacate his/her office position and still retain their member position.

The database has been designed such that admin staff on the boards upload appointment information , however, resignation (etc) information is done centrally here. When the staff are uploading information, they simply type in the name of the member or office holder that has been appointed. Due to this, there is no connection between members and office holders other than they _should_ have the same name (or at least the same surname - disregarding typos, of course).

Therefore when an office holder vacates the office, if they've resigned their member position as well, I want to pop up a new version of the same form so the user can decide that member Michael Smith is the same as Treasurer Mike Smith and vacate him accordingly. I'm using doCmd.OpenForm, but instead of displaying the form it seems the program is continuing on, and I run into a 3420 run-time error 'Object invalid or no longer set' further down the code (when it tries to navigate a recordset created by a query run by the original form).

So how do I modally open a form, and pause execution in the current form, while it is running? I was hoping something like java's new might exist (eg: DoCmd.OpenForm new "frmVacateMember") but that didn't work.

cheers and tia
mcalex

 

[HiTechCoach]

The way I handle this is to use a sub form to display the data from another record on the same form.


Also see:

How to Open Multiple Instances of a Form

 

[MarkK]

Set the WindowMode parameter to acDialog...

DoCmd.OpenForm "yourForm", , , , , acDialog

Execution continues on the next line of the calling code when the Dialog form is closed OR hiddden. Hiding it's handy since then your calling code can use the values directly from the form, and then close it.
To hide the modal dialog, handle the Unload event, cancel it and set the Visible property of the form...

private sub Form_Unload(Cancel as integer)
  Cancel = true
  Me.Visible = false
[COLOR="Green"]  'after execution here, control is passed back to the code that opened the form.[/COLOR]
end sub

Cheers,

 

[mcalex]

hi lagbolt, HiTechCoach

Both suggestions (OpenForm as acDialog, and the knowledgebase article) seem to result in the code continuing on. (I added: MsgBox "Form has opened. You are back in the calling method" immediately after the DoCmd.OpenForm / Set frmX = new Form line and it was invoked in both cases). Does it matter that the call is in a select case block which is in an if block?

Gonna give the subform method a try, but haven't yet done any subform coding at all, so i've got my fingers crossed.

Will update as i (hopefully) progress.

cheers

 

[mcalex]

Ok. Sorta managed to create a subform and get it to become visible when I want, but I still have the problem of getting current code to pause while I do actions in the sub/new form. Just like my attempts at the 'form as dialog' and the knowledgebase article suggestions, the code continues and then falls over at the 'Object invalid or no longer set' error that I mentioned in the OP.

The dialog approach feels most like what I want to do, and I'm beginning to think I'm doing something stupid, because everything I've read since lagbolt's post says it should work the way he says it does.

but it doesn't.

Is the fact that it is the same form the problem? ie if frmMyForm is open, and I say:
DoCmd.OpenForm "frmMyForm"
does Access realise that frmMyForm is already open and do nothing?

Now i'm wondering if it is easier to hold onto the values and then, once this position has been vacated, reopen the original form so the user can start again on the member position, but this is quite clunky (and will probably result in user grumbles :-(

Can anyone can suggest what could stop a form opened as acDialog to not behave modally?

Should I try another approach?

 

[Atomic Shrimp]

In the long run, this is a problem with the underlying data structure - if it's possible for the same person to have two positions, then the appointments data needs to be divorced from the people data and stored in a separate child table with a foreign key to the person ID - so that the people table contains people (one record per person) and the appointments table details what they do (one record per person, per job)

Appreciate this might not be something you have any control over, and might be difficult to implement in a released application with live data, but I felt it was worth saying anyway.

 

[mcalex]

lol, yeah, don't I know it. Fixes are planned for the database redesign, but i'm dealing with an 'interim' solution at the moment - the bulk of the appointments occur in July and the report is due at the end of the month.

The problem is now out of the db design area. I can't understand why a 'dialog' type form is not behaving modally - all the doco/help/posts say it should.