• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

Access Forms/VBA - General Design Question (Proliferating Code)


Hello Experts,

Have a general question about designing Access forms and using VBA, and was hoping someone might be able to provide some guidance.

One of the main things I've noticed in the short time I've been building Access forms and coding VBA is that, on complex forms (those containing many fields, buttons, controls, etc.), my code tends to proliferate. I suppose I should provide a little background here to better explain what I mean.

I have a form, and on this form I have, oh, approximately 45 text fields and 10 command buttons. The command buttons are used for such things as printing, searching, saving, etc. While all of the command buttons perform a distinct function, they also have some things in common. For instance, when a user moves a mouse over one of the buttons, I want to change the mouse cursor from an arrow to a hand, which is simple enough. The problem I have is that I need to have an individual mousemove event for each button, resulting in 10 different sub statements. Now, I did figure out that I only need to write the function that does the mouse cursor change in one place (in a module), but it seems I still need to call the function 10 separate times in a mousemove event in each of the buttons. This doesn't seem ideal to me, for many reasons. And, the form I have described is really fairly simple - the problem with proliferating code only gets worse when designing more complex forms that contain numerous control entities that have at least some common functional elements (such as the cursor change described earlier).

My question is this: how does one simplify all of this? Perhaps grouping and then assigning code at the group level? I had thought this would do the trick, but can't seem to find any way to do it.

This is a question of design methodology more than anything else. I've learned quite a bit about the code and now find myself searching for a better way to design and implement my code. I understand this is a fairly open ended question, but I would appreciate either direct guidance or a reference to a good book on the subject. All of the books I've bought so far have been mostly about the code and include very little about good Access/VBA design and practice.

I've given this question a value of 300 points, though I am willing and able to increase the point value depending on the quality of the feedback. Hopefully, I've made my question clear enough, but if I can provide any additional input to help you formulate a better answer, please do not hesitate to let me know.
0
Painfree888
Asked:
Painfree888
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
Arthur_WoodCommented:
actually, in VBA you can't simplify this, because VBA does NOT support Control Arrays, which would the the method used in Full VB 6 to accomplish this.  Control Arrays, or the lack thereof, is one of the major differences between VB 6 and VBA.

AW
0
 
dannywarehamCommented:
What i do to reduce controls on my screen is to have a series of option groups for my reports, and one "Generate" button.

I then use a CASE statement to check what type of report wants to be produced based on the option group selections.
In my command button, depending on which CASE statement is triggered, I change the report SQL.

As for a common functions, these can be done by creating a public function that can be called on an event, as you have done. That is how I do it (although I may have bad habts myself!)


0
 
stevbeCommented:
another approach to common functionality like the MouseMove procedure is that as long as it is a public function you can put the name of the function directly in the property field for the event in the properties dialog box. I once did a mouse move that bolded stuff and the On Mouse Move property said ...

=MyMouseMove([Form],[Form]![txtSOS])

[Form] is the equivalent of using Me in VBA. I used my function across multiple forms so I passed the form and the control reference

Public Function MyMouseMove(MyForm As Access.Form, MyControl As Access.Control)

.... put your code here

End Function

Steve
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Painfree888Author Commented:

Arthur, Danny and Steve,

Thank you for the advice. Steve, I am going to play around with implementing your suggestion today and see how it goes.

One thing that occured to me over the weekend was this: creating events on controls programmatically, when the form is loaded. For instance, I have a form, named FormA. On FormA, I have 20 fields. Of the 20 fields, I have 10 that I would like to assign a mousemove event - when the user moves the mouse cursor over any of those 10 fields, I want the cursor to turn into a hand, signifying that some action may be performed on those fields. I don't believe I need to go to each control individually and place a mousemove event. Instead, I would name the fields something like MM_FieldNameHere. Then, in the Load event of the form, I would loop through the controls on the page and, for all fields with MM in the name, do something like this (syntax isn't exactly right, but you'll get the idea):

Loop through controls
     If InStr(1, ControlName, "MM"") > 0 Then
          Me.MM_Field1.OnMouseMove = "=MouseMove(""" & Control.Name & """)"
     End If
End Loop

The MouseMove function would be in a module, and look something like this:

Public Function MouseMove(fName As String) 'Not sure I'd need to pass field name for the mousemove
    'Change cursor arrow to hand
 End Function

I could do this for other functionality as well. For instance, if I want to assign a common click event to these same controls, I could add the 'BC' to the field name and check for that as well and set the event code in the Load event for the form.

What do you guys think? All of my code would be centralized, and I would only need to change the field names to add or remove events from a control. Not sure what the overhead price would be, in terms of how long it would take the form to load, especially if I have many controls on the page, but for smaller forms, might be worth trying.
0
 
Arthur_WoodCommented:
you cannot add events on the fly, in code.  In fact, when you create a control, in code, on the fly, that new control CANNOT raise events, either.  The new control can only be used to display values.

Adding event handlers, on the fly, requires VB.NET at a minimum.

AW
0
 
Painfree888Author Commented:

>In fact, when you create a control, in code, on the fly, that new control CANNOT raise events, either.  The  >new control can only be used to display values.

I didn't mean to suggest adding events to controls created on the fly. I will have already created the form controls, and then assign events to the controls in the Form_Load script programatically, based on field name. Though, I might try it just for kicks.

>you cannot add events on the fly, in code.

Actually, I've already done it. Looks like this (this is a first attempt at it, haven't added code to loop through all controls):

Have a form, named FormA. On the form, I have one control (a textbox) named MM_Field1. In the Form_Load handler for FormA, have the following code:

Private Sub Form_Load()
    Me.MM_Field1.OnMouseMove = "=MouseMove(""" & Me.MM_Field1.Name & """)"
End Sub

MouseMove function is in a module, and looks like this:

Public Function MouseMove(fName As String)
    MsgBox fName
End Function

When the mouse is moved over the control, I get a message box that displays the name of the field. Seems to work just fine. Now, what I would need to do next is to code in the part where I loop through the fields, and based on field names, associate with various events (mousemoves, onclicks, onfocus, etc).



0
 
stevbeCommented:
when you reference a control directly in a property you should use this format ...

=MouseMove([Form]![txtSOS])

leave [Form] exactly as it is (equivalent of VBA's Me) and change txtSOS to the name of your control. I would suggest that you also use the exclamation ! and the brackets [] .

Private Sub Form_Load()
    Me.MM_Field1.OnMouseMove = "=MouseMove("[Form]![" & Me.MM_Field1.Name & "])"
End Sub

Steve
0
 
Painfree888Author Commented:

I am sure this is an excellent suggestion, SteveB, but can you explain the logic of including the Form and []s? Is this just generally good coding practice in VBA?

Still waiting to hear from others (though doubt any other ideas will be forthcoming given the lack of activity in this thread), and then I'll allocated points.

Gracias!
0
 
stevbeCommented:
Using [Form] makes the call explicit, otherwise Access needs to look through the call stack to determine which object you are referring to. Using the brackets avoids any issues if you have spaces of funny characters in your control names and if you use the expression builder that is exactly how Access would do it.

Steve
0
 
Painfree888Author Commented:

Thank you, gentlemen, for your input. Much appreciated.l
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now