Add records to several related tables in one or two clicks?

[Big Pat]

If you have three related tables, Parent -> Child -> Grandchild, with one to many relationships, how would you code a button to copy a record in the parent table, have it automatically copy records in both the other tables and then paste those as new records in the appropriate tables, preserving the relationship between the new records?

It’ll be easier to explain if you take a look at the sample DB I have attached, which is a very simplified version of what I’m trying to do. In my real database, the first table has approx 20 fields, the second has approx 100 and the third approx another 20, but the overall structure is the same.

My users will not understand the structure of the tables so I can't rely on them using manual copy/paste. Anyway, I'm probably going to deploy the final version in Access runtime.

So when the user is at one "top-level" record, viewing related records in sub-forms, I need a button that does something like:

------------------------------------------------------------------------
Copy this record
...and all the sub records
......and all the sub-sub-records

Create new record
...create new sub-records
......create new sub-sub-records

Paste everything into the right place.

Display the new record, with the related sub-forms and allow user to edit
-------------------------------------------------------------------------
Is this kind of thing possible?

 

[dkinley]

It is possible ... c'mon now, all things are possible

Here is some logic where I would start from ...

1. Copy current record from table (with WHERE statement referencing current records primary key).
2. Update table from 1. with data.
3. Get last assigned primary key.
4. Copy child record from child table (with WHERE statement referencing parent record foreign key with primary key from 1.)
5. Update table from 4. with data and primary key from 3. (stored into the approriate foreign key field)
6. Copy grandchild record from grandchild table (with WHERE statement referencing parent record foreign key with primary key from 1.)
7. Update table from 6. with data and primary key from 3. (stored into the approriate foreign key field)
8. Go to the last record on the form.

You can use DAO, SQL with a variable and probably a reference the current forms primary key.

Not sure if this is optimized or not - but I think that would be the logical steps involved in the execution methodology.

HTH,
-dK

 

[Big Pat]

Hi dkinley

Your methodology looks right to me. In fact I've spent much of this evening working along these very lines, but doing it via queries (constructed visually as I know only a little VBA). My thinking is that if can get three append queries to add the right data, then I can have a macro run them and use a command button to execute the macro.

I'm with you up to step 4 and I have queries that get me this far, but at step 5 I've run into a problem with getting the records to append in the right sequence. For example, 'HOUSE' has three 'ROOMS', Kitchen, Bathroom, Living room in that order. I have two queries to address this step:
First query lists the three rooms in order of their RoomID number and uses the Max() function to get HouseID of the record created in tblHouses (your step 2/3 I think). This works fine, but it's only a select query.
Second query is the append. It takes the HouseID and the RoomDescription from the first query and appends them **BUT** in the order kitchen, bathroom, living room i.e alphabetically.

For this to work, it's crucial that the new records are appended in the same order as the originals (it's to do with an inspection "route" round an almost identical property.)

The only way I can see to get the records in the right order is to use the original primary key, but of course I can't append that due to key violations. I've tried leaving the PK in the second query, making it a 'select', and then using a THIRD query as the append, but it seems to just add a meaningless step. Is there another way to sort the append query without actually trying to append the key?

I'm stumped at this stage. I really appreciate your advice so far. It confirms to me that I probably have the logic right, just lack the necessary skills to achieve these steps. I'm sure visual queries and a macro are an appalling way to go about this but if could just get the steps right, I would then look at translating them into VBA. I've looked at some other threads on here that deal with things like Current.DB.Execute "INSERT INTO..." but as you can see I'm a way off that just yet.

Grateful for any further pointers you (or anyone else) can give me.

Thanks again,

Pat.

 

[vbaInet]

It’ll be easier to explain if you take a look at the sample DB I have attached, which is a very simplified version of what I’m trying to do. In my real database, the first table has approx 20 fields, the second has approx 100 and the third approx another 20, but the overall structure is the same.

This strikes me to be that your structure is in need of serious normalization.

 

[Big Pat]

This strikes me to be that your structure is in need of serious normalization.

Oops! Typo in my original post. Second table has about 10 fields not 100. First table deals with individual properties, 5 address fields, some fields describing type of property, owner, various other info that maps 1-to-1 with the property, so no issues there.
Second table deals with parts of the property, i.e. rooms, areas, internal/external etc. measurements, floor level, aspect, etc. Each of these is specific to the individual room.
Third table deals with the contents of these rooms, description, condition, location, cost, etc.

The whole process seemed to lend itself clearly to a system of three core tables (a couple of others hold reference values, user data etc.) and overall I'm pretty happy with the way it "flows". I'm sure it could do with more normalisation and I may look at that, but if I could just get this copy/paste thing working properly, I'd be very happy and I'd certainly welcome your input.

