Copy a selected row from a subform to a new table

[TGVisser]

Hello all,

This is my first post/question on here, hope I can make myself clear :)

My employer wants me to build a storage management system which shows him his current supply of parts.

What I am trying to do is this:
I have a datasheet-subform on a normal form.
This subform shows data from a table (named searchtable).
The mainform has some controls to filter the data in the subform, all of that works.
Now I would like to click one of the visible records in the subform and copy that specific record to a new table (FetchTable) by pressing a button.

The searchtable contains the following columns:
Barcode | Description | Supply | Location
-------------------------------------------
text | text | double | text

The barcode column contains a unique key for each item.

I have read the solutions of doing this while using a textbox containing the key and a query with a WHERE clause.
Is there a way to click on a row and copy the selected row?

[TGVisser]

Figured it out.

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("FetchTable", dbOpenDynaset)
 
    rs.AddNew
    rs!Aantal = Forms!MainForm.FetchAantalBox.Value
    rs!Omschrijving = Forms!MainForm!SearchTableControl!Omschrijving
    rs!Locatie = Forms!MainForm!SearchTableControl!Locatie
    rs.Update
 
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
 

[ADezii]

1. I prefer a different approach that does not require you to:
   1. Create a Command Button on the Form.
   2. Select a Record in the Sub-Form.
   3. Click on the Command Button to execute the Copy Operation.
2. My approach:
   1. Places Code in the DblClick() Event of the Primaty Key on the Sub-Form [Barcode].
   2. Once the [Barcode] Field is Dbl-Clicked, and it is not a New Record, will prompt the User to see if they wish to Copy the Record associated with that [Barcode] to FetchTable.
   3. If the User clicks Yes, performs the Copy Operation.
3. This is simply just another approach, so I'll Post the Code for your Review:

Private Sub Barcode_DblClick(Cancel As Integer)
Dim intResponse As Integer
Dim strSQL As String
 
With Me
  strSQL = "INSERT INTO FetchTable ([Barcode],[Description],[Supply],[Location]) " & _
           "VALUES('" & ![Barcode] & "','" & ![Description] & "'," & ![Supply] & "," & _
           "'" & ![Location] & "')"
 
  If Not .NewRecord Then
    intResponse = MsgBox("Copy Record with BarCode: [" & ![Barcode] & "] to FetchTable?", _
                          vbQuestion + vbYesNo + vbDefaultButton1, "Copy Confirmation")
      CurrentDb.Execute strSQL, dbFailOnError
  End If
End With
End Sub