Form that appends/adds to multiple joined tables

[GustavoGanzo]

Hi team,

I am working to create a database of our cleaning chemicals. They are storred in various containers (bottles, buckets, tanks, etc.) and have different safety requirements (gloves, googles, specific tools, etc.). They are also storred in differet locations, e.g. not all soap is in the same room (...shocking, I know ).

As of now, I have a beautiful table with the ID (mine), the name and the producer of the substance.
That is connected to ... you know what, have a picture of the beauty:

Now, my users are less excited about tables than I am. So I created a few forms to help them finding what they need. My overview form has it all, and it looks pleasant enough. BUT:

If my user adds a new name (and in the background a new ID is assigned) Access gives me "The changes you requested to the table were not successful because they would create duplicate values in the index...etc" which does not make sense to me right now.

And since pictures (and databases) say more than a 1000 words - have the file:

 

[isladogs]

Welcome to the forum
Your two near identical posts were moderated for some reason - probably because of the attachments.
I've now deleted the other post

 

[June7]

Normally a form should do data entry/edit for 1 table. Use form/subform arrangement for related dependent tables (e.g. Orders, OrderDetails). If you want to add new record to a 'lookup' table 'on-the-fly' during data entry, use combobox NotInList event (products combobox on OrderDetails).

Embedding images (and other files) in table can quickly use up Access 2GB file size limit. Might be better to leave images external and save image path\name in a text field.

Not sure 5 t3Chemie_*** tables is optimized design, language barrier getting in the way.

Which is the 'overview' form - f1NeuerStoff?

INNER JOIN requires related records for data to display. Use LEFT or RIGHT. Most likely joins shouldn't be used at all in form RecordSource.

 

[mike60smart]

Hi

I tried Enforcing Referencial Integrity between tables and was only able to achieve this with 6 of the 19 tables.

It would help if the database you upload is actually in English as most don't speak German.

Which is your Main Data Entry Form?

 

[GustavoGanzo]

I attached an english version and cut down to the crucial tables.

@June7

Normally a form should do data entry/edit for 1 table. Use form/subform arrangement for related dependent tables (e.g. Orders, OrderDetails). If you want to add new record to a 'lookup' table 'on-the-fly' during data entry, use combobox NotInList event (products combobox on OrderDetails).

Understood. I tried to use subforms in the f1Overview table, and it presents the data from the subforms in a tabular view, which is fine, but if I try to append to the subforms (f3Substance_GHS for example) it gives back an error message. I suspect that happens due to the many-to-many that I broke into many-to-one + one-to-many joined table?

Embedding images (and other files) in table can quickly use up Access 2GB file size limit. Might be better to leave images external and save image path\name in a text field.

Noted! Thank you.

Not sure 5 t3Chemie_*** tables is optimized design, language barrier getting in the way.

These are the tables where I had a many-to-many relationship, and thus named them according to the two tables they connect. I suspect that this isnt the best way?! How would you recommend going about the naming of tables?

Which is the 'overview' form - f1NeuerStoff?

I translated them all, apologies for the German version - should have done the translation first to help you navigate the database.

INNER JOIN requires related records for data to display. Use LEFT or RIGHT. Most likely joins shouldn't be used at all in form RecordSource.

Could you elaborate on that point? Or point me to a resource where this is explained?




@mike60smart

I tried Enforcing Referencial Integrity between tables and was only able to achieve this with 6 of the 19 tables.

How can I do this myself? I would like to fix that, if thats needed. I was under the impression, that the way I structured the database (with substances in the middle) would guaranteee the referencial integrity, maybe thats my mistake?

 

[June7]

Simplify the RecordSource for each form. Set each to 1 table.

f1Overview = SELECT * FROM t1Substances WHERE ID_Substance = Forms!F1Overview![cobSelection];
sf2Overview_Hazards = t3Substances_Hazards
sf2Overview_GHS = t3Substances_GHS
sf2Overview_Precautions = t3Substances_Precautions
sf2Overview_PSA = t3Substances_PSA
need subform for t3Substances_Container

The Master/Child links will be on ID_Substance and ID_Chemie. Change all the ID_Chemie foreign key fields to LongInteger type, not Double. Do this before building relationships. Should be able to then set referential integrity. However, issues setting RI with the t2 tables.