Thank you.

Pat.

 

[dkinley]

Okay ... first off, I think your database is normalized enough. No sense in breaking out the street names and all of that - after all, computational power is 35x what it used to be and disk space is pennies on the megabyte. Besides all of that, I am in a good mood and had some free time today so just went ahead and whipped something up.

In looking at your db and thinking about your vision, I think I discovered a flaw. As your house list grows and grows and you want to go and add another house ... just how will you know which house to copy? Or how about the first house that has an attached apartment - how do you get the additional rooms and items added on a copy? Unless you intend on using combo boxes, how can you ensure data integrity is enforced? The last question is what if you wanted to run a report on how many of a specific type of room was inspected or whatevered in the past year? Library vs libary will return erroneous data.

So I added two more tables .. room defaults and item defaults. You can go and add all the potential rooms and potential defaults for this room (they are linked with pk-fk). And I added a sort order field for these tables so you can set your preference for whatever you like for the ordering. If you want a GUI for this aspect - well, you will have to create that for yourself, there are plenty of demos out there for you to 'stare and compare' to make one up.

I also modified your startup screen - I left yours but mine is called "fListing". From here, you can double-click the house name to go its record. I thought it cleaner than what you presented but you don't have to use - just an idea.

I also modified the rest of your forms. I wasn't sure what you were thinking on the subject of presentation but went ahead and did one for you. I think you might like the multi-subform action relating the rooms to items display. IMO in simplifies for the user experience ....

Now, I've added a button to the house form to do what you want. What it does is look at the default tables and inputs all of that information into your tables all nice and neat. I used recordsets (DAO) with a bit of SQL to make it work (it might could use with a bit of optimization but left that for when your skills get honed). It could probably be done with pure update queries but I am not any good at those or with a host of other methods. But, there are plenty of documents out there to assist you in interpretation. I also documented the code so you can get a grasp on what is going on to get you moving ....

What I didn't do was hide the button if the user is just viewing a record - will leave it up to you to hide it for a new record and apply all the cosmetics.

Then I got to thinking - what if the default stuff is like a gabillion rooms/items? I would probably try to do something like a list box to allow the user to select. I didn't do this for you, but I did a demo the other day and it is on this thread. That way, the user can select the rooms (and/or items) they want and click go to have the datasets created. You can take the ideas from the listbox demo and merge them with this demo to do that.

I did not put in a roll-back method - this is more of a batch processing type of deal (to commit all or to cancel) - you can do your research and add some code to this for that. As a shortcut, I modified the relationships to cascade deletes so if there is an accidental commitment to create it all, deleting the house will remove all rooms and items automatically.

Anyhow, there is enough here to keep your brain stymied for a bit - I hope it helps.

-dK

 

[dkinley]

Now .. I did say it was possible ...

For clarification - looking at what I proposed ....

1. Copy current record from table (with WHERE statement referencing current records primary key).
2. Update table from 1. with data.
3. Get last assigned primary key.
4. Copy child record from child table (with WHERE statement referencing parent record foreign key with primary key from 1.)
5. Update table from 4. with data and primary key from 3. (stored into the approriate foreign key field)
6. Copy grandchild record from grandchild table (with WHERE statement referencing parent record foreign key with primary key from 1.)
7. Update table from 6. with data and primary key from 3. (stored into the approriate foreign key field)
8. Go to the last record on the form.

What I did essentially does this except referencing the default tables. If you want it to copy records - the structure is there, you will just need to modify the code to look at the room/item tables for the house you want to copy.

The method I used was by nesting the recordsets to execute this order of action in fewer lines through iteration instead of sequential execution. It follows the structure I laid out but by nesting and looping I could copy/insert all of the grandchild records while in the midst of copy/inserting all of the child records. This allowed me to use the current RoomID of the child record I was in to apply it to the grandchild records.

Think of it like at step 7 ... is there more to do? Yes, goto 6, No ... is there more child records? Yes, goto 1, No ... finished.

Hope the further explanation helps you decode ...

-dK

 

[vbaInet]

Would it be easier if you:

1. Add an extra Yes/No column to each table that you would like a record copied from.
2. Clicking on the "Clone" button set all the recordsto NO before UPDATING just the current record to Yes.
3. On every form (subform included), you create a function that will find the YES and paste the values into the respective controls.

?

 

[Big Pat]

Wow...I mean...WOW! I had no idea someone was going to go to this much trouble. I've had a look at the updated db and in particular the code behind the "Set Up House Default Rooms and Items" button and your documentation is great. I can follow the steps you've taken, even if it will take me a long time to understand the intricacies of things like "rsRooms.Fields("fkHouseID") = Me.txtpkHouseID". I can see what you're doing but I'd never have worked out the syntax as I have a very poor grasp of this stuff!

