June9
I use MS Access (Windows version 2003 still) for managing my important lists such as my inventory list for my eBay store. I deal primarily in one-of-a-kind goods so I have, over time, over one thousand listings. This is a lot of listings to create, so to able to quickly copy and paste from my database to my listing is a plus.
You can use the “[control] + c” keyboard shortcut, or you can use the Edit | Copy menu, to copy. If you do so, you will need to select the entire contents of a field first (if you are using your mouse) and then use your keyboard, or you will need to click two times in your Access menu system. Some users don’t even know that these two options exist.
You may think that you can select the value of a field when entering it by going to Tools | Options | Keyboard and selecting Select entire field under the Behavior entering field section, but that works if you use your keyboard to enter a field; it does not apply if you enter a field using your mouse.
So, I have developed a small button system that I use to copy items in a particular field. I put a button to the right of the field that it works on. I can copy the contents of the field with one click and I don’t have to swipe and select the text I want to copy.
I have other buttons next to particular fields. For example, I may have buttons to search for text and to open the underlying lookup form so that I can edit the value. (I explain each of these in article devoted to each.)
I may end up with three buttons next to a field, all arranged in the same order each time: a C button to copy, an F button to find, and an E button to edit the lookup table for the field. I size the button heights to match my field heights and arrange them neatly (with a small space between each) for a cool and relaxed look.
You can easily add buttons in form design by using the Access button tool. It doesn’t matter what action you choose in this case because you will delete the code generated by the wizard, so choose one that doesn’t ask a lot of questions (try Go to First Record under Record Navigation).
You may have several fields on several forms that you want to copy the contents, so it is better to set each field to call one procedure so that you only need to maintain your code in one place.
Copying Text Boxes
For example, I have a field in my inventory database called InvName. (Don’t use just Name because that is an Access reserved word that would probably lead to a difficult to detect bug.) This field is a plain TextBox field because each inventory name is different and there is no need for a lookup table. You can add a button that has an Event Procedure for the OnClick Eevent in the form code as follows:
Call CopyTextBox(InvName)
Then add a procedure in one of your modules (outside of your form code) as follows:
Public Sub CopyTextBox(tbxFieldName As TextBox) If Not isnull(tbxFieldName) And tbxFieldName “” Then tbxFieldName.SetFocus tbxFieldName.SelStart = 0
tbxFieldName.SelLength = Len(tbxFieldName)
DoCmd.RunCommand acCmdCopy
Else MsgBox “Nothing to copy!”, vbOKOnly, “Copy Error”
End If End Sub
It would have been nice to use Screen.PreviousControl.SetFocus in order to set the focus to the field you want but that command sets the focus to the last field you entered. You could have one button for all copy actions this way, but you will have to first click on or enter a field and then click on the button. That’s two clicks or a keystroke and a click. Who can remember to do this all of the time?
Instead, you can simply click your C button for that field and instantly copy its value to your clipboard and know that it comes from the field you want, for sure.
The sub procedure expects the field name as a TextBox Database Object, not as a string. Call it tbxFieldName to remind yourself that this is a special text box object.
The if statement checks that the text box contains something. The isnull() function returns true if the field has a null value. The Not operator reverses the isnull() value, so it is an effective but awkward way of saying that there is a value for the field.
It is possible that a text box may not contain a null value but still contain an empty string value instead so we add:
And tbxFieldName “”
The string “” (a pair of double quotes with nothing between them) means an empty,, or zero length string.The And here is a logical operator so that both expressions have to be true to proceed to the true part of the if.
If there is a value in the field, then the next line of the procedure sets the focus to that field.
The next line after that sets the start of a selection to 0, the beginning of the field’s value. The following line sets the length of the selection to the length of the field’s value. Between the two statements, you end up selecting the entire value of your field. You can see the value of the field turning color as it is selected when you run this procedure by clicking your button.
The next line finally runs the Access copy command on the selection and puts the value into your clipboard. Now you can paste it somewhere, such as into your eBay listing title.
Finally, the Else part issues a message box if there is nothing to copy. That part is optional, and you can omit the else part entirely if you wish.
Copying Combo Boxes
Leaving the TextBox, how do you copy the value of a ComboBox? You may want to copy the value of a lookup field in a combo box such as a manufacturer’s name in an inventory database. If so, you will have to take some additional steps.
For each combo box you want to copy, add the following Event Procedure for its OnClick Event in your form code (here, our example copies the combo box for our ManufacturerID combo box):
Call CopyComboBox(Manufacturer)
Notice that our field is named ManufacturerID and that we store a numeric value in this field, the index of a record in the tblManufacturerLookup. The lookup table has two fields, an index field and a Manufacturer field which is a text field.
So, even though this is an OnClick Event Procedure for ManufacturerID, we call using the name of the text field, Manufacturer.
This works because we add a procedure called CopyComboBox which we place into our Copy module outside of our form code:
Public Sub CopyComboBox(cboFieldName As ComboBox)
If Not isnull(cboFieldName) And cboFieldName “” Then
cboFieldName.SetFocus cboFieldName.SelStart = 0
cboFieldName.SelLength = Len(cboFieldName)
DoCmd.RunCommand acCmdCopy
Else MsgBox “Nothing to copy!”, vbOKOnly, “Copy Error” End If End Sub
You may notice that this procedure follows the CopyTextBox procedure except that we pass the field name as a ComboBox database object and we name the parameter with a cbo prefix to remind us that of that.
The magic here is that Access refers to your text value rather than to the numerical value actually stored in your field, without any complex programming. You can even see the text value in your combo box change color as it is selected when you run this procedure.
Making A Copy Button
With both of those cases under our belts, let’s turn to how you create a handy copy button in form design mode:
Make a button using the button tool and its wizard.
Set the button to display text and set that text to C.
Call the button cmdCopy + Field Name, so to copy a field called CompanyName the button name would be cmdCopyCompanyName.
After the wizard completes, edit the button:
Set the font size to 6, a small but readable size.
Set the tab stop to NO because users do not need to stop at the button if they are tabbing through the form.
Set the Status Bar Text and the ControlTip Text to Copy Field so that users can easily remind themselves what the button does.
Then size the button as small as you can to be able to see the C. I use 0.1708 inches wide by 0.166 inches high with Arial text with my form grid spacing. Your size may vary. It works best when your button height matches the height of you field box, as mentioned above.
(After you create one button this way, you can copy and paste it with all of these settings set in the copy. All you have to do is to change the name of the button and add the appropriate OnClick Event Procedure.)
As a reminder, set the OnClick property of your button to [Event Procedure]. Then set the event procedure to act on a one specific field. You discard the wizard-written code and use your code instead. Remember that examples include:
Call CopyTextBox(InvName)
or
Call CopyComboBox(Manufacturer)
Each call to CopyTextBox or to CopyComboBox will have a different field name and that’s all you need to make these two procedures work for each instance.
Putting Procedures In Modules
It does matter where you put your procedure.
If you have many buttons but only one form, then you can add the procedure to the code for the form itself.
If you put your procedure into the form code, the scope of your procedure is valid for that form only. If you have a procedure in form 1 and you need to call it in form 2, you will get an error because form 2 cannot find it. In that case, you would either have to add another procedure to form 2, or better, you would move your procedure to a module so that both forms can find it and you only have to maintain one procedure.
I call my module General but you could add separate modules with one or more related procedures so you could easily import them into new databases. This could be your Copy module. You find the Modules section in the main database window along with Tables, Queries, Forms, Reports, and Macros.
Once you use a general module, your code references must also be general. You cannot use the Me shortcut for a field name in a module as you can in a procedure within a form. When a procedure is within a form, the code interprets Me to refer to the form.
Luckily we don’t have to resort to complex Access syntax to refer to a field or its value in these two cases. Passing a field name as a TextBox object or as a ComboBox object greatly simplifies referring to the value in the field. You can set your focus and select the value easily by referring to a action or a property of the TextBox or ComboBox object (tbxFieldName.SetFocus or cboFieldName.SelStart = 0, for example).
Last Words
Try this out on your Access forms and see if this is useful. I find that it helpful because seeing a button next to a field acts as a prompt to use it. If you forget what it does, place your mouse over it and read the tool tip or the status bar text.
If users see a button next to the field, and if you teach them that C means copy, they will use it. This is a variation on If you build it, they will come, except that a cornfield is not whispering to you (but maybe your mouse is).
I will show you how to add other useful form buttons to Access forms in other articles.
Thanks To : Anna Sui Fragrance Refinance Home Loan Soy Protein Invicta