Now have combobox in each subform to select value from appropriate t2 table. Make the comboboxes multi-column, example:
RowSource: SELECT * FROM t2PSA;
RowSourceType: Table/Query
ControlSource: ID_PSA
BoundColumn: 1
ColumnCount: 4
ColumnWidths: 0";0";1";0"

Textboxes can reference combobox columns by index to display info (index begins with 0), example referencing 4th column:
=[cboPSA].Column(3)
Set these textboxes as Locked Yes and TabStop No.

Could also use the overview form to enter new substances. Just include textboxes for rest of the fields. You might want to explore use of Tab control to make this form more compact.

Why are there GHS, Hazards, Precautions fields in t1Substances?

 

[GustavoGanzo]

Simplify the RecordSource for each form. Set each to 1 table.

f1Overview = t1Substances
sf2Overview_Hazards = t3Substances_Hazards
sf2Overview_GHS = t3Substances_GHS
sf2Overview_Precautions = t3Substances_Precautions
sf2Overview_PSA = t3Substances_PSA
need subform for t3Substances_Container

The Master/Child links will be on ID_Substance and ID_Chemie.

Now have combobox in each subform to select value from appropriate t2 table.

Thank you. This allows me to only have one-to-one connections, to my understanding. But one substance can have many hazards, thats why I had the t3 tables in there to allow for substance 1 to have hazard 2,3,4 etc.

Or do I misunderstand you?

 

[June7]

You do misunderstand. Each substance can have many hazard and each hazard can associate with multiple substance. This is many-to-many relationship. The t3 tables are 'junction' tables to associate records on each side of the m-to-m. Enter record for each pair of substance/hazard, substance/ghs, substance/container, substance/psa, substance/precaution. So yes, substance 1 can have records for hazards 2,3,4 in t3Substance_Hazards.

Cannot enter records to t2 tables via these subforms.

I made a lot of edits to previous post, apparently after you read it. Look again.

 

[GustavoGanzo]

Simplify the RecordSource for each form. Set each to 1 table.

f1Overview = SELECT * FROM t1Substances WHERE ID_Substance = Forms!F1Overview![cobSelection];
sf2Overview_Hazards = t3Substances_Hazards
sf2Overview_GHS = t3Substances_GHS
sf2Overview_Precautions = t3Substances_Precautions
sf2Overview_PSA = t3Substances_PSA
need subform for t3Substances_Container

Thank you for making it easily understandable - I did as you suggested, and it works fine. Logically, it now shows the IDs of the GHS, hazards, PSA etc. and not the text. How would I resolve that in the Overview form?

The Master/Child links will be on ID_Substance and ID_Chemie. Change all the ID_Chemie foreign key fields to LongInteger type, not Double. Do this before building relationships. Should be able to then set referential integrity.

Done, and changed all ID_Chemie to ID_Substance, since it means the same.

However, issues setting RI with the t2 tables.

Why is the referential integrity not working with the t2 tables? Arent those just better lists to select from? Thats what I intended them to do, at least.

Now have combobox in each subform to select value from appropriate t2 table. Make the comboboxes multi-column, example:
RowSource: SELECT * FROM t2PSA;
RowSourceType: Table/Query
ControlSource: ID_PSA
BoundColumn: 1
ColumnCount: 4
ColumnWidths: 0";0";1";0"

This works beautifully, thank you very much! How come, that I have the ID_Substance visible in some sub-forms and not in others?
And for whatever reason the sub-form "Hazards" doesnt allow additions/edits, but every other one does. I am working on that though, cant be that difficult.

Textboxes can reference combobox columns by index to display info (index begins with 0), example referencing 4th column:
=[cboPSA].Column(3)
Set these textboxes as Locked Yes and TabStop No.

Could also use the overview form to enter new substances. Just include textboxes for rest of the fields.

I think you lost me here - could you dumb this down for me/ explain a little what you recommend here?

You might want to explore use of Tab control to make this form more compact.

Will look into tab controls, thank you for pointing that out!

Why are there GHS, Hazards, Precautions fields in t1Substances?

This is living proof to the Excel origin of this file. I removed it from the table.

 

[mike60smart]

