Automatically transfer data

[Shane]

I have two tables Table 1 has 3 fields (emp_ID,Emp_Name, Emp_Address) Table 2 has several fields, out of which first four fields are (GroupNum, Empname, Serial_Num , DOJ). I need a VBA code that will move the value of Second column from Table 1 automatically to second table's second field.

On basis of this in existing table 2, first two fields gets autopopulated. Like say now we have 20 employees record in Table 1 then all the employees records get automatically transfered to table 2. Basis this all these 20 record get the same group number like "Grp001", Serial number starts from 1 for first record to 20th record.

Now let say after some time user gets 30 more records in table 1 now before moving these 30 records into table 2 . It will automatically check for group number, last time grp number was "GRP001" now for these 30 records it shud be "GRP002" and at the same time it check for the value of serial number in table 2 and again for these records it starts the value fo serial number from 1.

I need a VBA code name of first table is "Employee" and the second table is "Employee Group". Name of database is Emp_Mangmnt.mdb"

Any help is appreciated.

Regards,
Shane

[JimmyTheHand]

Can you post this Emp_Mangmnt.mdb to experiment with?

BTW, I think emp_ID should be the one transferred to Table 2 instead of Empname.

Jimmy

[Shane]

Hi!

Attaching the sample database for your consideration. I want Emp_Name to get transferred into Emp_group table not the Emp_Id.

Thanks in Advance.

Regards,
Shane

[OBP]

Shane, I agree with Jimmy, the Emp_ID should be transferred to the second table, in fact the second table is set up to recieve it.
The golden rule of Relational databases is that you do not duplicate data like Names etc only their Reference, so the Emp_Name should not be in the second table because you do not need it if the Emp_ID is in there.

If you only want the Emp_Name transferred the Emp_Name field should be set to Indexed with No Duplicates. Otherwise you will have to manually control not getting duplicates in the second table.
You also do not necessarily need VBA to do the actual transfer as it can be done with Queries.

[Shane]

Ok.. As per ur suggestion I will make the necessary changes . Now if I would like to get the Emp_ID automatically and then for rest of the below issue .... I need the VBA code..

On basis of this in existing table 2, first two fields gets autopopulated. Like say now we have 20 employees record in Table 1 then all the employees records get automatically transfered to table 2. Basis this all these 20 record get the same group number like "Grp001", Serial number starts from 1 for first record to 20th record.

Now let say after some time user gets 30 more records in table 1 now before moving these 30 records into table 2 . It will automatically check for group number, last time grp number was "GRP001" now for these 30 records it shud be "GRP002" and at the same time it check for the value of serial number in table 2 and again for these records it starts the value fo serial number from 1.

Can you help me with it.

Thanks and Regards,

[JimmyTheHand]

This piece of code does what I think you want.
It automatically increases the greatest existing group number by one. It also checks which records are already transferred, and transfers only new records.

[vba]
Sub Transfer
Dim S As String, NextGrp As String, MGIx As Long
Dim Rst As DAO.Recordset, rstEmp As DAO.Recordset, rstGrp As DAO.Recordset

'Determining next Group ID
'This algorithm works as long as the greatest Group index is smaller than 999
S = "SELECT Max(Emp_Group_No) AS MG FROM Emp_Grp"
Set Rst = CurrentDb.OpenRecordset(S)
NextGrp = "Grp" & Mid(CStr(Val(Mid(Rst!MG, 4)) + 1001), 2)
Rst.Close

'Deternmining new records in table Employee, updating table Emp_Grp
S = "SELECT Emp_ID FROM Employee WHERE Emp_ID NOT IN (SELECT Emp_ID FROM Emp_Grp);"
Set rstEmp = CurrentDb.OpenRecordset(S)

Set rstGrp = CurrentDb.OpenRecordset("SELECT * FROM Emp_Grp;")
While Not rstEmp.EOF
rstGrp.AddNew
rstGrp!Emp_Group_No = NextGrp
rstGrp!Emp_Id = rstEmp!Emp_Id
rstGrp.Update
rstEmp.MoveNext
Wend
rstEmp.Close
rstGrp.Close
End Sub[/vba]

[Shane]

Thanks a lot .

That did the trick.

Regards,
Shane