Cut/Paste Feild values to new record

[nickiwinki]

I am very new to Access 2007 and have no experience with VBA.

I am keeping a list of employees in my building. Each employee has a unique record. Each record has all the equipment (computers, phones, printers, etc.) that each employee has in their possession.

To do this, I downloaded the Contacts Management Template from Access and then manipulated it. I've added tabs to the forms for each type of equipment and created all the fields I need. There is one table (Contacts) and there are multiple forms. The data in the forms are populated by queries. One query populates the equipment that is assigned to employees (Contact Details) and the other populates the spare equipment (Building Equipment Details)

In the forms, I would like to create a button to cut data from certain fields and put it in the clipboard. I would then like another button to paste the data from the clipboard into the same fields in another record.

Basically, this button would be used if the employee gets a new computer. The old computer should still be tracked, but in the new form for unused equipment. The employee's record would now have blank workstation fields, freeing it up to cut the data from one of the Building Equipment records and paste into the employee's record.

I found some threads on this basic idea, but not quite what I need.

The field names that are in both forms are:
WK Node Name
WK Make
WK Model
WK Bar Code
WK Service Tag
WK IP Address
WK MAC
WK Operating System
WK Value
WK Owner



Any help would be greatly appreciated, as I am very lost.

Nicole

[June7]

I have never seen code for copy/paste with clipboard in Access. Don't think can be done. Can use INSERT SELECT query for this. Look at http://www.sqlteam.com/article/using...insert-records

[nickiwinki]

I've seen threads that say it can be done with copy instead of cut. Any ideas on that?

I'm sorry I'm so lost.

I have never seen code for copy/paste with clipboard in Access. Don't think can be done. Can use INSERT SELECT query for this. Look at http://www.sqlteam.com/article/using...insert-records

[June7]

This might be what you were thinking of http://bytes.com/topic/access/answer...ltiple-records

It doesn't use the Windows clipboard. It sets variables to values of a record then after moving to another record the fields are populated with the values of variables.

This will copy content of a field to clipboard http://www.vbaexpress.com/forum/arch...p/t-32202.html


I still haven't found any VBA that will copy/paste entire record through the Windows clipboard.

[nickiwinki]

oh, it's not the entire record, just certain fields. The ones I listed above.

I'll check the links you sent....thank you!

[nickiwinki]

I think I just don't understand VBA. I have no idea how to change the code to do what I need it to do.
So lost.

[June7]

On re-reading your post, something appears very wrong here. Sounds like you are duplicating data. What you should have is a table of equipment, each record would have a unique ID. Can each employee have more than one equipment record - computer, monitor, printer, etc? If so, need another table that will associate employee with assigned equipment. Like

tblEmpEquip
EmpID
EquipID

All you have to do is change the EquipID in this table, assuming you don't care about historical info, only current equipment assigned.

If there is only one item per employee, then can put the equipment ID in the employee table.

Both queries should have the Equipment table as data source with a join to tblEmpEquip or employee table. Then filter as appropriate to view equipment either assigned or not assigned.

[nickiwinki]

Not duplicating. A computer is assigned to an employee. There are also a cache of unassigned machines. When an employee needs a new machine, the old one gets turned in and they are assigned a new one from the cache. We do care about historical data, as the machines do not get retired for some time, even if an employee is not using it.

That's why I wanted a button to cut the data to the clipboard, thus removing it from the employee's record, then I can paste it into the table that is the unused equipment. then I can take one of the unused machines, cut it from the record that it is in, and paste it into the employee's record.

There is an ID field which is the primary key. Each ID has one employee assigned to it. There is only one record per employee. But then there are records that have no employee, just the piece of equipment. But no, each user only has 1 computer, one printer, etc. (In the odd case that they have 2, I have a tab in my forms for extra equipment....but that almost never happens.)

They are all in one Table, that just happens to be called Contacts. Then I have one query that populates a form with the equipment that's assigned and another query that populates another form with the equipment that is not assigned. I don't manipulate the table at all when using the database, just the forms.

Does that make sense?

[June7]

What do you mean by 'cache' - a table? You have this 'cache' table and a table for the Employee and assigned equipment? That is two tables holding equipment info. My point is there should be only one. Juggling data between the 'cache' and employee table subverts the relational database concept. Determining what equipment is assigned/not assigned can be accomplished with queries. A properly structured relational database should not require cut/paste data between tables.

