Difference makes the DIFFERENCE
Categorised Menu List
(this method reduces MANUAL coding to a great extent... Just give it a try)
To understand the following concept/code, a comfortable understanding of Event driven Programming model is required, where, one gets introduced to Properties, Events and Methods: However, for a layman's understanding, Property is generally referred to as physical behaviour, for eg: background-color, fore-ground color, text-type, text-font-size, text-font-name etc..., Some properties are available at design time, some are available at run-time, and some others are available at both times, also, some properties may not be listed in the properties list (F4). Event is any user activity or system activity - User Activities examples: Mouse-Click (left button), Mouse-Right-Button-Click, Mouse-left-button-double-click, etc.. System Events like: idle event, WorkBook-Open, WorkBook-Close etc... Method is any built-in programme/procedure, for eg: SetFocus, OpenDatabase etc...
Let us first consider the terms Control and Variable
Control: is any area where user can perform a certain action (one at a time).
User Actions may be like: entering data into a given space, selecting one among the list of given entries etc.. No user can perform two actions at the same time.
Variable: simply called a a memory location: where system can store some value. This value can be changed at run-time (dynamic - through a program) or by passing a value exclusively.
Most of the event-driven-programming languages support storing controls as variable. Usage can be observed in the later part of this article.
The following picture displays text box, label/caption, Command button controls and labelled accordingly. There are other controls like list-box, combo-box, radio-button, check-box, and others...
We can consider this example to be of three parts,
(Please try this example, with minor changes to the code from (2).
In VBA, Procedure looks like the following examples: for the sake of conveniency, numbered each line....
Check if the current control is of required type with 'TypeOf' operator with FOR EACH loop.
If the current control is the required control, then replace its text to empty string, note, not NULL. Please note that deleting data from the control will not have any effect either on the data base field or variable associated with this control. Hence data in memory is distinct from data seen over screen.
Key to this procedure is FOR..EACH looping structure. This loop checks through all the controls on the user form, whenever, it find the type of control, as mentioned in the IF condition with TYPEOF operator, said action is carried out - in this case, the existing text is replaced with empty string.
The following code can be adopted, if the controls are named with prefix, as per the type of control, eg: txt-for text controls, cmd-for command buttons, cmb-for combo box controls, lst-for list controls, hence the control names will be like: txtCustomerName, cmbStateCode, lstZipCode etc...