Help with attachments in Form Design

%3CLINGO-SUB%20id%3D%22lingo-sub-1350617%22%20slang%3D%22en-US%22%3EHelp%20with%20attachments%20in%20Form%20Design%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1350617%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%3EI%20am%20designing%20a%20form%20for%20a%20chemical%20inventory%20and%20I%20have%20the%20GHS%20Hazard%20label%20drop%20downs%20and%20I%20am%20trying%20to%20get%20the%20attachment%20image%20for%20each%20option%20to%20show%20correctly%20and%20I%20cannot%20seem%20to%20get%20it%20to%20work.%20%26nbsp%3B%3C%2FFONT%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20made%20a%20supporting%20table%20with%20the%20hazards%20(corrosive%2C%20irritant%2C%20etc.)%20and%20linked%20it%20to%20the%20inventory%20table%20in%20four%20columns%20Hazard%201%2C%20hazard%202%2C%20etc.%20for%20a%20relational%20lookup%20drop%20down%20menu.%20I%20am%20trying%20to%20make%20the%20image%20for%20each%20label%20show%20to%20the%20right%20of%20the%20drop%20down%20on%20the%20Form%20View%20when%20applicable%20but%20I%20am%20pretty%20new%20with%20the%20expression%20builder.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1350617%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3E2016%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1350762%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20attachments%20in%20Form%20Design%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1350762%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F643211%22%20target%3D%22_blank%22%3E%40Akaishi%3C%2FA%3E%26nbsp%3BUnfortunately%2C%20you%20seem%20to%20have%20at%20least%203%20design%20problems%20in%20the%20tables%20and%20the%20database.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%2C%20experienced%20Access%20developers%20have%20learned%20(often%20the%20hard%20way)%20not%20to%20use%20lookup%20fields%20in%20tables.%20That%20leads%20to%20more%20problems%20than%20they%20are%20worth.%20Replace%20them%20with%20standard%20Primary%2FForeign%20Key%20fields%20in%20the%20related%20tables%20and%20use%20combo%20boxes%20only%20on%20forms%2C%20where%20they%20are%20appropriate%20for%20interface%20designs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecond%2C%26nbsp%3Bexperienced%20Access%20developers%20have%20learned%20(often%20the%20hard%20way)%20not%20to%20store%20attachments%20internally%20in%20Access%20tables.%20Binary%20files%20(images%2C%20etc.)%20embedded%20in%20the%20accdb%20will%20bloat%20the%20size%20of%20the%20file%20beyond%20a%20manageable%20size%20very%20quickly.%20Store%20images%20externally%20in%20a%20share%20and%20only%20load%20them%20as%20needed%20in%20forms%20or%20reports.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThird%2C%20the%20table%20design%20you%20describe%20is%20often%20referred%20to%20as%20%22a%20spreadsheet%22%20and%20it%20is%20appropriate%20for%20Excel%2C%20but%20not%20for%20a%20relational%20database%20application%20built%20with%20Access.%20%3CA%20href%3D%22https%3A%2F%2Frogersaccessblog.blogspot.com%2F2011%2F03%2Fthe-problem-of-repeated-columns.html%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3ECorrect%20that%20table%20design%3C%2FA%3E%20to%20improve%20the%20overall%20performance%2C%20maintainability%20and%20flexibility%20of%20this%20relational%20database%20application.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20now%20we%20come%20to%20displaying%20images%20on%20a%20form.%20Use%20the%20image%20control.%20Load%20the%20appropriate%20image%20into%20it%20from%20the%20external%20file%20using%20the%20AfterUpdate%20event%20of%20the%20combo%20box%20on%20the%20form.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I am designing a form for a chemical inventory and I have the GHS Hazard label drop downs and I am trying to get the attachment image for each option to show correctly and I cannot seem to get it to work.   

I made a supporting table with the hazards (corrosive, irritant, etc.) and linked it to the inventory table in four columns Hazard 1, hazard 2, etc. for a relational lookup drop down menu. I am trying to make the image for each label show to the right of the drop down on the Form View when applicable but I am pretty new with the expression builder.

 

 

1 Reply

@Akaishi Unfortunately, you seem to have at least 3 design problems in the tables and the database. 

 

First, experienced Access developers have learned (often the hard way) not to use lookup fields in tables. That leads to more problems than they are worth. Replace them with standard Primary/Foreign Key fields in the related tables and use combo boxes only on forms, where they are appropriate for interface designs.

 

Second, experienced Access developers have learned (often the hard way) not to store attachments internally in Access tables. Binary files (images, etc.) embedded in the accdb will bloat the size of the file beyond a manageable size very quickly. Store images externally in a share and only load them as needed in forms or reports.

 

Third, the table design you describe is often referred to as "a spreadsheet" and it is appropriate for Excel, but not for a relational database application built with Access. Correct that table design to improve the overall performance, maintainability and flexibility of this relational database application.

 

And now we come to displaying images on a form. Use the image control. Load the appropriate image into it from the external file using the AfterUpdate event of the combo box on the form.