Hi Gustav

I have created a new version of your Db with the following Relationship Diagram:-



The frmOverview that opens at startup shows a Main Form to enter Substances and a Subform to enter Containers.

The Control highlighted in Green in the Substance Main Form is the Primary Key and then in the Subform for Containers the Control highlighted in Pink is the Primary Key and the Green Control is the Foreign Key linkied to the Primary Key from the Substance Form.

These Controls autopopulate when you create New Records.

I tend to leave them on display when creating the Forms to make sure that the relationship works. I then hide these controls before distribution of the Database.

View attachment Substances.zip

 

[pbaldy]

Post 9 was moderated, I'm posting to trigger email notifications.

 

[June7]

Possibly mike60smart sample db answers your questions. But here's more to consider.

Having same field name in multiple tables can become confusing, especially in queries that pull from those various tables. Many like to use PK and FK as suffix to clarify. In your case you now have 6 ID_Substance fields. Might want to do something like: ID_Substance_FK_PSA or SubstanceID_PSA. Similar for the t2 and t3 key fields.

t2 tables referential integrity fails because I suspect there are values in t3 tables not in t2, that's usual cause. I did not pursue.

The combobox can be multi-column and columns displayed or hidden in the dropdown but the box can display only one column. If you want the other columns info to be viewed after item selected then show info in textboxes. Expression in textbox ControlSource can reference columns of combobox so as to display info associated with the selected item. This is certainly optional and not a requirement. Use if you like.

For some oddball reason, the ID_Substance columns on those two tables not showing the ID have been reduced in width. This can be manually done on datasheet forms. When form closes it remembers these widths. I had to 'grab and drag' the columns to a viewable width. Users don't need to see these ID's anyway, could just delete the textboxes.

 

[mike60smart]

HI Gustav

Your Relationships should be on the lines of those shown below:-

 

[GustavoGanzo]

Hi Gustav

I have created a new version of your Db with the following Relationship Diagram:-

View attachment 74640

The frmOverview that opens at startup shows a Main Form to enter Substances and a Subform to enter Containers.

The Control highlighted in Green in the Substance Main Form is the Primary Key and then in the Subform for Containers the Control highlighted in Pink is the Primary Key and the Green Control is the Foreign Key linkied to the Primary Key from the Substance Form.

These Controls autopopulate when you create New Records.

I tend to leave them on display when creating the Forms to make sure that the relationship works. I then hide these controls before distribution of the Database.

View attachment 74641

Thank you for explaining this in such detail, I understand what you did here and also your updated diagram. Also appreciate the recommendation on the PK/FK controls, will follow your advice on that!

 

[GustavoGanzo]

Possibly mike60smart sample db answers your questions. But here's more to consider.

Having same field name in multiple tables can become confusing, especially in queries that pull from those various tables. Many like to use PK and FK as suffix to clarify. In your case you now have 6 ID_Substance fields. Might want to do something like: ID_Substance_FK_PSA or SubstanceID_PSA. Similar for the t2 and t3 key fields.

t2 tables referential integrity fails because I suspect there are values in t3 tables not in t2, that's usual cause. I did not pursue.

The combobox can be multi-column and columns displayed or hidden in the dropdown but the box can display only one column. If you want the other columns info to be viewed after item selected then show info in textboxes. Expression in textbox ControlSource can reference columns of combobox so as to display info associated with the selected item. This is certainly optional and not a requirement. Use if you like.

For some oddball reason, the ID_Substance columns on those two tables not showing the ID have been reduced in width. This can be manually done on datasheet forms. When form closes it remembers these widths. I had to 'grab and drag' the columns to a viewable width. Users don't need to see these ID's anyway, could just delete the textboxes.

Thank you June7, this makes sense to me. Appreciate the insight on the table widths, thank you again!

 

[GustavoGanzo]

Hi team,

I got another question - naturally.

My new mission is a report. Relatively trivial, I thought - but I am struggeling to reproduce the effects I achieved with my frmOverview. The idea is, that each page of the report discplays one substance, with the allocated Hazards, Precautions and GHS.

Would I work with groupings? or how would I achive that fate?

 

[June7]

Grouping and ForceNewPage property on the Group footer. Might need subreports in Detail section.