Forms, Subforms, and Many-to-Many Junction Tables, Oh My!

[Mod]

I've learned alot from everyone here, and I really appreciate it. So many of my problems and frustrations are due to a lack of experience with Access, which everyone here has in abundance.you all are the coolest.

Today, besides almost taking a hammer to my workstation of some check boxes, I've been struggling to design a good interface for populating a many-to-many junction table. I'll include a little bit of background, which I've tended to leave out in the past.

One side of the relationship has a specimen table, populated with data on various insects. The other side is a table of genes. Each specimen has many genes, and each gene belongs to many specimen. The main purpose of this relationship is not necessarily to show what genes are in each specimen, but rather which ones have been extracted and sequenced from a specimen.

For the sake of simplicity, assume that both specimen and gene tables contain only a primary key. my junction table would then have three columns, a primary key and two foriegn keys.

Each entry in the junction table will show that the specimen has had that gene extracted and sequenced, so is finished.

I've planned the data entry for the user in this manner: There is a form for browsing the species table. I've placed in it a continuous sub form whose record source is the gene table. The controls on the subform are one text box to display the name of the gene, and a check box indicating if the gene has been extracted from the current record specimen on the main form.

The reason the subform is based on the gene table is so that all of the genes in the database will be displayed. So, in order to make this subform work properly, i have several challenges ahead of me, but only one that really intimidates me:

Binding the check box to the junction table.

I don't think I can simply put an SQL query in the record source property for the checkbox. In my other subforms I've used the properties of Link Master/Child Fields, but I don't think that will work for this particular problem. OTOH I really have no idea how the Link property works anyway.

I considered using alot of functions on the checkboxes to update the junction table properly, and to describe properly it's current state.

I feel i haven't explained adequately my problem, but I can't find the words to say my difficulties.
I know this post will inspire many tl;dr but I can't concise it anymore

I hope someone has had experience with this sort of problem, and has some advice on the best solution. Step by step instructions are not necessary (probably), I'm just floundering and don't know the next steps.

upon request I will provide a copy of the DB.

 

[Mod]

I wish I could!!!! The post-doc I'm designing this database for has specified all of the controls to be used, and their behaviour. At first she wanted a checkbox for each gene, and only relented when I reminded her that the gene info was dynamic data and that the form would need to be updated every time a row was added to the gene table.

A picture of what the subform looks like is attached. It pretty much has to look like this.

how it should work hopefully (a better explanation maybe): each check box/text box pair is an iteration of the form. the values in each text box are abbreviations for names of genes. The parent form is the main form for viewing/editing the specimens table. The checkbox represents a junction between the current record speciman and the gene next to the check. My current plan is to have the forms oncurrent event be responsible for mainting the agreement between the subform and the mainform, and have the check box's click event be responsible for updating the junction table (add or delete a record).

It just seems to me like the design is flawed when you rely on events to maintain the integrity of the data.




On a side note, in the Access development (proffessional) world, is it common for the end-user to specify what controls to use? Because it is very difficult.