Run a macro and code from the same event?

[Tom K via AccessMonster.com]

I have a macro that runs on my forms On Current event. I now need to add a
private sub procedure. Can I do that? How do I run them both? I tried to
convert my macro to a module, which worked fine, but the only way I knew how
to run it was from a macro Run Code.
As you can see I am very lost.

Please help.

Tom

 

[Ken Snell [MVP]]

You can use the RunCode action in a macro to have the macro run a VBA
function procedure (must be a function, cannot be a subroutine) that is
Public in a regular module.

 

[Tom K via AccessMonster.com]

So, if I have a macro running a module already, then I can just use RunCode
again in the next macro line to run the next module? How do I make my code a
function? Right now it is a Private Sub that I need to run on the forms
Current() event.

Thanks,
Tom

You can use the RunCode action in a macro to have the macro run a VBA
function procedure (must be a function, cannot be a subroutine) that is
Public in a regular module.

I have a macro that runs on my forms On Current event. I now need to add a
private sub procedure. Can I do that? How do I run them both? I tried to

[quoted text clipped - 6 lines]

Tom

 

[Tom K via AccessMonster.com]

This is my code;

Private Sub Form_Current()
If Me.NewRecord Then
Me!lblNavigate.Caption = "New Record"
Else
With Me.RecordsetClone
.Bookmark = Me.Bookmark
Me!lblNavigate.Caption = "Record " & _
.AbsolutePosition + 1 _
& " of " & .RecordCount
End With
End If
End Sub

Thanks again...

Tom

So, if I have a macro running a module already, then I can just use RunCode
again in the next macro line to run the next module? How do I make my code a
function? Right now it is a Private Sub that I need to run on the forms
Current() event.

Thanks,
Tom

You can use the RunCode action in a macro to have the macro run a VBA
function procedure (must be a function, cannot be a subroutine) that is

[quoted text clipped - 5 lines]

 

[Ken Snell [MVP]]

I noted in my reply that you can run a Public function that is in a regular
module from a macro. You cannot run a Private procedure (function or sub)
from a macro, especially not one in a form's module.

I think perhaps we are not talking about the same "macro" concept. In
ACCESS, VBA code is not a macro. Macros are completely different entities.

