use Loop in swap data from one table to another.

[azhar2006]

There is one problem I want to address. It is when a certain value is repeated and does not add it

Private Sub cmdExecute_Click()


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strEmployeeName As String
Dim strSpecialization As String
Dim intCounter As Integer
Dim strSQL As String

Set db = CurrentDb
Set rs = db.OpenRecordset("qryNamesWithNewSpecialization", dbOpenSnapshot)

'Make sure we have records and then
'make sure we are at the first record
    If rs.RecordCount < 1 Then
        MsgBox "No records require an update"
        Set rs = Nothing
        Set db = Nothing
    Exit Sub
    End If

rs.MoveFirst
rs.MoveLast
rs.MoveFirst
intCounter = rs.RecordCount
MsgBox "You are about to update " & intCounter & " records."
'We need to loop through all of the records
'that our query object found
    While rs.EOF = False
    
        strEmployeeName = rs![EmployeeName]
        strSpecialization = rs![Specialization]
        strSQL = "UPDATE Table2 SET Table2.Specialization = '" & strSpecialization & "' WHERE ((Table2.EmployeeName)='" & strEmployeeName & "')"
        
        db.Execute strSQL, dbFailOnError
        
        rs.MoveNext
    
    Wend

rs.Close
Set rs = Nothing
Set db = Nothing

MsgBox "Complete"

End Sub

 

[theDBguy]

We will probably need a little more context to be able to offer any suggestions. Are you able to post a sample db with test data?

 

[plog]

It is when a certain value is repeated and does not add it

There's no "adding" going on, it's a series of UPDATE queries--it's changing values. I suggest you demonstrate your issue with sample data. Show us how its not working.

With that said, this seems over-engineered. Why not just create an UPDATE query to do the whole shebang in one go instead of an UPDATE query per EmployeName?

With that said, UPDATE queries are a sign of a poor table structure or not understanding how relational databaes are to work. It's unusual to have a process for updating tables with another table's data. Usually you just build a query and reference the data. Why must this data be updated?

 

[azhar2006]

thank you very much for the respond
I will attach a database for you as an example. Table number one transfers the information in the Specialization field to the Specialization field in Table Two. Add a label that is already in the first table and click the button on the form. It will not update or move the word, but if you add a word that does not exist in the second table, the event will work. Greetings to you my friend

 

[LarryE]

After looking at your DB, it appears you wish to keep track of employees which may have one or more specializations assigned to them. I took the liberty of re-designing it so it meets relational database specs. No opening recordsets just to add a record and no duplicate fields in tables. You can add or delete specializations to employees as needed and add or delete employees as needed. You can finish development as required.

 

[azhar2006]

thank you my dear friend . This is not what I want. On the contrary, I want the specialization to be repeated for more than one employee. And this is the problem that it is not acceptable to transfer a particular specialty from the first table to the second table if this specialty is with another employee. for example

 

[azhar2006]

I have a lot of employees what I want for example. I have 20 employees who hold a second-class driver's specialty. A year later, they became first-class drivers. I don't want to look up their names one by one. I just put their names in table number one and run the event is the one who searches for them in the second table full of employees and updates their specialization from the second class to the first degree based on their names in the table number one

 

[arnelgp]

is this what you want.
update the record if exists and add if not exists.

 

[azhar2006]

thank you my dear friend . Indeed, he changed the specialty, but he added the employee's name again, and this is what I don't want. I just want to change the specialty to the same name without repeating the name.

 

[azhar2006]

If you notice the database that I attached at the beginning. The command button performs the required tasks, but if you write a specialty that is mainly found in the second table and for a non-employee, he does not transfer it to another employee>>>>> Specialization (POO) because it is present with the employee (Bar Miller Siemens) so the event does not transfer it to the employee (Ad Michael Miller)

 

[arnelgp]

is this from my db i upload, download it again. this is not what it supposed to do.

 

[azhar2006]

is this what you want.
update the record if exists and add if not exists.

Ha-ha-ha-ha-ha, thank you my friend, now is very good. Thank you again

 

[azhar2006]

My friend arnelgp, is it possible to modify the (VBA) code?

 

[arnelgp]

using Query is much faster than VBA.
but if you insist.

 

[azhar2006]

using Query is much faster than VBA.
but if you insist.

thank you my friend

 

[Gasman]

Croosposted https://www.accessforums.net/showthread.php?t=61656 with 11 posts.

 

[Pat Hartman]

I hate to jump in at this point but people to specialties is a many-to-many relationship and requires THREE tables to implement. An Employees table, a Specialty table and a PeopleSpecialities table. The question seems to be regarding how to find the employees for a given specialty so I'm going to attach a sample that shows how this type of relationship works. Essencially you have a mainform of people with a subform linked to the PeoplesSpecialties table and a mainform of specialties with a subform linked to PeoplesSpecialties. This allows you to view the data from either direction of the relationship. The PeopleSpecialties table is frequently referred to as a junction table since it is used to join two separate tables.

PS, having a separate table for contractors just makes more work and causes difficulties when you need to see all the workers together. Just use a single table and add a flag to identify a contract worker.

 

[azhar2006]

I hate to jump in at this point but people to specialties is a many-to-many relationship and requires THREE tables to implement. An Employees table, a Specialty table and a PeopleSpecialities table. The question seems to be regarding how to find the employees for a given specialty so I'm going to attach a sample that shows how this type of relationship works. Essencially you have a mainform of people with a subform linked to the PeoplesSpecialties table and a mainform of specialties with a subform linked to PeoplesSpecialties. This allows you to view the data from either direction of the relationship. The PeopleSpecialties table is frequently referred to as a junction table since it is used to join two separate tables.

PS, having a separate table for contractors just makes more work and causes difficulties when you need to see all the workers together. Just use a single table and add a flag to identify a contract worker.

Thank you very much, Pat Hartman.
If you read my post from the beginning, you would know that I do not want to sort the specialties of the employees. What I want to do is to change a specific specialty for a group of employees, for example, they number 200 employees, in one click of a button. The total of these 200 employees, I get their names in the updated excel lists. I call it to a table in the database and then with a single button click the event compares the names in table number one and the names in table number two and then the event updates the data for specialization only and for these names only. Note that there may be 1,000 employees in the database, and only two hundred are chosen from them.

 

[Pat Hartman]

I don't think I said anything about sorting. I was describing the correct data structure which will actually allow you to do what you want.

However, updating all PersonSpecialties records for a single specialty doesn't make any sense. Period.

Try actually looking at the sample to help you to understand how to work with a m-m relationship and how to implement it correctly.

 

[azhar2006]

I don't think I said anything about sorting. I was describing the correct data structure which will actually allow you to do what you want.

However, updating all PersonSpecialties records for a single specialty doesn't make any sense. Period.

Try actually looking at the sample to help you to understand how to work with a m-m relationship and how to implement it correctly.

OK Thank you very much, Pat Hartman.