Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2004
    Location
    London
    Posts
    64

    Unanswered: Update Multiple Records From Access Form

    Afternoon all,

    Ive just switched from 97 to 2003 and while trying to get IT to fix the bugs (no wizards etc) in the installation i have come across an issue that i cant seem to get round.

    I provide a staffing database to the company that my team uses for the production of MI to the business.

    This is basically a staff table with various fields and a couple of input forms for changes to be made.

    The changes are made by directly editing a copy of the master staff table from the form (i use a select query to filter the records, then amend directly by overtyping as necessary which is great for single records). My problem is in amending multiple records.

    I have a form which shows an entire team of people based again on a select query (the user selects the manager name and the form shows all the staff). I want to be able to change the entire team to another manager. Currently i do this by providing combo boxes at the top of the form with validated entries, the user selects the new team and then runs an update process to make the change (this runs an SQL update query to do all of the staff in one go).

    The problem is that when all of one team move to a new manager i end up with effectively 2 teams of staff under one manager. I then need to move the new managers old team to another and so on. Doing it the way i have i dont have any way to filter the records that i want updated, so it moves everyone over. After not realising this at first i find myself with a lot of unused managers and a couple of big teams with about 70 people in them (oops, thankfully its all test data).

    Is there an easier/better way to do this. I am trying to replace an older system i created which used holding tables and lots of updating/appending/deleting.

    I hope this makes sense in some way to someone.

    As always any help given or time taken is greatly appreciated.

    Thanks

    EddiesVoicebox

    Please be aware the following: Experience of Access97 VBA but only just moved to 2003. SQL knowledge is pretty basic as never needed to use in the past, but can muster through and read fairly simple stuff.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    You select the new manager (A) of the current team ... Why can't you select the new manager for the old team that belonged to (A)??? You have to update that team 1st before updating your current team to this new manager ...

    I see a good game of musical chairs here ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What does your update process look like?
    Are you re-assigning a manager to a team, or a team to a manager? Note the subtle difference
    George
    Home | Blog

  4. #4
    Join Date
    Sep 2004
    Location
    London
    Posts
    64
    Thanks for the quick response guys.

    I will try to answer both posts in one.

    The SQL for the update is something i grabbed off the net yesterday:
    Code:
    Dim strSQL1 As String
    strSQL1 = "UPDATE Qry_Select_Team_Details_TEAM_CHANGES SET Qry_Select_Team_Details_TEAM_CHANGES.[Line Manager] = text169;"
    DoCmd.RunSQL strSQL1
    I use this multiple times for different fields (i have manager,manager logon, senior manager, senior manager logon & department to update)

    The problem i see with updating both teams at the same time is that it will probably be a never ending cycle as the new team would need moving and then the team they are replacing would need replacing etc.

    If there is a way that i can have say 10 rows of text boxes in my form and have a way to populate all of these with the results of a standard select query then i could add a tickbox as the criteria for updating which would help a lot, but i dont know how to bring all the records back into the form other than the way i do it now.

    Hope this helps.

    Again thanks for your help

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your UPDATE query above has no WHERE condition, implying that you update all the managers in Qry_Select_Team_Details_TEAM_CHANGES to the value of text169.
    Quote Originally Posted by georgev
    Are you re-assigning a manager to a team, or a team to a manager? Note the subtle difference
    I don't feel my question quite got answered
    George
    Home | Blog

  6. #6
    Join Date
    Sep 2004
    Location
    London
    Posts
    64
    I see your point there. I did try adding a tickbox next to the employee name field in the hope i could tick off the ones i want but every time i ticked one it ticked them all off.

    With regards to your original question that completely forgot to answer.
    Are you re-assigning a manager to a team, or a team to a manager? Note the subtle difference
    The answer originally was that i was updating the individual employees record with the new manager so i guess i was assigning both the manager to a team and the team to the manager.

    However while speaking to the team nerd about this i thought that i may be able to overcome this by assigning each employee to a team (i.e. Team A) then i could tag a manager to team A aswell. This i think should give me the criteria i would need to include the WHERE statement in my update process.

    I think this should cover me i just need to tweak some of the details of my great theory to make it work smoothly.

    Thanks for your help today, much appreciated.

    EddiesVoicebox

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Imagine this scenario:

    EmployeeID | Name | ManagerID
    001 John NULL
    002 James 001
    003 Jeremy 001
    004 Jerome 001
    005 Jamie 001
    006 Jill 001
    007 Jennifer 006
    008 Jack NULL

    Where Jennifer reports to Jill.
    Jill, Jamie, Jerome, Jeremy and Jame all report to John
    John and Jack don't report to anyone.

    If we wanted to re-assign everyone that reports to John so that they report to Jack we would simply change the manager numebrs to 008
    Code:
    UPDATE Employees
    SET ManagerID = '008'
    WHERE ManagerID = '001'
    However, if we simply want to make it so that James (002) now reports to Jack:
    Code:
    UPDATE Employees
    SET ManagerID = '008'
    WHERE EmployeeID = '002'
    Any help?
    I used lots of names beginning with the same letter so that you have to really read through it to get the jist
    George
    Home | Blog

  8. #8
    Join Date
    Sep 2004
    Location
    London
    Posts
    64
    Thanks for that, i think ive found the solution to be able to split out the original team members from the new ones, but that seems like a much easier way of running the update process than the one i had before, ill be using that from now on i think.

    I apreciate your help today. Hopefully one day i can repay the favour.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you have any more troubles, just pootle back and we can see if we can help!
    Quote Originally Posted by eddiesvoicebox
    I apreciate your help today. Hopefully one day i can repay the favour.
    and I'm sure you will!
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •