Help with some SQL Code.

[mych]

Hi,

I have a database that is used to control projects. A Service Planner would enter a new project and choose from a list of teams which team would be required to provide the project. After selecting the teams the app creates a checklist for the project that list each team required and the teams requirements and deliverables. From there on the Service Planner can add updates as each requirement or deliverable is worked on highliting any issues and any software or hardware requirements.

The Checklist is stored in a table caled TblChecklist. As each project is created the following code populated the table. The teams required are concactenated into a single field with ; separating them. When I'm ready to use this information I read this into an array TeamArray()

[green]'******** Using SQL Queries To Append Checklist Entries As Requested On Form *********[/green]

DoCmd.SetWarnings False
Dim SearchTeamNo As Integer

[green]'I'm having problems populating the ProjectID field in TblChecklist so a way around 
'this is to find out how many record in the table at present. Once I populate the table with 
'new data I can then go to record Y+1 and populate ProjectID to the last new record.[/green] 
Y = DCount("*", "TblChecklist")
 
[green]'Set up a loop that will start with 1 to c where c is the total number of teams needed for this project ( c was derived by counting the number of ; +1 in TblTeamsRqd.TeamsRequired)[/green]  

For X = 1 To c 
[green]'Next a bit of SQL that will insert into TblChecklist.RDNo any RDNo it finds in TblRDs where the TeamRefNo = TeamArray(X) and that its Redundant Flag is False. 
'What I also needed was the ProjectID field in TblChecklist to equal a Global Variable (GlbProjID) which holds the current projects ID 
'but couldn't work that out so resorted to the extra code below.[/green]
       GlbSQLtxt = "INSERT INTO TblChecklist ([RDNo]) "
       GlbSQLtxt = GlbSQLtxt & "SELECT RDNo "
       GlbSQLtxt = GlbSQLtxt & "FROM TblRDs "
       GlbSQLtxt = GlbSQLtxt & "WHERE  (((TblRDs.TeamRefNo)= " & TeamArray(X) & ") AND ((TblRDs.Redundant)=False));"

       DoCmd.RunSQL GlbSQLtxt

       Next X
       Z = DCount("*", "TblChecklist")
[green]'Do a recount to see how many new rows were added Z-Y
'Next bit of code goes though the TblChecklist and puts the GlbProjID in the newly added rows. [/green]

Set MyDB = CurrentDbSet 
myset = MyDB.OpenRecordset("TblChecklist", DB_OPEN_DYNASET)    
     myset.MoveFirst
     For X = 1 To Y
     myset.MoveNext
     Next X
     For X = Y + 1 To Z
     myset.Edit
     myset("ProjectID") = GlbProjID
     myset.Update
     myset.MoveNext
     Next X

     myset.Close

DoCmd.SetWarnings True

The code is ugly - I've used a sledgehammer to crack a nut - but I couldn't find an easier way to populate the ReqNo and the ProjectId

Although ugly the code works. Even if a new team is added I can use the SQL to add the RDnos for any team added.

If I need to remove a team I just select and delete any rows for a particular project where the TeamNo = the team I need to remove.

A new senario has to be acounted for. Lets say a some teams have some new Requirements or Deliverables added. I need to be able to run a sql command that will do the same as the code above. i.e. loop through each team required for a particular project, select from TblRDs any RDNo for a particular team but only add a row in TblChecklist if the RDNo does not exist in TblChecklist.

I think I need to create a SQL Minus checking what RDNo exist in TblChecklist for a particulat Project and particular team and compare it to the RDNo for the same team in TblRDs to find the new RDNo and then insert them into TblChecklist.

I may need to use a temp table!

As you can see it all a bit over my head and I could do with some help.

Any help appreciated.