Using a single form to update multiple linked tables

Question

Hello, all. I am in the process of setting up a database for work to keep track of engineering components that my group designs. I am very new to access and I am getting stuck on what I feel must be very basic problems (I'm very comfortable programming in a variety of languages, but Access is just breaking by brain!)

I have a main table - tblArtifacts - that contains information about the things that we design (we generically refer to each one as an "artifact"). Each artifact has unique number, a name, a status, a creator, etc. I have another table - tblArtifactDescriptions

• that has a one-to-one relationship with tblArtifacts. Each record in tblArtifactDescriptions contains a long text field for a detailed description of each artifact in tblArtifacts*. The records in tblArtifactDescriptions also a field for the unique artifact number that was used in tblArtifacts.

The problem is pretty simple: I want a single form for adding artifacts to my database, but this one form needs to add a record to both tblArtifacts and tblArtifactDescriptions, both of which need to use the same ArtifactNumber (not surprisingly I use ArtifactNumber as the foreign key in tblArtifactDescriptions). How do I get Access to do this? Is there a way that I can do this without resorting to VBA? I've included the table relationships output for my database with the two tables I am talking about here circled in red to make it clearer what I am talking about.

Thanks,

-Chris

*I was told that there could be some issues with table corruption, and that is why I have broken out the descriptions into their own table. Whether or not this is necessary is not something I want to get into here; there are other cases where I will need this one-to-one relationship in my database, so I need to solve the problem regardless.

Answer 1

I think part of the issue is that all of the artifact child tables should be linked to the ID field of tblArtifacts not on ArtifactNumber. 

In addition, I believe you are working to hard. Simply create a subform based on the tblArtifactDescriptions and drop it on the main form for tblArtifacts. You would set the Link Master/Child properties of the subform control to the Primary and Foreign key fields. There is no coding required. If you have the relationships set correctly as you add a record to the subform, it will automatically inherit the primary key value from its parent form.

Answer 2

Having the ArtifactNumber as the primary key should resolve the issues I had with your structure. 

I was also a procedural coder but it was decades ago. I still like programming VBA but enjoy the functionality easily afforded by link master/child, aggregate functions in reports, and many others.

Answer 3

Thanks for the reply, dhookom,I'll give what you suggest a try.

WRT the ID field vs ArtifactNumber field, I have only included the default ID field because I was advised to keep it even if I don't need it. I need control over the ArtifactNumber, so I didn't want to use an Auto number field. I have ArtifactNumber set to "Indexed (No Duplicates)", so I suppose I could just eliminate the ID fields from tables where I am using ArtifactNumber as the defacto primary key (tblArtifacts, tblArtifactDescriptions, & tblDeprecationDetails). Is there any reason to keep the ID fields those tables?

Re working too hard - I am almost certain that you are correct! My background is in robotics/engineering, and I tend to approach these sorts of information problems as if I were writing procedural code to solve them, but that isn't the "database way" to go about it. There's still a lot for me to learn!

Thanks again,

-Chris

Answer 4

I have one more (hopefully) simple question that I'll post here because it follows from my first. I have successfully set up a form with a subform as you suggested, and it works great. However, I will be deploying the front end to this database to several people, and I want to prevent people from being able to edit existing records with this particular form (I've called it "Create_New_Artifact", so I want only that behaviour).

Looking around online, it seemed like setting AllowEdits on the form to "no" would solve this problem. However, if I set it to "no", the form doesn't let me enter any data into the subform when I am creating a new record. On the main form I am able to create a new record, but I can't click on the ArtifactDescription field in the subform to add any text. It seems as though setting AllowEdits to "no" on the main form prevents edits on existing records in tblArtifacts (good!) but also prevents the creation of new records in tblArtifactDescriptions (bad!).

Thanks again,

-Chris

Answer 5

Hi Chris,

That is the standard behavior. Must of us write a little code to lock/disable controls on the main form but still allow editing of the subform records.

If you have more questions on this I suggest you create a new thread with a title/subject like: "Lock main form but allow editing of subform".