By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,442 Members | 1,332 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,442 IT Pros & Developers. It's quick & easy.

Copy a selected row from a subform to a new table

P: 3
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?
Mar 3 '14 #1
Share this Question
Share on Google+
2 Replies

P: 3
Figured it out.

Expand|Select|Wrap|Line Numbers
  1.     Dim db As DAO.Database
  2.     Dim rs As DAO.Recordset
  3.     Set db = CurrentDb
  4.     Set rs = db.OpenRecordset("FetchTable", dbOpenDynaset)
  6.     rs.AddNew
  7.     rs!Aantal = Forms!MainForm.FetchAantalBox.Value
  8.     rs!Omschrijving = Forms!MainForm!SearchTableControl!Omschrijving
  9.     rs!Locatie = Forms!MainForm!SearchTableControl!Locatie
  10.     rs.Update
  12.     rs.Close
  13.     db.Close
  14.     Set rs = Nothing
  15.     Set db = Nothing
Mar 3 '14 #2

Expert 5K+
P: 8,701
  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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Barcode_DblClick(Cancel As Integer)
  2. Dim intResponse As Integer
  3. Dim strSQL As String
  5. With Me
  6.   strSQL = "INSERT INTO FetchTable ([Barcode],[Description],[Supply],[Location]) " & _
  7.            "VALUES('" & ![Barcode] & "','" & ![Description] & "'," & ![Supply] & "," & _
  8.            "'" & ![Location] & "')"
  10.   If Not .NewRecord Then
  11.     intResponse = MsgBox("Copy Record with BarCode: [" & ![Barcode] & "] to FetchTable?", _
  12.                           vbQuestion + vbYesNo + vbDefaultButton1, "Copy Confirmation")
  13.       CurrentDb.Execute strSQL, dbFailOnError
  14.   End If
  15. End With
  16. End Sub
Mar 3 '14 #3

Post your reply

Sign in to post your reply or Sign up for a free account.