Results 1 to 5 of 5
  1. #1
    Hannah is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    4

    Copying Data in tables

    In my database I have a form which is based on query. It displays in tabular format.
    I have an extra text box where the user will enter a new value.
    Then a button where the values are inserted into a table.
    I want the button to add each row of the query into the table with the new value but at the moment can only get it to add the first.
    Any ideas how I can do this?
    The code on my button currently is shown below Combo15 being the new value and the other values being the results from the query.
    If Len(Combo15 & vbNullString) > 0 Then
    DoCmd.RunSQL "Insert into BOMs([FG Stk No],[RM Stk No],[Qty per Lot],[I/P WF]) Values (Combo15.Value,[RM Stk No].Value,[Qty per Lot].Value,[I/P WF].Value);"
    Else
    MsgBox ("You need to add a value")
    End If





    I will try to explain a little more about why I am doing this.
    Table 1 containing RMs with unique 11 digit IDs
    Table 2 containing FGS with different unique 11 digit IDs
    Table 3 links the RMs to the FGs so each FG will be attached to various different RMs
    Sometimes it will be required to copy a group of RMs that are attached to a certain FG
    So the query finds all the RMs the user enters the new FG then each of the rows is added to Table 3 linking the new FG and old RMs.

    I hope this all makes sence.
    Rgds
    Hannah

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    And one record is all this can write. You would have to move to each record of the form in a loop and run the INSERT. How many records?
    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
    Hannah is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    4
    It may be a different amount of records depending which FG number is selected.

  4. #4
    Hannah is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    4
    Quote Originally Posted by Hannah View Post
    It may be a different amount of records depending which FG number is selected.
    So would something like the following work if I could get the RMCount textbox to display the number of records found in the query. ( I haven't yet worked out how to do this so can't test it)

    Amount = RMCount
    Do
    If Len(Combo15 & vbNullString) > 0 Then
    DoCmd.RunSQL "Insert into BOMs([FG Stk No],[RM Stk No],[Qty per Lot],[I/P WF]) Values (Combo15.Value,[RM Stk No].Value,[Qty per Lot].Value,[I/P WF].Value);"
    Else
    MsgBox ("You need to add a value")
    End If
    counter = counter + 1
    Loop Until Couter = Amount +1

  5. #5
    Hannah is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    4
    Hi I eventually got this to work after playing around with my loop.
    Thanks
    Code is shown below.
    Amount = RMCount
    Do
    If Len(Combo15 & vbNullString) > 0 Then
    DoCmd.RunSQL "Insert into BOMs([FG Stk No],[RM Stk No],[Qty per Lot],[I/P WF]) Values (Combo15.Value,[RM Stk No].Value,[Qty per Lot].Value,[I/P WF].Value);"
    Else
    MsgBox ("You need to add a value")
    End If
    Counter = Counter + 1
    Recordset.MoveNext
    Loop Until Counter = Amount

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

Similar Threads

  1. Replies: 1
    Last Post: 06-08-2011, 02:58 AM
  2. Copying tables
    By Bajaz001 in forum Programming
    Replies: 21
    Last Post: 04-11-2011, 03:51 PM
  3. Need Help with copying Data?
    By Sanandreas21 in forum Import/Export Data
    Replies: 1
    Last Post: 03-17-2011, 03:36 PM
  4. Copying only part of data from a text field
    By davedejonge in forum Queries
    Replies: 1
    Last Post: 02-03-2010, 05:18 PM
  5. copying data from subform to mainform
    By wasim_sono in forum Forms
    Replies: 0
    Last Post: 03-10-2006, 04:35 AM

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