468,167 Members | 1,931 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,167 developers. It's quick & easy.

using command button to copy a record in a field

Hi.
I am using Access 2010,

I have a table like below.
In a form, I have a combo box linked with the NAME field and have a buttonm, COPY next to it.

ID NAME NUMBER
1 BACH 1-1212
2 MOZART 2-2323
3 BEETHOVEN 3-4343
4 VIVALDI 4-5656



I would like to make the COPY button functioning copy the NUMBER when I choose a NAME from the combo box. I will paste the number somewhere else as clipboard.
Of course the NAME and the NUMBER should be the same record.
I couldn't find any COPY command in macro but does not matter VB command.

Thanks~
Mar 14 '21 #1

✓ answered by cactusdata

Create a textbox and name it Column2.
It can have a width of zero and be placed anywhere on the form.

Set its ControlSource to: =[NameOfYourCombobox].[Column](2)

Create an AfterUpdate event of the combobox:

Expand|Select|Wrap|Line Numbers
  1. Private Sub NameOfYourCombobox_Click()
  2.  
  3.     Me!Column2.SetFocus
  4.     DoCmd.RunCommand acCmdCopy
  5.     Me!NameOfYourCombobox.SetFocus
  6.  
  7. End Sub
Select a name, and you can paste the value of column2 where you like.

2 2281
isladogs
232 Expert 128KB
First of all both Name and Number are reserved words in Access. Both should be changed or they will cause issues later.

Saving to the clipboard requires the use of an API which cannot be done using a macro.
Whilst it is possible in code, it really isn't necessary in this situation
Instead add code to your combo after update event similar to this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub YourComboName_AfterUpdate()
  2. Dim strText As String
  3. StrText = Me.YourComboName.Column(2) 'this is the third column as numbering starts at 0"
  4. End Sub
  5.  
You can then reuse that strText variable elsewhere. However to prevent it going out of scope outside this procedure/form, you should either define it in the form declarations area or as a public variable in a standard module
Mar 14 '21 #2
cactusdata
164 Expert 128KB
Create a textbox and name it Column2.
It can have a width of zero and be placed anywhere on the form.

Set its ControlSource to: =[NameOfYourCombobox].[Column](2)

Create an AfterUpdate event of the combobox:

Expand|Select|Wrap|Line Numbers
  1. Private Sub NameOfYourCombobox_Click()
  2.  
  3.     Me!Column2.SetFocus
  4.     DoCmd.RunCommand acCmdCopy
  5.     Me!NameOfYourCombobox.SetFocus
  6.  
  7. End Sub
Select a name, and you can paste the value of column2 where you like.
Mar 15 '21 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

By using this site, you agree to our Privacy Policy and Terms of Use.