BUT (you could hear the "but" coming, right?) default rooms and items aren't really the way I need to go with this. Maybe I should have explained a bit more about what we're doing here. We have properties on our books, which are rented out by their owners. Our staff take detailed inventories - hence the 'Rooms' and the 'Items' - when the property is first rented out to a new tenant, at least once mid-term, and finally when the rental ends.

Properties can range from unfurnished studio flats on the wrong side of the tracks, to larger country residences. Far more of the former to be honest, but we live in hope and build our database accordingly! So there isn't really a "default" in the sense I think you have assumed. Instead we tend to get "clusters". A certain owner may buy three or four apartments in a new block and bulk-furnish them almost identically. So apartment 1 is to be rented out and our guy goes and takes his detailed inventory and inputs it. This will form the basis for a printed report to be signed off by all parties and also a slightly differently formatted report which will be used during the mid-term and final inspections.

Weeks or months later, apartment 2 is being rented out. We know it's nearly identical to apartment 1, so we want to display THAT on-screen, then use the "copy/paste" functionality to create apartment 2. It's probably going to be a BIT different, so we need to quickly run through the rooms and items and edit some of them. But it sure beats starting the whole process from scratch.

You're not really that far off with your default idea to be honest and your code may not need a great deal of alteration. You're adding a default/standard template, whereas I want to add a copy of a specific previous property. If I change the SELECT/FROM/WHERE statement, I think I may not be a million miles off.

On some of your other points:
How will I know which house to copy? Local knowledge basically. As explained, our guy will know which other properties are similar and I'm going to provide a search box on the opening screen so he can quickly find an existing house e.g. by searching on postcode.
House with attached apartment? Again, he'll use just search for a house that's closest to the new one and copy that. Sometimes he'll get closer than others, but we can live with that.
Run a report of a sepcific type of room? Never gonna happen, frankly.
Multi sub-form layout. I like that idea. Need to study that box that keeps them sync'd but if I can work it out, I may well go with it.
Sort order: I like that too and I'll see if I can get it working by applying it "somehow" to the source data.
Nested recordsets: Very elegant. I really like that.

Once again, thank you SO much for your help on this. Really, it's above and beyond the call of duty. And if this isn't pushing it... do feel free to have another good mood and some more free time!

Pat.

 

[Big Pat]

vbaInet, I think I follow your logic and that sounds like it would work, but step 3 is where I'd come a cropper. "Paste the values into the respective controls" is what I need to do, but I don't really know how to that.

From the code inthe db dkinley posted, it seems to be (in very general terms) set object = recordset, but my VBA is nowhere near that level.

I feel like I've just stared 1st year French and I'm trying to translate Channel 4 News!

 

[vbaInet]

From the code inthe db dkinley posted, it seems to be (in very general terms) set object = recordset, but my VBA is nowhere near that level.

On the subform, the function would be:

Public Function SetChildClone()

Dim db as Dao.database, rst as dao.database

set db = currentdb
set rst = db.openrecordset("SELECT * FROM tblMyAccess WHERE [CloneField] = Yes")

With rst
    If .bof = true then
        Control1 = Nz(![Field1], "")
        Control2 = Nz(![Field2], "")
        .... So on and so forth
    end if
End With

set rst = nothing
set db = nothing

End Function

You create a similar code for the main form, so when you click the "New Record with Clone" button (from the main form), you do something like this:

Docmd.runcommand accmdrecordsgotonew
Call SetMainClone
docmd.runcommand accmdsaverecord
Me.Controls("Child1").Form.Setfocus
Me.Controls("Child1").Form.SetChildClone
docmd.runcommand accmdsaverecord
Me.Controls("Child1").Form.Controls("GrandChild1").Form.setfocus
Me.Controls("Child1").Form.Controls("GrandChild1").Form.SetGrandChildClone
docmd.runcommand accmdsaverecord
..... and so on

You would also need to cancel setting the child and grandchild IF main has no cloned record.

Create two buttons for creating a new record: "New Record", "New Record with Clone". Or have a "New Record" and a "Paste Clone" button. The Paste Clone should be enabled IF a cloned record on the MAIN form exists. Perform this check on the AFTER_INSERT event of the main form.

 

[dkinley]

Well ... hope there was something there for ya - that's what ya get when a C coder starts thinking about solving problems. Let's just call it a tutorial then ....

Going above the call? Nah, I just like the logic and when I get time to work it out. But I do have to give much props to the optimization ... wait, scratch that - total revamping of the method.

Very nice implementation vbaInet! I am digging this method much more than mine.

-dK

 

[vbaInet]

dk: That was what I could think of at the time. Don't have time to implement it unfortunately