I exploit MS Entry (Home windows model 2003 nonetheless) for managing my vital lists akin to my stock checklist for my eBay retailer. I deal primarily in one-of-a-kind items so I’ve, over time, over one thousand listings. It is a lot of listings to create, so to capable of rapidly copy and paste from my database to my itemizing is a plus.

You should use the “[control] + c” keyboard shortcut, or you need to use the Edit | Copy menu, to repeat. Should you achieve this, you’ll need to pick out the complete contents of a discipline first (in case you are utilizing your mouse) after which use your keyboard, or you’ll need to click on two occasions in your Entry menu system. Some customers do not even know that these two choices exist.

It’s possible you’ll assume which you can choose the worth of a discipline when coming into it by going to Instruments | Choices | Keyboard and deciding on Choose whole discipline beneath the Habits coming into discipline part, however that works should you use your keyboard to enter a discipline; it doesn’t apply should you enter a discipline utilizing your mouse.

So, I’ve developed a small button system that I exploit to repeat gadgets in a specific discipline. I put a button to the fitting of the sphere that it really works on. I can copy the contents of the sphere with one click on and I haven’t got to swipe and choose the textual content I wish to copy.

I’ve different buttons subsequent to explicit fields. For instance, I could have buttons to seek for textual content and to open the underlying lookup kind in order that I can edit the worth. (I clarify every of those in article devoted to every.)

I could find yourself with three buttons subsequent to a discipline, all organized in the identical order every time: a C button to repeat, an F button to search out, and an E button to edit the lookup desk for the sphere. I measurement the button heights to match my discipline heights and organize them neatly (with a small area between every) for a cool and relaxed look.

You may simply add buttons in kind design by utilizing the Entry button device. It does not matter what motion you select on this case as a result of you’ll delete the code generated by the wizard, so select one that does not ask lots of questions (attempt Go to First Report beneath Report Navigation).

You might have a number of fields on a number of kinds that you just wish to copy the contents, so it’s higher to set every discipline to name one process so that you just solely want to keep up your code in a single place.

Copying Textual content Bins

For instance, I’ve a discipline in my stock database referred to as InvName. (Do not use simply Identify as a result of that’s an Entry reserved phrase that may in all probability result in a tough to detect bug.) This discipline is a plain TextBox discipline as a result of every stock title is completely different and there’s no want for a lookup desk. You may add a button that has an Occasion Process for the OnClick Eevent within the kind code as follows:

