Transfer fields to another table

[Steve via AccessMonster.com]

I've reviewed several postings regarding transferring data from one table to
another and I'm not quite getting what I need to do. I have 2 tables holding
client information for our training program. There are situations where a
client can come in for a youth program but at some point transfer to another
training program so I would like to press a button and transfer certain
fields to the other table. I've seen all of the warnings on transferring data
and all of the "Why's" and "Its not a good Idea" which I understand but it
needs to be setup this way. The other issue is using the append feature could
cause duplicates which I dont want to happen. Can somebosy tell me how to do
the following:

Take the following fields from the Youth Table:
SSN
FName
LName
Address
City
State
Zip
DOB

and transferr to Training Table on a button click. The fields on the Training
Table have the same names.

My dillema:

1) When the transfer button is pressed, another form will appear for the
training module that will show a new blank record with just the fields from
the youth table entered into it. (The user will fill out any other additional
fields at that point)
2) The training table has several other fields with information so I only
want the fields listed above to go to the training table fields as a new
record
3) SSN is unique so I need a message to prompt a user if they click the
transfer button and the SSN already exists in the training table. I do have
message indicators showing when a training record is not entered for a youth
but sometimes these dont prove to be enough when it comes to preventing
mistakes.

Thanks
Steve

 

[bhipwell via AccessMonster.com]

Steve,

First, I would recommend just having a field on one table that tells you what
program an individual is in. It doesn't seem to make much sense to have
different tables containing 95% of the same data. You can always filter for
individuals only in program #1 or program #2.

Anyhoo, if you were interested in transfering data, I have a work around that
I have used for tiny snipets of data. First create a form with fields of
data from table one. Create a button that will effectively open a second
form tied to table #2. On the OnOpen property, use some code that will first
create a new record and "transfer" data to the corresponding fields such as:

Me.Table2Info = Forms("Table1Form).Controls("Table1Info")

You would do this for each field you want transferred, and you can add blank
fields for that additional information. Not the cleanest way, but you will
avoid some errors and have an easier time troubleshooting.

I would still recommend using one table to contain all data about a
particular individual. Perhaps a simple drop down menu to select the correct
program and then a Visible property code to display the fields necessary to
fill when someone changes.

B

 

[bhipwell via AccessMonster.com]

-
Steve,

First, I would recommend just having a field on one table that tells you what
program an individual is in. It doesn't seem to make much sense to have
different tables containing 95% of the same data. You can always filter for
individuals only in program #1 or program #2.

Anyhoo, if you were interested in transfering data, I have a work around that
I have used for tiny snipets of data. First create a form with fields of
data from table one. Create a button that will effectively open a second
form tied to table #2. On the OnOpen property, use some code that will first
create a new record and "transfer" data to the corresponding fields such as:

Me.Table2Info = Forms("Table1Form).Controls("Table1Info")

You would do this for each field you want transferred, and you can add blank
fields for that additional information. Not the cleanest way, but you will
avoid some errors and have an easier time troubleshooting.

I would still recommend using one table to contain all data about a
particular individual. Perhaps a simple drop down menu to select the correct
program and then a Visible property code to display the fields necessary to
fill when someone changes.

B

 

[scarlton via AccessMonster.com]

Good Morning,

So I have a Youth Form called frm_Youth with that uses tbl_ClientAppYouth as
a record source. The fields in that table are labled [SSN], [FName], [LName],
etc I have a seperate form called frm_CTLCMain that uses tbl_Students as a
record source with the same field names (i.e. [SSN], [LName], [FName], etc).

There is a text box control on frm_Youth that called CTLCChk that does a
Dlookup to see if a student record exists in the tbl_Students table and
returns a message as to whether its there or not. So IF one doesnt exist and
IF we want to create a student account I can press the CTLCChk to open the
frm_CTLCMain with a blank record and thats where the fields listed above will
get transferred. How do I use your code below to do that because I'm not
quite following it. I would prefer the code to appear in the OnClick property
of the CTLCChk text box control as opposed to the OnOpen property for the
frm_CTLCMain because there will be plenty of times where that form will get
opened with data needing to be transferred.

Thanks in advance for your help. I understand what you were saying about
using one table and in most situations I do that but this particular
situation, because of what we're doing here, requires me to keep them
seperate without creating several other issues that we're trying to report on.