Results 1 to 8 of 8
  1. #1
    DarrenUD is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    22

    Unhappy Copy selected row from one data grid to another table

    Hi All,

    I am new to your forum and I am a beginner programmer in VB and VBA. I am working on a small inventory control system and I and kind of a stuck on this particular problem, so here it is:

    I have a search form and it is suppose to search and filter the criteria I enter from the text boxes and combo boxes and display the resulted query on the sub form (datasheet view). From the filtered results I want to select the product from the shortlist and add it as a new record in the "purchase order details" table with a click of a command button. moreover, I want to add an extra field of "quantity" to the "purchase order details" table with the selected data from the product search query.My knowledge of Access and VBA programming limited, Please Help.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,031
    Maybe something like:

    CurrentDb.Execute "INSERT INTO tablename (ProductID, Quantity) VALUES (" & Me.productID & ", " & Me.quantity & ")"

    The syntax for referencing the variable ProductID and Quantity depends on where the the code is in relation to the variables. This might be easier if you use a listbox for the 'shortlist' instead of a subform. Do you allow data entry/edit in the subform?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    DarrenUD is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    22
    Hi June, Thanks for the reply, I use three combo boxes and one text box for user input and another one to enter the Qty and that suppose to go to the second table with the fields from the first table. all the editing and deleting records are disabled. I have attach a picture for your reference. what if I have spaces in table or query names, is that going to be a problem.
    Click image for larger version. 

Name:	form_zps7473f8cd.jpg 
Views:	13 
Size:	41.3 KB 
ID:	9495

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,031
    If names include spaces, special characters, punctuation (underscore is exception) or are reserved words, enclose in [].

    Did you consider suggestion for listbox instead of subform?

    Did you attempt code?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    DarrenUD is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    22
    I haven't tried the list box, I got manged to get the code working for couple of fields except for the attachment. here it is:

    Private Sub Command26_Click()
    Dim strSQL As String

    strSQL = "INSERT INTO xxx_Order_Details ([Model No],Attachment) VALUES ('" & Form![xxxOrderSearchQuery]![Model No] & "','" & Form![xxxOrderSearchQuery]![Attachment] & "')"


    CurrentDb.Execute strSQL, dbFailOnError
    Me.xxx_Order_Details_subform.Requery
    End Sub

    If I use this SQL for other fields it works, I am still working on it, Thanks again for the helping me, by the way how do I use the list box, do I have to use one list box for every field?

  6. #6
    DarrenUD is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    22
    Tried the list box and I guess it is better than the sub form..hope my SQL works with the list box the same way...

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,031
    Can't use SQL action to copy into or from attachment field. Review http://blogs.office.com/b/microsoft-...cess-2007.aspx
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    DarrenUD is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    22
    Bummer ...I have to remove the Attachment field then...Thanks June7

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 05-10-2012, 08:20 PM
  2. copy data from one table to another
    By Sureshbabu in forum Access
    Replies: 1
    Last Post: 01-08-2012, 01:27 PM
  3. Replies: 3
    Last Post: 05-07-2011, 10:25 AM
  4. Replies: 1
    Last Post: 05-25-2010, 02:58 PM
  5. Grid lines vs Grid Dots
    By dharriet in forum Access
    Replies: 0
    Last Post: 10-14-2008, 09:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums