Results 1 to 12 of 12
  1. #1
    doci4a is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    20

    Display data from an existing table in a form and save it into another table

    Hi everyone!



    I am fairly new to Access 2007 and I have a few problems with a form.

    I have an existing table filled with Employees and their personal data.
    I would like to have a form with a few fields where I can display some of the personal data from the Employees table plus a few new fields that would be filled in by hand. The displayed information plus the new information should be saved in another table that I have created.

    Now the question is, how do I save all the information from the form into the new table in the corresponding fields (I would like to use a Save button eventually that would create a new record in my table with all the data).

    What I already tried was to 'add existing fields' from my Employees table to the form in order to display my existing information. This also implied their mapping onto the fields in the new table. After doing so, my Employees' data was displayed correctly in the design view but the form went blank in the form view.

    I am not pretty sure if I am going the right way...



    Thank You all for your time

    Cheers

  2. #2
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    you could use a recordset object in VBA,

    Code:
    Dim rst as new adodb.recordset
    
    rst.open "tblMyTable",currentproject.connection, adOpenKeyset, adLockPessimistic
     With rst
            .AddNew
            !field1 = value1
            !field2= Value2
           .........
            !fieldn = valuen
            .Update
            .Close
        End With
    
    set rst = nothing
    but why would you want to duplicate the already existing information into another table?

  3. #3
    doci4a is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    20
    Because the Employees' table is actually a linked table on another location and I don't have the permissions to add/delete/modify or whatsoever in it. I want to have a local copy of certain data from that table combined with some additional data that I want to be able to add myself.

  4. #4
    doci4a is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    20
    And about this piece of code...as far as I understand it is supposed to insert the values entered in the fields into the opened table.
    This should also work if some of the fields are linked to the information in my existing Employees' table without modifying it?

  5. #5
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    the code only modifies the data in the table it inserts into, it will add a new record there, but not change existing ones.

    greetings
    NG

  6. #6
    doci4a is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    20
    Great thx! That was exactly what I need. Any ideas how can I call this from the Save button? I mean...save all those fields at a time?

  7. #7
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    you can write the code in the on click event of the save button

    greetings
    NG

  8. #8
    doci4a is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    20
    I am still doing something wrong...The code I am using is:

    Private Sub Command5_Click()
    Dim rst As New adodb.Recordset
    rst.Open "CERTIFICATES_TABLE", CurrentProject.Connection, adOpenKeyset, adLockPessimistic
    With rst
    .AddNew
    !ASSOCIATE_ID = Value1
    !NAME_FIRSTNAME = Value2
    .Update
    .Close
    End With
    Set rst = Nothing

    End Sub

    It won't write anything in the table...

    Any ideas?

  9. #9
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    value1 and value2 are just examples, you must subsitute these by the real values. If there on the form, I guess it will be something like me.Fieldn,

    where fieldn of course should be the name of the control on the form that contains the value.

    Also check if the adodb library is loaded. you can check this in the VBA window opening the references window by clicking: Tools -> references. make sure the Microsoft ActiveX Data Objects reference is checked.

    greetings
    NG

  10. #10
    doci4a is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    20
    Hello again...I still have problems getting this code going and I think it's because I am still don't use the correct values for the code you provided.

    So for the example:

    !field1 = value1

    field1 corresponds to the name of the field in my new table where I want to save the information

    value1 corresponds to the name of the field in my form I want to get the information from

    Is this correct?

    Thx

  11. #11
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    in this case you shoud use:

    !field1 = me.value1

    me referring to the form you start the code in

    success
    NG

  12. #12
    nabiki is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2012
    Posts
    2
    Hi - this was very helpful for me
    i used
    rst.Open "activity", CurrentProject.Connection, adOpenKeyset, adLockPessimistic
    With rst
    .AddNew
    !code = Me.code
    !Activity = Me.Activity
    .Update
    .Close
    End With
    Set rst = Nothing


    This is good however - if i click the button more than once it makes duplicate entries in my table. how best would you prevent this from happening ?


    Your help would be much appreciated.

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

Similar Threads

  1. Replies: 9
    Last Post: 01-20-2011, 02:22 PM
  2. Replies: 4
    Last Post: 01-05-2011, 07:56 AM
  3. save last record in table through a form
    By ajetrumpet in forum Forms
    Replies: 3
    Last Post: 09-09-2010, 08:53 AM
  4. Replies: 7
    Last Post: 11-22-2009, 02:38 PM
  5. Using VBA create a new table from an existing table
    By KramerJ in forum Programming
    Replies: 0
    Last Post: 03-25-2009, 04:07 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