Run a macro from an event procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an event procedure that saves current record and then gets next record
and then sets the focus to a selected field. I inserted code in the event
Procedure to run a macro before these actions.
I need to do some data verifcation and manipulation( check values of fields
and if true open a form, select new value, close the form and save the new
value) and have a macro for that.

the problem is that when I run the macro control does not return to the
EPwhen the macro completes. Is there a way to force the return. I am not
knowledgeable enough to write the verification code in an EP.

Thanks Steve
 
Post the VBA code that you use for calling the macro and for saving the
record, etc.

Also post the actions of that macro.

I assume that the "On <event name>" property shows "<Event Procedure>" in
the box for that property in Properties window?
 
The Event
Procedure---------------------------------------------------------------------
Private Sub New_Expense_Button_Click()

On Error GoTo Err_New_Expense_Button_Click


DoCmd.RunMacro "Requery.Set PT ID" ---NOTE: After the macro finishes
control does not return to here.--
DoCmd.GoToRecord , , A_NEWREC
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
DoCmd.RunMacro "GoToDatePaid"

Exit_New_Expense_Button_Click:
Exit Sub

Err_New_Expense_Button_Click:
MsgBox Error$
Resume Exit_New_Expense_Button_Click

End Sub


The
macro--------------------------------------------------------------------------------
C:\Documents and Settings\MDTP\My Documents\Access\Rentals.mdb
Monday, September 05, 2005

Macro: Requery
Page: 2

Set PT ID
Not [tax unit StopMacro
id].[column](2)




If NOT pass thru property STOP

[Forms]![Record SetValue Item:
[Forms]![Record Expenses]![Pass
Expenses]![Item
Thru ID]
Description].[column]
(1)>0



Expression:
[Forms]![Record Expenses]![Item

Description].[column](1)
If known PT Category save it

... StopMacro









OpenForm Form Name:
Set Pass Thru

View:
Form
Filter Name:

Where Condition:
Data Mode: -1

Window Mode:
Normal



C:\Documents and Settings\MDTP\My Documents\Access\Rentals.mdb
Monday, September 05, 200
 
Can you repost the macro actions in a format that would allow us to clearly
see the different parts of the actions? What you posted appears to be
various columns all jumbled together.

Also, if you want to use VBA instead of the macro for its actions, tell us
in words what it's doing so that we can suggest the VBA code to do those
actions.
--

Ken Snell
<MS ACCESS MVP>


Steve S said:
The Event
Procedure---------------------------------------------------------------------
Private Sub New_Expense_Button_Click()

On Error GoTo Err_New_Expense_Button_Click


DoCmd.RunMacro "Requery.Set PT ID" ---NOTE: After the macro finishes
control does not return to here.--
DoCmd.GoToRecord , , A_NEWREC
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
DoCmd.RunMacro "GoToDatePaid"

Exit_New_Expense_Button_Click:
Exit Sub

Err_New_Expense_Button_Click:
MsgBox Error$
Resume Exit_New_Expense_Button_Click

End Sub


The
macro--------------------------------------------------------------------------------
C:\Documents and Settings\MDTP\My Documents\Access\Rentals.mdb
Monday, September 05, 2005

Macro: Requery
Page: 2

Set PT ID
Not [tax unit StopMacro
id].[column](2)




If NOT pass thru property STOP

[Forms]![Record SetValue Item:
[Forms]![Record Expenses]![Pass
Expenses]![Item
Thru ID]
Description].[column]
(1)>0



Expression:
[Forms]![Record Expenses]![Item

Description].[column](1)
If known PT Category save it

... StopMacro









OpenForm Form Name:
Set Pass Thru

View:
Form
Filter Name:

Where Condition:
Data
-1

Window Mode:
Normal



C:\Documents and Settings\MDTP\My Documents\Access\Rentals.mdb
Monday, September 05, 2005
------------------------------------------------------------------------------------------------

Ken Snell said:
Post the VBA code that you use for calling the macro and for saving the
record, etc.

Also post the actions of that macro.

I assume that the "On <event name>" property shows "<Event Procedure>" in
the box for that property in Properties window?
Click to expand...
Click to expand...
 
I think we are getting a little off track. the macro works just fine if
envoked from something like "form on close". there is no problem with the
macro. The problem is that when I run the macro from an event procedure
control passes from the EP to the macro and it execites OK but control does
not return back to the next statement in the EP. Is there any way to force
control back to the EP?

If there is not then maybe I need to code statements (DoCmds) in the EP to
run macro commands instead of the macro itself.

Steve

Ken Snell said:
Can you repost the macro actions in a format that would allow us to clearly
see the different parts of the actions? What you posted appears to be
various columns all jumbled together.

Also, if you want to use VBA instead of the macro for its actions, tell us
in words what it's doing so that we can suggest the VBA code to do those
actions.
--

Ken Snell
<MS ACCESS MVP>


Steve S said:
The Event
Procedure---------------------------------------------------------------------
Private Sub New_Expense_Button_Click()

On Error GoTo Err_New_Expense_Button_Click


DoCmd.RunMacro "Requery.Set PT ID" ---NOTE: After the macro finishes
control does not return to here.--
DoCmd.GoToRecord , , A_NEWREC
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
DoCmd.RunMacro "GoToDatePaid"

