VBA basic question

[roneyc]

I am trying to modify an existing database for which the developer is
not available and I'm new to VBA. I have some basic questions to try to
understand the object-VBA relationships before I even begin to start
editing.

The situation: Database consists of 10 tables. A master form exists,
with 8 tabbed controls and 16 subforms. These are essentially 2 sets of
8, one for editing and viewing existing records and another complete
set to add a new record. When in design view, I click on the event
procedure in properties, the VBA view opens up pointing to a private
subroutine in the middle of several pages of code.

Questions:
1. Will there be separate pages of code for each subform, or do these
pages represent all the code for the database?

2. If this code is only attributable to the single subform I am trying
to modify, how do I determine the function of the other subroutines not
part of the Event Procedure?

3. If this code is for other subforms and controls throughout the
database, is there a way to point backwards to the objects linked to
different subroutines?

These questions arise because viewing the VBA code from several of the
different subforms shows the pages of code are not an exact match.
There is probably 75% overlap on the subroutines from form to form, but
each collection of code is slightly different in the subroutines it
contains. I'm wondering if the developer had some different versions
and there could be some outdated VBA that doesn't apply to any of the
current objects, reports, etc. If so, I'd like to simplifly by deleting
extraneous code, but without knowing what it might be linked to, I
don't want to proceed.

 

[Graham R Seach]

Answers in-line.

Questions:
1. Will there be separate pages of code for each subform, or do these
pages represent all the code for the database?

Yes, each form/subform has its own code "page", which is called a module.

2. If this code is only attributable to the single subform I am trying
to modify, how do I determine the function of the other subroutines not
part of the Event Procedure?

Each form/subform has its own event procedures. Therefore, you need to code
events in the individual form/subform.

3. If this code is for other subforms and controls throughout the
database, is there a way to point backwards to the objects linked to
different subroutines?

The first part of this question is moot, however, you can refer to one
subform from another, and indeed from the main form to any of its subforms:
From the main form to a control on a subform:
Me!subformname.Form!controlonsubform

From one subform to the main form:
Me.Parent.Form!controlonmainform

From one subform to another:
Me.Parent.Form!subformname.Form!controlonothersubform

These questions arise because viewing the VBA code from several of the
different subforms shows the pages of code are not an exact match.
There is probably 75% overlap on the subroutines from form to form, but
each collection of code is slightly different in the subroutines it
contains. I'm wondering if the developer had some different versions
and there could be some outdated VBA that doesn't apply to any of the
current objects, reports, etc. If so, I'd like to simplifly by deleting
extraneous code, but without knowing what it might be linked to, I
don't want to proceed.

Hard to say.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[roneyc]

Thanks for the information. This gives me a better handle on what I'm
dealing with.

However, I think I was unclear on Question 3 (although your answer will
be very useful when I start editing to make the relationships better
between the main form and the subforms).

What I'm looking for is assistance in understanding the existing VBA
code. Is there a tool in the VBA view that will allow me to see the
object or control that the code is linked to? Or can I only search
forward by clicking on the various event procedures, combo box
properties, etc. to and seeing what VBA code comes up?

 

[Graham R Seach]

Three places.

1. Look at the very top of the window:
Microsoft Visual Basic - myDatabase - [Form_Form1 (Code)]
...this tells you you are currently looking at the code for Form1 in the
myDatabase database.

2. Press Ctrl + R. This displays the Project Explorer. As you move from
object to object, it will show you which object (form, report, module)
you're currently working in. Double-click any object in the Project Explorer
to view the module for that object.

3. Just above the words "Option Compare Database" is a drop-down box. This
shows you the name of the object (textbox, listbox, etc) that owns the
procedure the cursor is currently positioned within. The drop-down to its
right shows you the name of the procedure the cursor is currently in.

Additionally, when you're in any procedure, you can click on any variables
and procedure names and select Definition from the context menu, and Access
will take you to the place where that variable or procedure is defined.
Right-click and select Last Position to jump back to where you were.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[roneyc]

Thanks so much! I now have a handle on how to proceed with my analysis.
I'll probably post again when I get knee-deep in the editing. Cynthia