Historically, do you care that an employee had computer A for six months two years ago?

Again, will employee have multiple equipment?

If you insist must do this then INSERT SELECT sql may be best approach to copy data to the 'cache' table then in the employee table simply set the fields equal to Null. Do you also want to go the other way? When assigning equipment, remove from 'cache' table and set values of employee table?

I strongly advise you to reconsider approach to this.

This is like checking out books from a library. Might look into the Lending Library template.

[nickiwinki]

Cache as in the stock of equipment.

I have one table. It's called contacts. It has all the employees with their equipment. It also has unassigned equipment. I have a query that pulls the records for employees and populated the form. I have a copy of the same exact form, and another query that populates it with the unassigned equipment.

Basically, I want to take a computer that a user has, move it to a new record. Then take an assigned computer and put it in the employees record.

The computer is still here, even if they aren't using it, and it can possibly end up being another employee's at some point, which is why I want to keep a record for it. I don't care historically who's computer it was, prior to beginning unassigned.

Sorry it's so confusing.

What do you mean by 'cache' - a table? You have this 'cache' table and a table for the Employee and assigned equipment? That is two tables holding equipment info. My point is there should be only one. Juggling data between the 'cache' and employee table subverts the relational database concept. Determining what equipment is assigned/not assigned can be accomplished with queries. A properly structured relational database should not require cut/paste data between tables.

Historically, do you care that an employee had computer A for six months two years ago?

Again, will employee have multiple equipment?

If you insist must do this then INSERT SELECT sql may be best approach to copy data to the 'cache' table then in the employee table simply set the fields equal to Null. Do you also want to go the other way? When assigning equipment, remove from 'cache' table and set values of employee table?

I strongly advise you to reconsider approach to this.

This is like checking out books from a library. Might look into the Lending Library template.

[June7]

Cache is just computers sitting in a room? No table? Then my recommendation is you create the table.

tblEquipment
EquipID (primary key)
etc

tblEmployees
EmpID (primary key)
EquipID (foreign key)
etc

Now all you have to do is change the value in the Employee table EquipID field, no VBA required. Assumes employee can have only one equipment assigned. If multiple equipment assigned then need junction table.

tblEmpEquip
EmpID (foreign key)
EquipID (foreign key)

This is relational database. Otherwise, might as well just be a spreadsheet.

[nickiwinki]

Ok, I'm not going to do that, since the database is exactly what I need, short of 2 functions. I need to make a button for copying only certain fields to the clipboard from on record. Then I need a button to paste them into the same fields in another record.

Thanks anyway. I can keep looking.

Cache is just computers sitting in a room? No table? Then my recommendation is you create the table.

tblEquipment
EquipID (primary key)
etc

tblEmployees
EmpID (primary key)
EquipID (foreign key)
etc

Now all you have to do is change the value in the Employee table EquipID field, no VBA required. Assumes employee can have only one equipment assigned. If multiple equipment assigned then need junction table.

tblEmpEquip
EmpID (foreign key)
EquipID (foreign key)

This is relational database. Otherwise, might as well just be a spreadsheet.

[June7]

I did a lot of searching for copy/paste through clipboard. I found code to copy single field data or entire record, nothing for a subset of multiple fields to clipboard except as one long concatenated text string.

This is the closest I can find to what you want. One of the suggestions sets variables, moves to New Record, sets fields. In your case you can also set the source fields to null then move to new record. It could be adapted to move to an existing record, which requires more than the single line of code that moves to New Record.

http://www.tek-tips.com/viewthread.cfm?qid=1262966

So I wish you luck on your search.

[nickiwinki]

Thanks again for your help.

I found the following, regarding copying to the clipboard. http://support.microsoft.com/default...roduct=acc2000

It's just a matter of not knowing how to manipulate the code for copying multiple fields.

Maybe someday.

[June7]

That could be modified to concatenate multiple fields to a single string, which is one of the examples I ran across. Does not keep the data as distinct fields. I very much doubt can be done for only some fields of a record. An entire record can be copy/pasted.

I still think would be much easier to modify the database.