I want my code to finish running 1

[melt333]

Hi,

I have an event called by a control on a form, which calculates a few things, then runs a macro. This macro opens another form and closes the first form.

The problem I am experiencing is that the original event procedure doesn't finish (because the onus is on the user choosing another event from the newly opened form) and therefore a query which the second form calls is left in the background, until the second form is also closed (which I don't want to happen as this is a menu which does many tasks)

How do I force the first event to finish?

Please help!

Cheers,

Mark

 

[GKProgrammer]

Hi Mark,

Well unfortunately the calling procedure will never be out of scope until the procedure that it calls is exited and then the form will close. Regards,
gkprogrammer

 

[JeremyNYC]

Mark,

If you convert your macro to code and paste the code it will be easier for us to see what's going on. (Tools|Macros|Convert to VB).

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[melt333]

Hi Jeremy,

Here is my code.

First the click event for a button on the first form:

Private Sub cmdCheckSecurity_Click()
Set rstAgents = CurrentDb.openrecordset("tblAgentDetails&quot
rstAgents.MoveFirst
Do While Not rstAgents.EOF 'search down table for match
If rstAgents(1) = cboAgent.Value Then
GoTo found
End If
rstAgents.MoveNext
Loop
Exit Sub
found:
If rstAgents(2) = txtPassword Then
If rstAgents(3) = "SNA" Or rstAgents(3) = "Coord" Then
rstAgents.Close
DoCmd.RunMacro "mcrOpenAdminMenu" 'open admin menu
Else
rstAgents.Close
DoCmd.RunMacro "mcrOpenAgentMenu" 'open agent menu
End If
End If
End Sub

Now the 2 macros:

Function mcrOpenAdminMenu()
On Error GoTo mcrOpenAdminMenu_Err

DoCmd.OpenForm "frmAdministratorMenu", acNormal, "", "", acEdit, acDialog


mcrOpenAdminMenu_Exit:
Exit Function

mcrOpenAdminMenu_Err:
MsgBox Error$
Resume mcrOpenAdminMenu_Exit

End Function

Function mcrOpenAgentMenu()
On Error GoTo mcrOpenAgentMenu_Err

DoCmd.OpenForm "frmAgentMenu", acNormal, "", "", acEdit, acDialog


mcrOpenAgentMenu_Exit:
Exit Function

mcrOpenAgentMenu_Err:
MsgBox Error$
Resume mcrOpenAgentMenu_Exit

End Function

and now the load & on open events for both forms:

Private Sub Form_Load()
acxCalendar.Visible = False
txtDay28 = Day_1 + 27
End Sub

Private Sub Form_Open(Cancel As Integer)
txtName = Forms!frmSecurityMenu.cboAgent
DoCmd.RunMacro "mcrCloseSecurityMenu"
End Sub

So now I am left with a form (either 'Agent Menu' or 'Admin Menu') open which has a button to run a crosstab query. When this button is pressed the crosstab runs, but displays behind the form. I'm beginning to think that the only solution is to minimize the form, create another small form with a button to maximise the form again once the user has finished with the crosstab.

What do you think?

Your help is much appreciated,

Mark

 

[melt333]

I have found a kind of solution. I have changed the border style of the second form to 'None' and now it stays in the background - Result!!!

Cheers,

Mark

 

[JeremyNYC]

I would open a form that holds the results of the query and then make the form with the button invisible. In the Close event of the form that holds the results of the query put in a line of code to make the other form visible again.

If I may, I'd like to point out a few ways you could make your code stronger:
1) Ditch the macros. See that one line of code in the macros that opens the form? That's all you need to put in place of calling the macro, and then all the code is in one place and it's easier to see what's going on.

2) Declare all your variables, and require yourself to do so (in the options dialog). Otherwise, misspelling a variable name will mean that you have two different variables. This one is sort of a no-brainer. Declare all your variables at the top of each routine and you'll be able to understand what's going on in your code much more easily.

3) Whenever possible, don't use GoTo statements. In the first procedure you posted, there's no need for the goto statement. Simply move the code under that label to where the goto line is and everything will work fine. GoTo statements are relics from old-school programming days. In an event driven programming environment with distinct procedures, there is almost nevder a need for a GoTo statement.

Hope this helps.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[melt333]

Thanks Jeremy,

I'll give it a go.

And thanks for the advice on the code, I know my style isn't very good - unfortunately, my work needs me to run before I can walk, so I have to skip the fundamentals, but I am trying to find the time to learn from scratch at home. There are never enough hours in the day!!

Your help is much appreciated.

Cheers,

Mark