copy values to another table

kitty77 · Jan 1, 2020

I have a form that has four fields on it. I would like to take those four field values and add them to another record in another table. Would like to use a command button. What is the best approach?

Field1, Field2, Field3, Field4

Thanks...

theDBguy · Jan 1, 2020

Hi. Storing duplicate information in multiple tables is usually not necessary nor desired, unless you have a valid reason. Would you mind stating why you want to do this? Thanks.

Cronk · Jan 1, 2020

I agree with dbGuy as to duplicating data. However

Code:

currentdb.execute "Insert into YourTable (yourNumericField1, yourNumericField2, yourTextField, yourDateField) select " & Field1 & ", " & Field2 & ",'" & Field3 & "', #" & format(Field4,"mm/dd/yyyy") & "#"

You will need to substitute your own table/field names and change the formatting of the input data depending on the type of data in your table.

theDBguy · Jan 1, 2020

Cronk said:
I agree with dbGuy as to duplicating data. However

Code:

currentdb.execute "Insert into YourTable (yourNumericField1, yourNumericField2, yourTextField, yourDateField) select " & Field1 & ", " & Field2 & ",'" & Field3 & "', #" & format(Field4,"mm/dd/yyyy") & "#"

You will need to substitute your own table/field names and change the formatting of the input data depending on the type of data in your table.

Hi Cronk. LOL. No offense, but that almost sounded like:

"I agree, playing with a loaded gun is dangerous, but here's a loaded gun - just pull the trigger."

vba_php · Jan 1, 2020

kitty,

since I'm the black sheep in the group around here, and if you are in a situation where you have no choice but to do what you're doing, then you might want to run this code behind the button:

Code:

dim db as database
dim rsTarget as recordset
set db = currentdb
set rsTarget = db.openrecordset("name of the table where your target record is")

rsTarget.movelast
rsTarget.movefirst

rsTarget.findfirst "[target table's field name you want to use for the search]" = " & me.[name of the primary key ID field control on the form]

rsTarget.edit
rsTarget.fields("name of first field you want to receive a form control value") = me.[form control name here]

'do the above line 3 more times for the remaining 3 form controls
rsTarget.update

rsTarget.close
set rsTarget = nothing
set db = nothing

isladogs · Jan 1, 2020

I agree. Don't duplicate data unless e.g. that is a preliminary to removing it from the first table.

However if you really must copy the data across, use an append query (INSERT...) rather than write unnecessary code to do this with a recordset

Cronk · Jan 1, 2020

dbGuy
Re #4

"I agree, playing with a loaded gun is dangerous, but here's a loaded gun - just pull the trigger."

No offence taken but we don't know the full circumstances and I would have put it
"I agree, playing with a loaded gun is dangerous , but if you really need it here's a loaded gun - just pull the trigger."

theDBguy · Jan 1, 2020

Cronk said:
dbGuy
Re #4

No offence taken but we don't know the full circumstances and I would have put it
"I agree, playing with a loaded gun is dangerous , but if you really need it here's a loaded gun - just pull the trigger."

Got it! Thanks!

kitty77 · Jan 1, 2020

Sure... I have an input form. Used for data entry.
So, while the on the input form, the user will simply click a button that would send that info in those fields to the table.

That info is used to make labels. So, the user can pick and choose when they may want a label for that record or customer.

Hope that makes sense...

copy values to another table

kitty77

Registered User.

theDBguy

I’m here to help

Cronk

Registered User.

theDBguy

I’m here to help

vba_php

Forum Troll

isladogs

MVP / VIP

Cronk

Registered User.

theDBguy

I’m here to help

kitty77

Registered User.

Similar threads

Users who are viewing this thread