@bucher1 But the problem I'm running into is finding code to help put different pictures that will link with one part number.
Good Morning Bucher,
Now we have something to work with!
If you stick with this... you can do this... however,
this is really not a novice project - so be patient with yourself (and with us :) ) we've all been there, and we'll help you get thru this!
📌You are not going to find a single VBA code solution that will show multiple images for a given part.
🏁 HOWEVER - comma...
the Concept of Normalization can help here:
> Database Normalization and Table Structures.
So to give you idea as to how I might accomplish what it appears you are trying to do...
Using a
very simple construct
(I'm going to GREATLY simplify the normalization)
Table with your "Part information"
tbl_Parts-
[PK_Part] [PartID] [PartName] [PartDetail ]
-
[1 ] [124A ] [Example1] [Just an example part item one]
-
[2 ] [567A ] [Example2] [Just an example part item two]
Table linking each part to a series of pictures
tbl_ImagesToParts-
[PK_Img2Prt] [FK_Part] [ImgName ] [ImgDetail ]
-
[1 ] [1 ] [Img_1001.jpg] [1st Image for item one]
-
[2 ] [1 ] [Img_1002.jpg] [2nd Image for item one]
-
[3 ] [1 ] [Img_1003.jpg] [3rd Image for item one]
-
[4 ] [2 ] [Img_2001.jpg] [1st Image for item two]
-
[5 ] [2 ] [Img_2002.jpg] [2nd Image for item two]
-
[6 ] [3 ] [Img_3001.jpg] [3rd Image for item three]
All of the fields that start with "PK" are of type "Autonumber" and will be set to the "primary key" for the table
What these fields do is keep a unique record id that is easier, and more stable, to use to relate records from one table to another. (Please read the article about Normalization if you don't understand)
Better yet, this primary key field, IMNSHO, should NEVER change for the record; hence why we have the field for the [PartID] . Field [PartID] can be either text or numeric data type and if the part id changes (say management wants to add a location id or whatever), you only need to update in one place!)
Something to note: [tbl_ImagesToParts]![ImgName ]
In this example I've only used the image file name.
In post#2 in the insight article I linked you to the function uses the entire file-path-file name to the image - we can talk about how to handle this later as the function can be changed to search for the image file in various ways depending on the need.
[tbl_ImagesToParts]![FK_Part] is of data type Numeric-Long-Integer
This is the field that we're using to establish the relationship the two tables
(IIRC: In Access2000 > Menu bar > DATABASE TOOLS > Relationships)
In the dialog that opens you add the two tables and then drag-drop
[tbl_Parts]![PK_Part] on top of [tbl_ImagesToParts]![FK_Part]
so you have something analogous to what I have in my school roster database the table between students, classes, attendance etc... (the lines here show 1:1 and 1:Infinity ... that's next)
now change the relationship by RIGHT-clicking on the line (here I show the relationship between the student list and the attendance table.)...
> Set Enforce Referential Integrity to true
Two things here, I do not use the Cascade-Update option because I use the autonumber field as the primary key (again, see the article on Normalization if you don't understand). This field will NEVER change so the auto-update is not needed. The second thing here is that I rarely if ever use the Cascade-Delete option because it's all too easy to lose a lot of information (for example, if we set C-Delete to true, deleted item one in tbl_parts (PK_Parts = 1) then all of the records in [tbl_ImagesToParts]![FK_Part]=1 would also be deleted!!! OUCH (yes, it's happened to me, learn from my mistake) Whereas if C-Delete is set to false then every record in [tbl_ImagesToParts]![FK_Part]=1 would have to be deleted first before the record in tbl_parts could be deleted.
If you look back at post#4 I have something fairly complex; however, the left panel is the parent form, when I select a record the two sub-forms populate...
So, for this example, something really simple would be to setup two forms for example
frm_Parent - data source will be [tbl_parts]
frm_Child - data source will be [tbl_ImagesToParts] and will eventuall be the subform to frm_Parent. Once linked, the related images to the current record in frm_Parent will show. As a stand-alone form all of the images will be available.
Have your part information in the header section of frm_Parent and in the details section of frm_Parent insert the frm_Child as the subform relating the parent to child on between the [
PK_Parts] and the [
FK_Parts] fields
In the frm_Child, the detail section would have the image control that uses the custom function (that I referred you to in
post#2 in the insight article I linked you to - this custom function "reaches out" on to your network, local drive, etc... and finds the image file and then displays it in the image control...
So, because you're stuck with Access2000, before we go any further; the question here is: can you download and open the database and related files in the attachment to the post in the above link?
If not, I can still down grade it to an Access2000 MDB - however, try opening it and let me know 😵
It will be easier for you to understand how the function works and how it is used if you can open that database and related files... to try and explain it would make the post 1000x longer!
5