Name CopyTextBox(InvName)
Then add a process in one in all your modules (outdoors of your kind 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 repeat!”, vbOKOnly, “Copy Error”

Finish If Finish Sub

It might have been good to make use of Display screen.PreviousControl.SetFocus as a way to set the main focus to the sphere you need however that command units the main focus to the final discipline you entered. You may have one button for all copy actions this fashion, however you’ll have to first click on on or enter a discipline after which click on on the button. That is two clicks or a keystroke and a click on. Who can keep in mind to do that all the time?

As an alternative, you’ll be able to merely click on your C button for that discipline and immediately copy its worth to your clipboard and know that it comes from the sphere you need, for certain.

The sub process expects the sphere title as a TextBox Database Object, not as a string. Name it tbxFieldName to remind your self that this can be a particular textual content field object.

The if assertion checks that the textual content field comprises one thing. The isnull() operate returns true if the sphere has a null worth. The Not operator reverses the isnull() worth, so it’s an efficient however awkward approach of claiming that there’s a worth for the sphere.

It’s potential {that a} textual content field could not comprise a null worth however nonetheless comprise an empty string worth as an alternative so we add:

And tbxFieldName <> “”

The string “” (a pair of double quotes with nothing between them) means an empty,, or zero size string.The And here’s a logical operator in order that each expressions should be true to proceed to the true a part of the if.

If there’s a worth within the discipline, then the subsequent line of the process units the main focus to that discipline.

The following line after that units the beginning of a range to 0, the start of the sphere’s worth. The next line units the size of the choice to the size of the sphere’s worth. Between the 2 statements, you find yourself deciding on the complete worth of your discipline. You may see the worth of the sphere turning coloration as it’s chosen while you run this process by clicking your button.

The following line lastly runs the Entry copy command on the choice and places the worth into your clipboard. Now you’ll be able to paste it someplace, akin to into your eBay itemizing title.

Lastly, the Else half points a message field if there may be nothing to repeat. That half is optionally available, and you may omit the else half fully if you want.

Copying Combo Bins

Leaving the TextBox, how do you copy the worth of a ComboBox? It’s possible you’ll wish to copy the worth of a lookup discipline in a combo field akin to a producer’s title in a list database. In that case, you’ll have to take some further steps.

For every combo field you wish to copy, add the next Occasion Process for its OnClick Occasion in your kind code (right here, our instance copies the combo field for our ManufacturerID combo field):

Name CopyComboBox(Producer)

Discover that our discipline is called ManufacturerID and that we retailer a numeric worth on this discipline, the index of a file within the tblManufacturerLookup. The lookup desk has two fields, an index discipline and a Producer discipline which is a textual content discipline.

So, regardless that that is an OnClick Occasion Process for ManufacturerID, we name utilizing the title of the textual content discipline, Producer.

This works as a result of we add a process referred to as CopyComboBox which we place into our Copy module outdoors of our kind 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 repeat!”, vbOKOnly, “Copy Error” Finish If Finish Sub

It’s possible you’ll discover that this process follows the CopyTextBox process besides that we move the sphere title as a ComboBox database object and we title the parameter with a cbo prefix to remind us that of that.

The magic right here is that Entry refers to your textual content worth slightly than to the numerical worth really saved in your discipline, with none advanced programming. You may even see the textual content worth in your combo field change coloration as it’s chosen while you run this process.

Making A Copy Button

With each of these instances beneath our belts, let’s flip to the way you create a useful copy button in kind design mode:

 

• Make a button utilizing the button device and its wizard.
• Set the button to show textual content and set that textual content to C.
• Name the button cmdCopy + Discipline Identify, so to repeat a discipline referred to as CompanyName the button title can be cmdCopyCompanyName.

 

After the wizard completes, edit the button:

 

• Set the font measurement to six, a small however readable measurement.
• Set the tab cease to NO as a result of customers don’t must cease on the button if they’re tabbing by means of the shape.
• Set the Standing Bar Textual content and the ControlTip Textual content to Copy Discipline in order that customers can simply remind themselves what the button does.

 

Then measurement the button as small as you’ll be able to to have the ability to see the C. I exploit 0.1708 inches broad by 0.166 inches excessive with Arial textual content with my kind grid spacing. Your measurement could fluctuate. It really works greatest when your button top matches the peak of you discipline field, as talked about above.

(After you create one button this fashion, you’ll be able to ad copy and paste it with all of those settings set within the copy. All it’s a must to do is to vary the title of the button and add the suitable OnClick Occasion Process.)

As a reminder, set the OnClick property of your button to [Event Procedure]. Then set the occasion process to behave on a one particular discipline. You discard the wizard-written code and use your code as an alternative. Keep in mind that examples embrace:

Name CopyTextBox(InvName)

or

Name CopyComboBox(Producer)

Every name to CopyTextBox or to CopyComboBox can have a unique discipline title and that is all you have to make these two procedures work for every occasion.

Placing Procedures In Modules

It does matter the place you set your process.

If in case you have many buttons however just one kind, then you’ll be able to add the process to the code for the shape itself.

Should you put your process into the shape code, the scope of your process is legitimate for that kind solely. If in case you have a process in kind 1 and you have to name it in kind 2, you’ll get an error as a result of kind 2 can’t discover it. In that case, you’d both have so as to add one other process to kind 2, or higher, you’d transfer your process to a module in order that each kinds can discover it and also you solely have to keep up one process.

I name my module Common however you possibly can add separate modules with a number of associated procedures so you possibly can simply import them into new databases. This may very well be your Copy module. You discover the Modules part in the principle database window together with Tables, Queries, Kinds, Experiences, and Macros.

As soon as you utilize a common module, your code references should even be common. You can not use the Me shortcut for a discipline title in a module as you’ll be able to in a process inside a kind. When a process is inside a kind, the code interprets Me to check with the shape.

Fortunately we do not have to resort to advanced Entry syntax to check with a discipline or its worth in these two instances. Passing a discipline title as a TextBox object or as a ComboBox object significantly simplifies referring to the worth within the discipline. You may set your focus and choose the worth simply by referring to a motion or a property of the TextBox or ComboBox object (tbxFieldName.SetFocus or cboFieldName.SelStart = 0, for instance).

Final Phrases

Do this out in your Entry kinds and see if that is helpful. I discover that it useful as a result of seeing a button subsequent to a discipline acts as a immediate to make use of it. Should you overlook what it does, place your mouse over it and browse the device tip or the standing bar textual content.