Results 1 to 5 of 5
  1. #1
    MattyT is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    3

    Copy data from Tables in VBA

    Hello all.

    I am fairly inepxerienced with access so this question may seem dumb.


    basically, i am trying to copy a certain record from one table to another.

    So. I have a Form which allows users to select values from list boxes. These values are then added to the movements table using the INSERT INTO SQL command. Easy to do.

    However, once the code for this has been run i then want the program to copy the phone numbers for these people from Tabe 2 based on what first name and second name I have entered into Table 1.
    The above is an example of what I am trying to do as It would take me ages to explain it all..

    However, I also want the code to add a few more fields which are automatically picked up from another table (say oreBlocks) which adds additional data into the movements table based on some of the data the user has selected.

    I hope that makes sense?

    Can anyone help with this?




    Below is the code I have used for the Command Button:-

    ----CODE ----

    Private Sub ID_Add_Data_Click()

    Dim Recdate As Date
    Dim Rectime As Date
    Dim RecShift As String
    Dim RecFlitch As Single
    Dim RecBlock As String
    Dim RecDest As String
    Dim Rectruck As String
    Dim RecExcav As String
    Dim RecSpotter As String
    Dim Recaugc As Double

    'Gather the data into variables
    Recdate = ID_Date.Value
    Rectime = ID_time.Value
    RecShift = ID_shift.Value
    RecFlitch = ID_Flitchs.Value
    RecBlock = ID_oreblock.Value
    RecDest = ID_destination.Value
    Rectruck = ID_Truck.Value
    RecExcav = ID_Excavators.Value
    RecSpotter = ID_Spotter.Value



    'Place the Variables into the Movements Table using the SQL INSERT INTO command

    Dim strSQLADDY As String

    strSQLADDY = "INSERT INTO [Movements]( [Date_m], [Time], [Shift], [Flitch], [OreBlock], [Destination], [TruckNum], [Spotter], [Excavator] )" & _
    "VALUES ('" & ID_Date & "','" & Rectime & "','" & ID_shift & "'," & RecFlitch & ",'" & RecBlock & "','" & RecDest & "','" & Rectruck & "','" & RecSpotter & "','" & RecExcav & "')"

    DoCmd.RunSQL strSQLADDY


    End Sub

  2. #2
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,603
    Quote Originally Posted by MattyT View Post
    Hello all.

    I am fairly inepxerienced with access so this question may seem dumb.
    basically, i am trying to copy a certain record from one table to another.
    <snip>
    Ummm, it soulds like you table structure is not normalized. You shouldn't have to copy data from one table to another. You don't want 2 (or more) tables to have the same data.

    Here is a tutorial on normalization:

    http://www.accessmvp.com/Strive4Peace/


    Or maybe you would explain more about your table structure/relationships and what you are trying to do.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  3. #3
    MattyT is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    3
    Thanks for reply, my description was not very good.

    Basically I work on a mine site.
    What we do is create boxes around our ore based on gold grade, sulphur percentage and carbon. These are deisnged using 3D mine packages. The data for each block, i.e tonnes, Au grade etc are then put into a Table called GCOB_TABLE.

    We are then using another Table called Movements which record each truck load of ore that comes from the OREBLOCKS. Basically, what I am trying to do is get the truck data into the Movments Table which the VBA code does by taking the selected values from the list boxes on the Form. Then once the field data has been added to the Movements Table, additional data such as gold grade, Sulphur percentage and Carbon will also be added to each movement record in the Movements Table.

    For example, say we have an OREBLOCK called OB001 which containts 1000tonnes and 3grammes per tonne of Gold. During a 24 hour period, a total of 5 trucks were loaded with ore from that OREBLOCk. So 5 records are added to the Movement table which show what time the truck was loaded from which oreblock. Once that data has been entered I am looking at some code to look up that perticular OREBLOCk in the GCOB_TABLE to find the corresponding Gold grade etc and copy that data to the each record in the Movements Field.
    That is the part I am having issues with.

    However, I understand what you are saying, perhaps I don't need to copy the data again as its just taking up additional disk space. So if I need to know how much has been mined over the previous 24hour period, I can just create a Query which takes all the movements in a certain date range and then add up the truck count i.e how many trucks were filled and times that by a standard tonnage (thats what we do here, we say each truck carries 100tonnes). Then I could crossreference the Oreblock mined to the GCOB_TABLE and get the Gold Grade into the Query and create a report from that Query..

    Hummm.. Does that make sense?

  4. #4
    MattyT is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    3
    Its ok.. I have figured it out.. I was trying to be to OTT and not think about having a good database structure.. Thanks for the reply though, it pointed me in the right direction..

    Solved..

  5. #5
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,603
    However, I understand what you are saying, perhaps I don't need to copy the data again as its just taking up additional disk space. So if I need to know how much has been mined over the previous 24hour period, I can just create a Query which takes all the movements in a certain date range and then add up the truck count i.e how many trucks were filled and times that by a standard tonnage (thats what we do here, we say each truck carries 100tonnes). Then I could crossreference the Oreblock mined to the GCOB_TABLE and get the Gold Grade into the Query and create a report from that Query..
    Does the ".... to the GCOB_TABLE and get the Gold Grade...." ever change? Will/do you ever have to pull historical data for reports? If so, you might have to store "some" of the data from the GCOB_TABLE or have a FK to get the correct Gold Grade from the table. Or is there a date range the GCOB_TABLE?

    (Just musing...... in my database, I have to think of every possible thing that could happen...)


    Hummm.. Does that make sense?


    Yes, you have pierced the void!!!
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. String to Copy a Field of Data to Another Table
    By aquarius in forum Programming
    Replies: 1
    Last Post: 09-17-2010, 09:02 AM
  2. Linked tables and copy
    By TimG in forum Access
    Replies: 2
    Last Post: 10-08-2009, 07:54 AM
  3. copy data from text file into table in access
    By miziri in forum Programming
    Replies: 3
    Last Post: 08-12-2009, 03:02 PM
  4. COPY DATA and TRIM
    By Ed H in forum Access
    Replies: 1
    Last Post: 01-01-2009, 07:01 AM
  5. Replies: 0
    Last Post: 09-21-2008, 11:28 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