Making copies of records and updating related tables

[stickers]

Has anyone done this before? If so, help would be appreciated.

I have three 'main' tables, Projects, Sites and Rooms. These are joined by two linking tables to overcome the many-many relationships.

So one project may have many sites and vice versa, and one site may have many rooms and vice versa.

tblSites has default entries, and a user may choose to put more than 1 of one default site within a project:

ProjName SiteName Quantity
Project1 DefaultSite1 3

- this is the linking table tblProjSite

All the data entry, selection of sites etc. is done through forms. At a later stage, users will need to 'specify' the default sites, give them names, change attributes and so on.

What I need to do, on a button click is to check which Site needs to be specified, and in tblSites, copy the record for this site the correct number of times, giving it a new name each time:

DefaultSite1Temp1
DefaultSite1Temp2
DefaultSite1Temp3

Then, for each of these, I need to duplicate the records in the two linking tables that already exist for DefaultSite1.

Basically, anywhere in the Database that DefaultSite1 (or whatever) is mentioned, it needs to be replicated for each of the specific sites that are about to be created. With the new name (which the user will be prompted to change straight away).

Has anyone ever done anything like this????

Cheers in advance for your help.

 

[beetee]

Just a few thoughts:
1) remove the 'quantity' field from the Projects table.
2) add a 'populate' button to your projects form. Let the user specify the number of default sites to add.
3) when the user clicks the populate button, generate new site id's, and add the sites and projects to your site/project linking table. Code below.
4) refresh the site subform (assuming you have one) that is in your project subform. In fact, I would imagine you have a project form, a site sub-form in that; and in the site sub-form you have a rooms sub-form. The same logic used for projects/sites can apply to sites/rooms.
5) This will eliminate the need to later go through and process Project or Site data to add default rooms. If you go the subform route, you will allow your user to see project/site/rooms in a single, user friendly context.

Rough sample Code to populate the site table (untested, you will probably want to make sure it's kosher).

Sub AddRecordsToSiteTable()
Dim DataDB As Database
Set DataDB = DBEngine.OpenDatabase("TheDbName&quot
With DataDB
Dim SourceSiteRst As Recordset
Set SourceSiteRst = .OpenRecordset("Sites&quot
Dim DestSiteRst As Recordset
Set DestSiteRst = .OpenRecordset("Sites&quot

' copy in the records, assuming that the
' default entries are the first three in
' the table. We are additionally assuming
' that SiteID is an autonumber field
SourceSiteRst.Index = "SiteID"
SourceSiteRst.MoveFirst
Dim counter As Integer
For counter = 1 To UserSitesToAdd
DestSiteRst.AddNew
Dim CurField As Field
For Each CurField In SourceSiteRst.Fields
' check help for correct
If (CurField.Attributes And dbAutoIncrField) = 0 Then
DestSiteRst(CurField.Name) = CurField.Value
End If
Next CurField
SourceSiteRst.MoveNext
Next counter
SourceSiteRst.Close
Set SourceSiteRst = Nothing
DestSiteRst.Close
Set DestSiteRst = Nothing
End With
DataDB.Close
Set DataDB = Nothing
End Sub


HTH

 

[beetee]

Of course I left out the code to update the project/site relation table.

You would need something like


DestSiteRst.AddNew
Dim CurField As Field
For Each CurField In SourceSiteRst.Fields
' check help for correct
If (CurField.Attributes And dbAutoIncrField) = 0 Then
DestSiteRst(CurField.Name) = CurField.Value
End If
Next CurField
dim DestSiteID as long
DestSiteID = DestSiteRst("SiteID&quot
' I neglected to invoke the .update, as well
DestSiteRst.Update
ProjectSiteRst.AddNew
ProjectSiteRst("ProjectID&quot = forms("MyForm&quot("ProjectID&quot
ProjectSiteRst("SiteID&quot = DestSiteID
ProjectSiteRst.Update
SourceSiteRst.MoveNext

 

[stickers]

Thank you very much for both replies!! It's food for thought - at the moment I don't have the system of subforms that you suggest. Mostly because I am trying to make this all VERY simple for the users so currently they have a form where they simply select a default site, and click a button and it drops into the project. There is a separate form where they can enter site details and drop those in. But I agree that it would be useful to have it all in the same place. Maybe with tab controls so you only see the Site or Project Name and reference number above the Room or site details. Will have a think!

 

[beetee]

Tabs might be a nice way to go indeed. Good luck!