copying fields in access by a macro/button on a form

Question

Good day

ms-access 2007

I have 2 sub-Datasheet's on my form, One display a list of items. the other is blank.

Is there a way by placing a button on the form "Copy/Add", it copies the highlighted field to a field in the blank data-sheet.

both field's are of the same type.

Thank you

Answer 1

Probably the easiest thing is to run an append query. Let us say the button is on the main form, you would need a little code on the lines of:

Set db = CurrentDB
strSQL = "INSERT INTO TableB (ID, SomeField) " _
       & "SELECT ID, SomeField FROM TableA WHERE ID = " _
       & Me.[NameOfSubformControl].Form.[NumericIDField]
db.Execute strSQL, dbFailOnError

You will need quotes if the ID field is not numeric.