Copying data from one table to another..

[Someone123]

Hi,

I'm looking to include a button on a form that opens a new entry in another table and copies some field entries from the first table into the second table. I tried an append query, but it seems to be unable to be displayed in a form. I just want to copy data from one form to another. Thanks.

 

[boblarson]

An append query will append data from one table (or tables) to another. If you want data moved between FORMS just use:

Forms!YourSecondFormNameHere.YourControlNameHere = Me.YourControlOnFirstFormHere

 

[neileg]

In general, duplicating data is a bad idea. If exists in one table, usually a query to retrieve the data is the best way.

However, without knowing more about what you are doing, it's a bit tricky to advise...

 

[Someone123]

Thanks for replying.

The idea is that customers fill in a defect form, reporting defects in equipment.

The engineers then repsond to this defect in a fault report form. The idea is to grab the "device", "date" and "description" fields of the defect and then copy them into the relavent fault report table forms, using a button. Once the engineer fills in the fault report entry, another button is pressed, in order to change a few fields (auto) on the defect report forms.

I may just redesign the tables, but I would like to save alot of data and work and do it this way if possible.

 

[neileg]

Data isn't held in forms, it's held in tables. Forms are based on queries (using good practice) and queries can be based on more than one table. So there's no need to copy the data. All you need to retrieve the "device", "date" and "description" fields is the primary key value from the fault report table.

 

[JCShort]

I realize this thread is old, so I hope someone keeps up with the old posts!

What criteria is used in the Append Query to only transfer the active record on the form to the other table?

I have 2 tables and 2 forms respectively. Table 1 is preloaded with thousands of records, while Table 2 is loaded by the user with records on an as needed basis. However, when the Append Query is run using a command button on Table 1, it appends ALL records into Table 2. I just need the current record.

Is that possible?

 

[neileg]

You need to use the primary key of the record currently displayed in the form as a criterion in your append query. You reference a control on a form like this:
Forms!MyFormName!MyControlName

 

[JCShort]

Thanks Neil! I had a feeling that was the case. The fields that I wanted to copy are pretty common and can be found multiple times in each table.

However, as I was trying to identify a way to apply the primary key so that it worked and wouldn't disrupt any of my data, a programmer that works w/ SQL happened to walk by and ask me what I was doing. It took him about 3 minutes to write code for it. Looks like I need to start learning SQL!

In case anyone else is looking to do this same type of function in the future, here's the code that worked for my purposes:

Private Sub Button_Click()
On Error GoTo Err_Button_Click
Dim ssql As String

ssql = "Insert into [Recipiant Table] (txtField1b, txtField2b, txtField3b, txtField4b, txtField5b, dateField1b ) " & _
"Values ('" & Me.txtField1a & "','" & Me.txtField2a & "','" & Me.txtField3a & "','" & Me.txtField4a & "','" & Me.txtField5a & "',#" & Now() & "#);"
CurrentDb.Execute (ssql)
MsgBox ("This data was loaded into the new table.")

Exit_Button_Click:
Exit Sub
Err_Button_Click:
MsgBox Err.Description
Resume Exit_Button_Click

End Sub

This takes the info from txtField1a on the current form and adds it to txtField1b in the other table.

Thanks again!