Access data transfers

[Brian Walsh]

Hi to all that might be able to help me.
I have created a pretty extensive database that has switchboards and all all of the like.
My delima is that I need to be able to trasfer single recods from a form to another table. I would like to do this with a command using an event proceedure. I am developing this for my fellow coworkers to enable them to more effiecienty view thier data and move data around as need arises. Here is what I have in a nut shell.

1. 3 Data Tables all struclturly the same

2. Each record is queried to a form specified by certain criteria.

3.I need to be able move the data from the form to a different table and back. and in the process delete the reocrd that is being moved form the current form/table

I know this sounds a little conveluded, but it is hard to explain online.

Thanks for any help.

[ADezii]

Hi to all that might be able to help me.
I have created a pretty extensive database that has switchboards and all all of the like.
My delima is that I need to be able to trasfer single recods from a form to another table. I would like to do this with a command using an event proceedure. I am developing this for my fellow coworkers to enable them to more effiecienty view thier data and move data around as need arises. Here is what I have in a nut shell.

1. 3 Data Tables all struclturly the same

2. Each record is queried to a form specified by certain criteria.

3.I need to be able move the data from the form to a different table and back. and in the process delete the reocrd that is being moved form the current form/table

I know this sounds a little conveluded, but it is hard to explain online.

Thanks for any help.

Assumptions:

1. 3 Fields exist on an Employees Form. They are [LastName], [FirstName], and [MI]
2. The Table to copy the Record to is called tblData1 and it consists of 3 Fields: [Last], [First], and [Middle Initial]
3. The following code will copy these 3 Fields on the Form to corresponding Fields in tblData1, then Delete the Form Record. Be advised that after deletion of the Record, there will be no Current Record, so move the Record Pointer wherever you so desire.
4. Good Luck!

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblData1", dbOpenDynaset)
 
With MyRS
  .AddNew
    MyRS![Last] = Me![LastName]
    MyRS![First] = Me![FirstName]
    MyRS![Middle Initial] = Me![MI]
  .Update
End With
 
 'Now, lets Delete the Record with no warnings
DoCmd.SetWarnings False
  DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
  DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings True
 
MyRS.Close

[Brian Walsh]

Thanks for your help.
the code is returning the last name field saying that it is not there.
The tables that I am using are Open2, Denied and Maintenance
The cliams go from Open2 to Denied or Maintenance
The fields that I have are:
Last Activity Date
Current ARU
First Name
Last Name
DOI
RTWC Comments
All of the dbs are structurally the same

Thanks for all of your help.

[ADezii]

Thanks for your help.
the code is returning the last name field saying that it is not there.
The tables that I am using are Open2, Denied and Maintenance
The cliams go from Open2 to Denied or Maintenance
The fields that I have are:
Last Activity Date
Current ARU
First Name
Last Name
DOI
RTWC Comments
All of the dbs are structurally the same

Thanks for all of your help.

You're quite welcome. Just make the appropriate substitutions and you'll be fine.