Complex forms with data and data entry from many to many relationship tables

[AmandaC]

I have setup a simple test database before creating a more complex one, and I am having trouble with the form design.

I have a Table (Plasmids) with an auto-valued primary key (Plasmid_ID) and several other fields relating to the plasmid (Plasmid_name, Created_by, Backbone, Length, etc.)

I have another table (Features) that has an auto-value primary key (Feature_ID) and fields relating to the feature (Feature_name, Sequence, Function, Notes, etc.)

I have setup a junction Table (Plasmid_Features) that has a dual primary key of the fields Plasmid_ID and Feature_ID that are both integers.

I have setup the one to many relationships.

I am trying to create a form that has the plasmid information as the main form, and a subform where I can add features (and to check if the feature already exists, by checking the sequence before adding the entry) and have Features table and the Junction table update.

I can not figure out how to do this in base.

[iplaw67]

I might add that using the default embedded hsqldb as the database engine for such a db is probably not the greatest of ideas, depending on whether or not you are going to include sequence data in one of the fields. You would be better off using a “split” hsql database (see other posts on splitting hsql out for how to do that) or else a server backend db engine such as MariaDB/MySQL or Postgresql.

[iplaw67]

Rather than re-inventing the wheel, you might want to look at this project instead (I have only just discovered this, and it looks interesting) :

http://plasmidb.sourceforge.net/intro.phtml

[Ratslinger]

I guess a picture is worth more than a thousand words. To answer your question I have provided a sample Base file
Plasmids.odb This was crudely put together so don’t expect the world. I’ve slightly changed your ‘Link’ table to use names vs ID’s - thought it made more sense than some numbers. On the only form, the “master” table (Plasmids) is at the top. When you select a record, (on opening the first record is selected as default) the “Link” table (just below the Master) will fill with records which are linked to the “Features” table (shown just below the “Link” table). When you select a record in the “Link” table the detail of that record is shown in the table to the right.

Now, without macros, I don’t see adding a NEW feature and automatically updating the “Link” table. However in the “Link” table, fields are listboxes and adding a new record is fairly easy (select from dropdown). The one factor - Since the items in the listbox is a Query of the Features, and if you add a NEW Feature, the Query must be refreshed and thus the button to the right of the “Features” table.

There are many, many, many things which can be done to enhance this sample - sort sequences, duplicates, displayed fields, etc. The objective here was to show the possibilities and the display of one table based upon another. This, hopefully, will set you in the right direction.

[AmandaC]

Thanks very much for the response.

The example was helpful, but I am a little confused why your junction table doesn’t have any relationships with the two tables that it is joining.

What I would like is a form that the Plasmid table fields as the main form, and subform that is all the features associated with that plasmid (through the join table). I want to be able to add new features to the plasmid (automatically updating the junction table) either from existing feature or adding a new one.

[Ratslinger]

As stated, this was quick & dirty. You can add any relationships you want. Not necessary for this form.
The ‘Link’ table grid already displays all of the ‘Features’ associated with the selected ‘Plasmid’ record. This grid is not all of the items in the table - just the ones for the selected ‘Plasmid’ record.
As stated in the original answer, without macro coding, I don’t see a way of ‘automatically’ updating the junction table.

[AramFingal]

I was able to simplify this by using the link table as the main form and adding a subform for each of the other tables. Unfortunately, it becomes unusable as soon as you implement the foreign key constraints (which LibreOffice calls relationships for some reason). It won’t let you edit the subforms until you first enter something into the main form but you can’t save any new data in the main form because it needs to be in the other tables first.