Are you running VBA code? If yes, you can call a sub or function that is in
the same module (such as the form's module) by calling it:

Call Form_Current

--

Ken Snell
<MS ACCESS MVP>

This is my code;

Private Sub Form_Current()
If Me.NewRecord Then
Me!lblNavigate.Caption = "New Record"
Else
With Me.RecordsetClone
.Bookmark = Me.Bookmark
Me!lblNavigate.Caption = "Record " & _
.AbsolutePosition + 1 _
& " of " & .RecordCount
End With
End If
End Sub

Thanks again...

Tom

So, if I have a macro running a module already, then I can just use
RunCode
again in the next macro line to run the next module? How do I make my code
a
function? Right now it is a Private Sub that I need to run on the forms
Current() event.

Thanks,
Tom

You can use the RunCode action in a macro to have the macro run a VBA
function procedure (must be a function, cannot be a subroutine) that is

[quoted text clipped - 5 lines]

Tom

 

[Tom K via AccessMonster.com]

The only code Im running or want to run is the code that I noted. I try to
avoid code because I dont understand it well enough to use it correctly. In
this case I need to run code on the same event (On Current) as where I am
allready running a macro. I just need to figure out how to run them both.

Thanks for your help.

Tom

I noted in my reply that you can run a Public function that is in a regular
module from a macro. You cannot run a Private procedure (function or sub)
from a macro, especially not one in a form's module.

I think perhaps we are not talking about the same "macro" concept. In
ACCESS, VBA code is not a macro. Macros are completely different entities.

Are you running VBA code? If yes, you can call a sub or function that is in
the same module (such as the form's module) by calling it:

Call Form_Current

This is my code;

[quoted text clipped - 30 lines]

 

[Tom K via AccessMonster.com]

Is there a way to turn my code into a macro? Im much better with macros.

TK

The only code Im running or want to run is the code that I noted. I try to
avoid code because I dont understand it well enough to use it correctly. In
this case I need to run code on the same event (On Current) as where I am
allready running a macro. I just need to figure out how to run them both.

Thanks for your help.

Tom

I noted in my reply that you can run a Public function that is in a regular
module from a macro. You cannot run a Private procedure (function or sub)

[quoted text clipped - 13 lines]

 

[Ken Snell [MVP]]

Let's start at the beginning. What is your macro doing right now? On which
event is it being run? Tell us what you are wanting to do.... we then can
suggest a reasonable approach.
--

Ken Snell
<MS ACCESS MVP>

Is there a way to turn my code into a macro? Im much better with macros.

TK

The only code Im running or want to run is the code that I noted. I try to
avoid code because I dont understand it well enough to use it correctly.
In
this case I need to run code on the same event (On Current) as where I am
allready running a macro. I just need to figure out how to run them both.

Thanks for your help.

Tom

I noted in my reply that you can run a Public function that is in a
regular
module from a macro. You cannot run a Private procedure (function or sub)

[quoted text clipped - 13 lines]

Tom

 

[Tom K via AccessMonster.com]

Ok, This is what Im doing. I running a macro on the On Current event of my
form frmBusiness. If a condition is true it sets enabled to false on several
controls. If the condition is false it sets enabled to true on the same
controls. This is the macro;

Condition: ([Forms]![frmAddress]![frmBusinessSubform].[Form]![AddressID]) Is
Null

Set Value: [Forms]![frmAddress]![frmBusinessSubform].[Form]![AddressID].
[Enabled] Expression: Yes
Set Value: [Forms]![frmAddress]![frmBusinessSubform].[Form]![BusinessID].
[Enabled] Expression: No
Set Value: [Forms]![frmAddress]![frmBusinessSubform].[Form]![BusinessPhone].
[Enabled] Expression: No
Set Value: [Forms]![frmAddress]![frmBusinessSubform].[Form]![BusinessName].
[Enabled] Expression: No

Condition: ([Forms]![frmAddress]![frmBusinessSubform].[Form]![AddressID]) Is
Not Null

Set Value: [Forms]![frmAddress]![frmBusinessSubform].[Form]![AddressID].
[Enabled] Expression: Yes
Set Value: [Forms]![frmAddress]![frmBusinessSubform].[Form]![BusinessID].
[Enabled] Expression: Yes
Set Value: [Forms]![frmAddress]![frmBusinessSubform].[Form]![BusinessPhone].
[Enabled] Expression: Yes
Set Value: [Forms]![frmAddress]![frmBusinessSubform].[Form]![BusinessName].
[Enabled] Expression: Yes

The macro works just like I want it to. The problem is that now I want to add
a procedure to the On Current event. The procedure replaces the navigation
Record x of y. I have a label on my form that is called lblNavigation. I dont
know how to add a procedure to the event after I already have a macro on the
same event. This is the procedure;

Private Sub Form_Current()
If Me.NewRecord Then
Me!lblNavigate.Caption = "New Record"
Else
With Me.RecordsetClone
.Bookmark = Me.Bookmark
Me!lblNavigate.Caption = "Record " & _
.AbsolutePosition + 1 _
& " of " & .RecordCount
End With
End If
End Sub

Is there an easy way to add a procedure to an event when you are already
running a macro?

Thanks for the help.

Tom

Let's start at the beginning. What is your macro doing right now? On which
event is it being run? Tell us what you are wanting to do.... we then can
suggest a reasonable approach.

Is there a way to turn my code into a macro? Im much better with macros.

[quoted text clipped - 16 lines]

 

[Ken Snell [MVP]]

Let's just change the event procedure to also do what the macro currently is
doing, and get rid of the macro. In the form's design view, change the On
Current property to "[Event Procedure]". Then click the "three-dot" box at
far right. You'll see the Visual Basic Editor open, and then you'll see
three lines, with the cursor on the blank, second line:

Private Sub Form_Current()

End Sub


Now, paste the following lines in the blank line:

If IsNull(Me![AddressID]) = True Then
Me![AddressID].[Enabled] = True
Me![BusinessID].[Enabled] = False
Me![BusinessPhone].[Enabled] = False
Me![BusinessName].[Enabled] = False
Else
Me![AddressID].[Enabled] = True
Me![BusinessID].[Enabled] = True
Me![BusinessPhone].[Enabled] = True
Me![BusinessName].[Enabled] = True
End If
If Me.NewRecord Then
Me!lblNavigate.Caption = "New Record"
Else
With Me.RecordsetClone
.Bookmark = Me.Bookmark
Me!lblNavigate.Caption = "Record " & _
.AbsolutePosition + 1 _
& " of " & .RecordCount
End With
End If


--

Ken Snell
<MS ACCESS MVP>

Ok, This is what Im doing. I running a macro on the On Current event of my
form frmBusiness. If a condition is true it sets enabled to false on
several
controls. If the condition is false it sets enabled to true on the same
controls. This is the macro;

Condition: ([Forms]![frmAddress]![frmBusinessSubform].[Form]![AddressID])
Is
Null

Set Value: [Forms]![frmAddress]![frmBusinessSubform].[Form]![AddressID].
[Enabled] Expression: Yes
Set Value: [Forms]![frmAddress]![frmBusinessSubform].[Form]![BusinessID].
[Enabled] Expression: No
Set Value:
[Forms]![frmAddress]![frmBusinessSubform].[Form]![BusinessPhone].
[Enabled] Expression: No
Set Value:
[Forms]![frmAddress]![frmBusinessSubform].[Form]![BusinessName].
[Enabled] Expression: No

Condition: ([Forms]![frmAddress]![frmBusinessSubform].[Form]![AddressID])
Is
Not Null

Set Value: [Forms]![frmAddress]![frmBusinessSubform].[Form]![AddressID].
[Enabled] Expression: Yes
Set Value: [Forms]![frmAddress]![frmBusinessSubform].[Form]![BusinessID].
[Enabled] Expression: Yes
Set Value:
[Forms]![frmAddress]![frmBusinessSubform].[Form]![BusinessPhone].
[Enabled] Expression: Yes
Set Value:
[Forms]![frmAddress]![frmBusinessSubform].[Form]![BusinessName].
[Enabled] Expression: Yes

The macro works just like I want it to. The problem is that now I want to
add
a procedure to the On Current event. The procedure replaces the navigation
Record x of y. I have a label on my form that is called lblNavigation. I
dont
know how to add a procedure to the event after I already have a macro on
the
same event. This is the procedure;

Private Sub Form_Current()
If Me.NewRecord Then
Me!lblNavigate.Caption = "New Record"
Else
With Me.RecordsetClone
.Bookmark = Me.Bookmark
Me!lblNavigate.Caption = "Record " & _
.AbsolutePosition + 1 _
& " of " & .RecordCount
End With
End If
End Sub

Is there an easy way to add a procedure to an event when you are already
running a macro?

Thanks for the help.

Tom

Let's start at the beginning. What is your macro doing right now? On which
event is it being run? Tell us what you are wanting to do.... we then can
suggest a reasonable approach.

Is there a way to turn my code into a macro? Im much better with macros.

[quoted text clipped - 16 lines]

Tom

 

[Tom K via AccessMonster.com]

Thanks,

That worked great! I guess I need to learn VBA. I had no idea when I started
to learn Access that I would need to learn to program. It seems that it would
work like the other programs bundled with MS Office, in that you don't need
to know how to program. The good thing is that I've found a whole new
interest, creating databases. I'm even looking forward to learning VBA.

Do you have any suggestions to learning VBA. Should I start with somthing
else first? Is it too difficult to begin with? Is it silly to learn to
program just to make Access databases?

Thanks again for all the help!!

Tom

Let's just change the event procedure to also do what the macro currently is
doing, and get rid of the macro. In the form's design view, change the On
Current property to "[Event Procedure]". Then click the "three-dot" box at
far right. You'll see the Visual Basic Editor open, and then you'll see
three lines, with the cursor on the blank, second line:

Private Sub Form_Current()

End Sub

Now, paste the following lines in the blank line:

If IsNull(Me![AddressID]) = True Then
Me![AddressID].[Enabled] = True
Me![BusinessID].[Enabled] = False
Me![BusinessPhone].[Enabled] = False
Me![BusinessName].[Enabled] = False
Else
Me![AddressID].[Enabled] = True
Me![BusinessID].[Enabled] = True
Me![BusinessPhone].[Enabled] = True
Me![BusinessName].[Enabled] = True
End If
If Me.NewRecord Then
Me!lblNavigate.Caption = "New Record"
Else
With Me.RecordsetClone
.Bookmark = Me.Bookmark
Me!lblNavigate.Caption = "Record " & _
.AbsolutePosition + 1 _
& " of " & .RecordCount
End With
End If

Ok, This is what Im doing. I running a macro on the On Current event of my
form frmBusiness. If a condition is true it sets enabled to false on

[quoted text clipped - 69 lines]

 

[Ken Snell [MVP]]

ACCESS can be used without macros or programming, but it would be like
buying a new rocket spaceship and just pushing it where you want it to go.
In other words, macros and programming allow you to use the powerful
features built into ACCESS.

Macros can do a lot... and one can become quite proficient in them in order
to do all kinds of things. But, as you want to build more complicated (read
this as "more user friendly") databases, you'll quickly find that macros hit
a brick wall at some point. So, learning to use VBA is a pretty important
part of learning/using ACCESS if you want to continue doing more with the
program.

The nice thing about ACCESS VBA -- it's very similar to EXCEL VBA, WORD VBA,
POWERPOINT VBA, etc. In other words, learn one, and you have the foundation
for unlocking the potential in the other Office Suite programs too.

Learning VBA for me was an oscillating two-step process. First, I bought
some books about ACCESS and VBA. There are many good books out there... see
www.viescas.com for many of John Viescas' books. Also see
www.mvps.org/access for more lists, and
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Books
for more lists. Second, I then did "hands on" work -- in other words, I knew
that I wanted to do something, so I got to work writing small VBA code
snippets to do it... and then expanded on it... and then read more in the
book... and then did more in VBA... and then read more... and so on ...

Learning how to unlock the potential in ACCESS is a steep learning curve, so
don't be frustrated if it seems to go slowly at first. Practice, read the
newsgroups, read the books, trial and error... suddenly, you'll have
forgotten how you didn't know VBA before.