Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-17-2019, 10:42 AM   #1
GustavoGanzo
Newly Registered User
 
Join Date: Apr 2019
Posts: 6
Thanks: 8
Thanked 0 Times in 0 Posts
GustavoGanzo is on a distinguished road
Form that appends/adds to multiple joined tables

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:
Attached Files
File Type: zip Gefahrenstoffe Database V1.7.zip (361.8 KB, 4 views)

GustavoGanzo is offline   Reply With Quote
Old 04-17-2019, 11:07 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,875
Thanks: 105
Thanked 2,378 Times in 2,186 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Form that appends/adds to multiple joined tables

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
GustavoGanzo (04-17-2019)
Old 04-17-2019, 11:18 AM   #3
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,512
Thanks: 0
Thanked 367 Times in 366 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Form that appends/adds to multiple joined tables

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.

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 04-17-2019 at 11:49 AM.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
GustavoGanzo (04-17-2019)
Old 04-17-2019, 11:31 AM   #4
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 313
Thanks: 21
Thanked 68 Times in 68 Posts
mike60smart is on a distinguished road
Re: Form that appends/adds to multiple joined tables

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?
mike60smart is online now   Reply With Quote
Old 04-17-2019, 10:33 PM   #5
GustavoGanzo
Newly Registered User
 
Join Date: Apr 2019
Posts: 6
Thanks: 8
Thanked 0 Times in 0 Posts
GustavoGanzo is on a distinguished road
Re: Form that appends/adds to multiple joined tables

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

@June7

Quote:
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?

Quote:
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.

Quote:
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?

Quote:
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.

Quote:
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
Quote:
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?
Attached Files
File Type: zip Gefahrenstoffe Database V2.zip (241.8 KB, 3 views)
GustavoGanzo is offline   Reply With Quote
Old Yesterday, 12:33 AM   #6
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,512
Thanks: 0
Thanked 367 Times in 366 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Form that appends/adds to multiple joined tables

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?
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; Yesterday at 01:44 AM.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
GustavoGanzo (Yesterday)
Old Yesterday, 12:40 AM   #7
GustavoGanzo
Newly Registered User
 
Join Date: Apr 2019
Posts: 6
Thanks: 8
Thanked 0 Times in 0 Posts
GustavoGanzo is on a distinguished road
Re: Form that appends/adds to multiple joined tables

Quote:
Originally Posted by June7 View Post
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?

GustavoGanzo is offline   Reply With Quote
Old Yesterday, 01:22 AM   #8
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,512
Thanks: 0
Thanked 367 Times in 366 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Form that appends/adds to multiple joined tables

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.
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; Yesterday at 01:27 AM.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
GustavoGanzo (Yesterday)
Old Yesterday, 04:15 AM   #9
GustavoGanzo
Newly Registered User
 
Join Date: Apr 2019
Posts: 6
Thanks: 8
Thanked 0 Times in 0 Posts
GustavoGanzo is on a distinguished road
Re: Form that appends/adds to multiple joined tables

Quote:
Originally Posted by June7 View Post
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?

Quote:
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.

Quote:
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.

Quote:
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.

Quote:
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?

Quote:
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!

Quote:
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.
Attached Files
File Type: zip Gefahrenstoffe Database V2.zip (332.2 KB, 1 views)
GustavoGanzo is offline   Reply With Quote
Old Yesterday, 04:53 AM   #10
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 313
Thanks: 21
Thanked 68 Times in 68 Posts
mike60smart is on a distinguished road
Re: Form that appends/adds to multiple joined tables

Hi Gustav

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

RI.JPG

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.

Substances.zip
mike60smart is online now   Reply With Quote
Old Yesterday, 09:20 AM   #11
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,708
Thanks: 11
Thanked 4,000 Times in 3,938 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Form that appends/adds to multiple joined tables

Post 9 was moderated, I'm posting to trigger email notifications.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old Yesterday, 11:04 AM   #12
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,512
Thanks: 0
Thanked 367 Times in 366 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Form that appends/adds to multiple joined tables

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.

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; Yesterday at 11:21 AM.
June7 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
New Record with Auto-Number Primary Key Joined to Multiple Tables MeilingM Queries 9 10-29-2012 12:05 PM
Replace function with multiple joined tables MGAllen Queries 4 07-02-2012 11:32 AM
Add multiple records to joined tables maw230 Tables 13 10-06-2010 07:43 AM
form that adds record into tables. how can it be so complicated? ymds Forms 2 07-15-2010 08:30 PM
Access Form with 3 tables joined with foreign key cute125 Forms 0 04-20-2007 02:47 AM




All times are GMT -8. The time now is 12:28 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World