Exit_New_Expense_Button_Click:
Exit Sub

Err_New_Expense_Button_Click:
MsgBox Error$
Resume Exit_New_Expense_Button_Click

End Sub


The
macro--------------------------------------------------------------------------------
C:\Documents and Settings\MDTP\My Documents\Access\Rentals.mdb
Monday, September 05, 2005

Macro: Requery
Page: 2

Set PT ID
Not [tax unit StopMacro
id].[column](2)




If NOT pass thru property STOP

[Forms]![Record SetValue Item:
[Forms]![Record Expenses]![Pass
Expenses]![Item
Thru ID]
Description].[column]
(1)>0



Expression:
[Forms]![Record Expenses]![Item

Description].[column](1)
If known PT Category save it

... StopMacro









OpenForm Form Name:
Set Pass Thru

View:
Form
Filter Name:

Where Condition:
Data
-1

Window Mode:
Normal



C:\Documents and Settings\MDTP\My Documents\Access\Rentals.mdb
Monday, September 05, 2005
------------------------------------------------------------------------------------------------

Ken Snell said:
Post the VBA code that you use for calling the macro and for saving the
record, etc.

Also post the actions of that macro.

I assume that the "On <event name>" property shows "<Event Procedure>" in
the box for that property in Properties window?

--

Ken Snell
<MS ACCESS MVP>

I have an event procedure that saves current record and then gets next
record
and then sets the focus to a selected field. I inserted code in the
event
Procedure to run a macro before these actions.
I need to do some data verifcation and manipulation( check values of
fields
and if true open a form, select new value, close the form and save the
new
value) and have a macro for that.

the problem is that when I run the macro control does not return to the
EPwhen the macro completes. Is there a way to force the return. I am
not
knowledgeable enough to write the verification code in an EP.

Thanks Steve
Click to expand...
Click to expand...
Click to expand...
 
Your original post said that the macro wasn't returning to the code. That is
unusual behavior. To understand what the macro is doing, I asked if you
could repost the macro's actions so that they would be understandable. I
offered to assist to rewrite the macro as VBA code as a possible fix.

--

Ken Snell
<MS ACCESS MVP>

Steve S said:
I think we are getting a little off track. the macro works just fine if
envoked from something like "form on close". there is no problem with
the
macro. The problem is that when I run the macro from an event procedure
control passes from the EP to the macro and it execites OK but control
does
not return back to the next statement in the EP. Is there any way to
force
control back to the EP?

If there is not then maybe I need to code statements (DoCmds) in the EP to
run macro commands instead of the macro itself.

Steve

Ken Snell said:
Can you repost the macro actions in a format that would allow us to
clearly
see the different parts of the actions? What you posted appears to be
various columns all jumbled together.

Also, if you want to use VBA instead of the macro for its actions, tell
us
in words what it's doing so that we can suggest the VBA code to do those
actions.
--

Ken Snell
<MS ACCESS MVP>


Steve S said:
The Event
Procedure---------------------------------------------------------------------
Private Sub New_Expense_Button_Click()

On Error GoTo Err_New_Expense_Button_Click


DoCmd.RunMacro "Requery.Set PT ID" ---NOTE: After the macro
finishes
control does not return to here.--
DoCmd.GoToRecord , , A_NEWREC
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
DoCmd.RunMacro "GoToDatePaid"

Exit_New_Expense_Button_Click:
Exit Sub

Err_New_Expense_Button_Click:
MsgBox Error$
Resume Exit_New_Expense_Button_Click

End Sub


The
macro--------------------------------------------------------------------------------
C:\Documents and Settings\MDTP\My Documents\Access\Rentals.mdb
Monday, September 05, 2005

Macro: Requery
Page: 2

Set PT ID
Not [tax unit StopMacro
id].[column](2)




If NOT pass thru property STOP

[Forms]![Record SetValue Item:
[Forms]![Record Expenses]![Pass
Expenses]![Item
Thru ID]
Description].[column]
(1)>0



Expression:
[Forms]![Record Expenses]![Item

Description].[column](1)
If known PT Category save it

... StopMacro









OpenForm Form Name:
Set Pass Thru

View:
Form
Filter Name:

Where Condition:
Data
-1

Window Mode:
Normal



C:\Documents and Settings\MDTP\My Documents\Access\Rentals.mdb
Monday, September 05, 2005
------------------------------------------------------------------------------------------------

:

Post the VBA code that you use for calling the macro and for saving
the
record, etc.

Also post the actions of that macro.

I assume that the "On <event name>" property shows "<Event Procedure>"
in
the box for that property in Properties window?

--

Ken Snell
<MS ACCESS MVP>

I have an event procedure that saves current record and then gets
next
record
and then sets the focus to a selected field. I inserted code in the
event
Procedure to run a macro before these actions.
I need to do some data verifcation and manipulation( check values of
fields
and if true open a form, select new value, close the form and save
the
new
value) and have a macro for that.

the problem is that when I run the macro control does not return to the
EPwhen the macro completes. Is there a way to force the return. I
am
not
knowledgeable enough to write the verification code in an EP.

Thanks Steve
Click to expand...
Click to expand...
Click to expand...
 
Back
Top