Access Forms/VBA - General Design Question (Proliferating Code)
Posted on 2004-11-24
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.