How To: Migrate VBA To Modules

[LaCour]

I am more experienced at Visual Basic (4 yrs) than I am in Access. That being said, I have been able to take advantage of the power of VBA in my recent Access experience.

All of my VBA resides in events in the forms I have created. I would like to migrate this code to modules. This is better form - correct? Please recomend an approach that I should take (always do X - never do Y). I know there might be some grey areas, but I want to get some direction here. Proper declarations and syntax will be helpful. Basic tips will help.

Thank You
Blair

 

[lupins46]

You cannot move event procedures out of form modules - well you can but they won't work. As for other subs and functions, it depends on how much they are associated with a specific form. If the only place a procedure will ever be used is that one form then in my view it makes sense to put it into the form module so that if you reuse the form you get the associated code. In any case if you find you need to use the proc more generally you can move it into a standard module later. But a lot of subs and functions have more general use and so must be in standard modules. But this would be true in VB.
I'm a little surprised that you expect writing code in Access is going to be different to writing code in VB.

 

[LaCour]

I understand that the event and event procedure can not be seperated from the form which is where the event is detected. What I am looking to do is to have a very thin form where every applicable event calls procedures or functions in modules / classes rather than have the logic right in the form event.

I'm about a week into using Access ... the way I am getting into my VBA is by going into properties of a given event, choosing the code builder, where I then put my logic. I am looking to put my logic in outside modules / classes which the form events will call ... I am unsure how to make this connection and am looking for some advice. I know that this is closely related (if not exactly) to what I have been doing in VB. However,to be honest, I didn't start from scracth when I began my VB work (like most people I would imagine) ... I did some elaborite things, but I always started with the project that we had been using with existing classes and modules in place. I understand, apreciate and want to take advantage of good desing ... but my experience in VB was not focussed on starting from scratch, but rather enhancing the enterprise app that we had.

Please throw in your 2 cents

Thanks

 

[RoyVidar]

Simple sample - if you have the following code in, for instance the on current event of the form:

[tt]private sub form_current()
if isnull(me("txtSomeControl").value) then
me("txtSomeOtherControl").value = now()
msgbox "some message"
me.requery
end if
end sub[/tt]

Could then become something like this

[tt]private sub form_current()
call myform_current(me)
end sub

public sub myform_current(frm as form)
if isnull(frm("txtSomeControl").value) then
frm("txtSomeOtherControl").value = now()
msgbox "some message"
frm.requery
end if
end sub[/tt]

- here passing the form as parameter to the public sub residing in a statndard module. As long as the relevant objects/variables are passed, you should be able to whatever you need to do within the form.

- here also demonstrating one of the strength of using a naming/referencing convention on form controls including the Me keyword -> simply replace all (relevant) occurences of Me with frm (the replace all option, can of courese be dangerous, but, even one by one speeds up the process...).

I don't use VB at all, so I don't know this, but I thought the "only" difference in this regard, is that the Access form object (and related methods, properties and events) differs from the VB forms. Usage of standard modules, classes... should be more or less the same (though VB proabaly has some more power?), or am I wrong?

Roy-Vidar

 

[LittleSmudge]

What I am looking to do is to have a very thin form where every applicable event calls procedures or functions in modules / classes rather than have the logic right in the form event

Why ?

It won't make your forms load any faster and it won't make the code run any quicker ( potentially quite the opposite )

Apart from the academic exercise - Why bother?



General rules that I tend to apply are:-
If the code COULD potentially be used in multiple forms then put it in a module
HOWEVER
If the effort required to 'standardise' the code outweighs the advantage then it remains in the form/report code page.

Therefore, if the code refers to lots of control names then it's going to stay in the form.


'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[CajunCenturion]

VB uses the same underlying VBA engine that all the Office application do. The differences, just as RoyVidar has said, is in the user interface objects, like the Form. The Form object, and its basic controls, for Access are defined in Access, and the Form object, and its basic controls, for VB are defined in VB.

But the basic code, such as the Mid function, For Next loops, if-then-else construts, and so forth, are defined in VBA and therefore are the same for both applications.

As far as Standard Code Modules and Classes, they are the same for both.

The criteria for deciding what should be in a Class, a Standard Code Module, and what should remain in the Form object should also be the same.

I would not create a class just for the sake of creating a class. If I have need for an object, and especially the need for multiple instances of that object, then I'm going to define a class. If I have a Function that is going to be used in a validation routine from six different forms, then that function will be a Public Function in a standard code module.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[MacDaddyinAustin]

When passing control references to functions in Standard Modules, must one use the full reference name of the control object? For example, If I have a function to test for nulls or zero strings in a text box and return a message to the user stating that the control must contain data for the form it resides on to process, what is the best way to pass the name of the control to the function?

 

[CajunCenturion]

I wouldn't pass the name of the control. Passing the name of the control requires that fully reference the Form object with the parameterized name to reference the control.

Why not simply pass the Control by Reference? From the form
ValidInput = VerifyInput (txtThisTextBox)
and in the standard code module

Public Function VerifyInput (rCtl_TextBox as Control) as Boolean
VerifyInput = true/false
End Function



Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein