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

[doci4a]

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

[NoellaG]

Hi,

you could use a recordset object in VBA,

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?

[doci4a]

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.

[doci4a]

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?

[NoellaG]

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

[doci4a]

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?

[NoellaG]

Hi,

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

greetings
NG

[doci4a]

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?

[NoellaG]

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

[doci4a]

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

[NoellaG]

in this case you shoud use:

!field1 = me.value1

me referring to the form you start the code in

success
NG

[nabiki]

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.