Results 1 to 12 of 12
  1. #1
    azhar2006's Avatar
    azhar2006 is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    528

    use Loop in swap data from one table to another.

    I'm trying looking to swap data from one table to another.
    But I'm trying to write a function in vba to do so


    Any assistant or an example I should be grateful to you
    For example,
    I want to looking for the name of the employee in the table of the number one and go to match the all the records and after finding that employee replaces its specialization, and so on back and looking for the other, and so on.
    Attached Thumbnails Attached Thumbnails 1.JPG   2.JPG  
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Make a third temp field.
    use update queries...
    write tbl1.colA to temp
    Write tbl2.colB to tbl1.colA
    write tbl1.temp to tbl2.colB

  3. #3
    azhar2006's Avatar
    azhar2006 is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    528
    Thank ranman256
    Sorry, I did not understand
    It may not be a conciliator in bringing the idea.
    What I want is Update Query That Compares Values In Two Fields In Two Tables .
    Thanks again ranman256
    Last edited by azhar2006; 08-27-2016 at 05:00 PM.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    The following SQL will update every single record in Table2. The value from Table1.Specialization will be assigned to Table2.Specialization. I used a JOIN on the ID field.

    Code:
    UPDATE Table1 INNER JOIN Table2 ON Table1.id = Table2.id SET Table2.Specialization = [Table1]![Specialization];
    If you do not want to update every single record in Table2, you will want to use another query to select only the records you want to update. Perhaps an Unmatched Query Wizard will help you find the records in Table2.Specialization that do not match Tabl1.Specialization

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Are you doing alright there azhar? It's getting close to my bedtime here. If you need more help or you would prefer to use DAO, let us know. I will check in again tomorrow.

  6. #6
    azhar2006's Avatar
    azhar2006 is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    528
    Hello ItsMe
    I've doing the above query, he updates the records of each table. I do not want it, what I want to match the employee's name in a table number one, and if the employee is found in Table A who shall then updating.
    I do not understand how to use (DAO)

    Thank you ItsMe
    Attached Files Attached Files

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I created a query that looks for records in Table2 that do not have a matching value in Specialization. So It looks at a record in Table1, determines the name, looks for that name in Table2, determines if Specialization is different in table two for that name.
    Code:
    SELECT Table1.id, Table1.EmployeeName, Table1.Specialization
    FROM Table1 LEFT JOIN Table2 ON Table1.[Specialization] = Table2.[Specialization]
    WHERE (((Table2.Specialization) Is Null));
    This query does not consider that there may be duplicate names in either of the tables.

    I created a form and some VBA. The VBA use DAO to open the above query and loop through the records. If it finds any records, it will update Table2 with the Specialization from Table1. Everything is considering the NAME and not the Primary Key.
    Code:
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strEmployeeName As String
    Dim strSpecialization As String
    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
    '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"
    Attached Files Attached Files

  8. #8
    azhar2006's Avatar
    azhar2006 is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    528
    Thank you so much ItsMe
    Thanks again for helping me
    that's what I aspire to it.
    Acknowledgements
    I have another question.
    Is it possible to show us the MsgBox that the number of records that have been updated?

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    There is always a way. There are two issues you would have to address. One is in case there is an error for some reason. After an error during the UPDATE, you would have to make a correction on the count.

    The other issue would be to create a SELECT statement within the loop that would use the variables strSpecialization and strEmployeeName. This way, you can open a recordset and get a recordcount of that dynamic SELECT statement.

    Another approach would be to get a recordcount from the rs recordset that is declared in the example. However, this may not be reliable in case of an error and I am not sure how it would work in case there are duplicate names and or duplicate Specializations.

    I do not believe it is a reliable and perfect way. However, you can get a simple record count by replacing rs.Movefirst with the following ...
    Code:
    Dim intCounter As Integer
    rs.MoveLast
    rs.MoveFirst
    intCounter = rs.RecordCount
    MsgBox "You are about to update " & intCounter & " records."

  10. #10
    azhar2006's Avatar
    azhar2006 is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    528
    Thank you very much ItsMe I've already works well

  11. #11
    azhar2006's Avatar
    azhar2006 is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    528
    There is one problem I want to address. It is when a certain value is repeated and does not add it

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. Replies: 4
    Last Post: 08-29-2015, 03:51 PM
  2. Replies: 9
    Last Post: 05-08-2015, 02:36 PM
  3. Swap words from a string.
    By tigorin in forum Access
    Replies: 13
    Last Post: 04-07-2014, 02:37 PM
  4. Replies: 3
    Last Post: 03-10-2013, 07:04 AM
  5. Replies: 13
    Last Post: 06-05-2012, 10:46 AM

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