I’ve used an “on save” macro for some time now to calculate invoices, which can now be automatically activated by the Save button/menu in Cardbox. There's no need any more for users to push a button (and forget to do so). Although many of you may think this is only a slight improvement, just think of all the possibilities.
First of all, you can make a macro avoid situations in which errors occur. For example: field “CODE” contains a code with spaces that must be entered as underscores. Standard validation can prescribe that input must be A-Z, 0-9 and “_”. A user who enters “ABC XYZ 123” will see an error message telling him that this input is not correct, and he’ll have to figure out by himself that it’s the spaces that are not allowed.
The following small macro will avoid this situation by going to the field CODE and simply and silently replace all spaces by underscores.
GotoField “CODE”
ReplaceAll “ “, “_”
SaveRecord
Remember that macros that you attach to the Save button/menu must contain the command SaveRecord or the record will never be saved.
Secondly, you can replace or enhance standard validation by checking for errors that are specific to your database. This has the great advantage of having custom-made error messages and in-depth error checking. Here’s an example of checking a very common situation: field “A” may contain text, but if it does, field “B” must also contain text. E.g. a field “DATE SENT” and a field “SENT BY”. There’s no way standard validation can check for this. This macro will do the trick. Of course you can make it also check for the reverse situation: field “B” has text, but “A” has not.
If Fields(“A”).Textlength > 0 AND Fields(“B”).Textlength = 0 then
Msgbox “Field B must contain text”
GotoField “B”
Halt
End if
SaveRecord
If field A and B are both empty or both contain text, the macro will return no error message, and the record will be saved. If not, the record will not be saved and the user will see the cursor in field B.
As long as you write the macros, there no limit to what you can do. Perhaps this small but significant improvement will entice you to fill the Macros section of this forum with hundreds of tips and tricks.
By the way, this feature does not influence validation. Validation will run after the macro has run.
Charles
