use Loop in swap data from one table to another. (2 Viewers)

azhar2006

Registered User.
Local time
Today, 05:25
Joined
Feb 8, 2012
Messages
202
There is one problem I want to address. It is when a certain value is repeated and does not add it
Code:
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

I’m here to help
Staff member
Local time
Today, 05:25
Joined
Oct 29, 2018
Messages
21,357
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

Banishment Pending
Local time
Today, 07:25
Joined
May 11, 2011
Messages
11,611
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

Registered User.
Local time
Today, 05:25
Joined
Feb 8, 2012
Messages
202
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
 

Attachments

  • New Microsoft Access Database.zip
    39.9 KB · Views: 448

LarryE

Active member
Local time
Today, 05:25
Joined
Aug 18, 2021
Messages
562
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.
 

Attachments

  • New Microsoft Access Database.zip
    79.6 KB · Views: 304

azhar2006

Registered User.
Local time
Today, 05:25
Joined
Feb 8, 2012
Messages
202
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
 

Attachments

  • 1.PNG
    1.PNG
    19 KB · Views: 417
  • 2.PNG
    2.PNG
    18.4 KB · Views: 449

azhar2006

Registered User.
Local time
Today, 05:25
Joined
Feb 8, 2012
Messages
202
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

..forever waiting... waiting for jellybean!
Local time
Today, 20:25
Joined
May 7, 2009
Messages
19,169
is this what you want.
update the record if exists and add if not exists.
 

Attachments

  • New Microsoft Access Database.zip
    24.7 KB · Views: 303

azhar2006

Registered User.
Local time
Today, 05:25
Joined
Feb 8, 2012
Messages
202
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.
 

Attachments

  • 1.PNG
    1.PNG
    11.1 KB · Views: 364

azhar2006

Registered User.
Local time
Today, 05:25
Joined
Feb 8, 2012
Messages
202
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)
 

Attachments

  • 1 (1).PNG
    1 (1).PNG
    19 KB · Views: 412
  • 2.PNG
    2.PNG
    18.4 KB · Views: 403

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:25
Joined
May 7, 2009
Messages
19,169
is this from my db i upload, download it again. this is not what it supposed to do.
 

azhar2006

Registered User.
Local time
Today, 05:25
Joined
Feb 8, 2012
Messages
202
My friend arnelgp, is it possible to modify the (VBA) code?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:25
Joined
May 7, 2009
Messages
19,169
using Query is much faster than VBA.
but if you insist.
 

Attachments

  • New Microsoft Access Database.zip
    33.5 KB · Views: 453

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:25
Joined
Feb 19, 2002
Messages
42,970
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.
 

Attachments

  • ManyToMany20210414.zip
    1.5 MB · Views: 384

azhar2006

Registered User.
Local time
Today, 05:25
Joined
Feb 8, 2012
Messages
202
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

Super Moderator
Staff member
Local time
Today, 08:25
Joined
Feb 19, 2002
Messages
42,970
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

Registered User.
Local time
Today, 05:25
Joined
Feb 8, 2012
Messages
202
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.
:)(y)🌺🌷OK Thank you very much, Pat Hartman.
 

Users who are viewing this thread

Top Bottom