I am designing a form in Base that will link to other records in a many to one relationship with a separate table containing images. What I want to do, if possible, is have each main record populate a drop-down box that has each image LISTED (i.e. Image 1, Image 2, etc…) and when the box is changed, the indicated image is displayed in the sub-form. I’ve looked, but can’t seem to make sense of what kind of Macro I’d need, or how to set that up. Any advice is appreciated.
Hello,
It appears you are dealing with a one-to-many relation of tables. You have a main form record with many records in a sub form and each containing an image. So it seems you want to select a main record and then from among the linked records select one to display an image.
If this is the case, and to not use macros, instead of a list box use a table control in a sub form along with an image control. So you have:
Main form record (table control or individual fields
then linked to it
Sub for using table control - all fields can be hidden except the related name field and additionally add an image control field connected to the image field of the record.
With this, you select a main record, then a sub record which displays the image:
No code to deal with. Main form and sub form linked through the associated ID (sub to main):
This example linked ‘visit_link’ in ‘places’ to ‘id’ in ‘visited_places’.
Edit 2021-04-23:
Have no problem with multi-field keys:
Edit 2021-04-23 #2:
To further clarify:
Removed visit_link and linked id to id
also:
Adding a new image.
I believe I have now answered this and additional questions.
Okay. What you are describing is ALMOST what I am looking for, at least as near as I can tell. Please correct me if I’m wrong. I’ve got two tables and they are linked with a one-to-many relationship yes. I’m just not sure what you mean by using a list box. I can’t post an image in the comment, so you can view a PNG of my tables on my web server. What I am looking for is a way I can iterate through all the images that have the same ID field. The database is already setup with a foreign key constraint. So what I need is a way to select a particular record from the Main table, and when that happens, the sub form shows the first picture from the Pics table that has the same ID. Then I need a control that will let me select later images in the Pics table for the same Main record. Plus, I need to be able to add additional images as they may become available, still relating to the same Main record.
First, your link does not work. Always test your links.
Second, you should not access linked records from the main form or use that to add forms. That would require macros and makes the form unnecessarily complex.
The answer presented does all you asked for except to add new images. Adding that capability is easy enough. Simple expand the sub form table control to show all fields (example hid them to be more like a list box) and new items can be added.
I can’t believe I goofed the link up. My bad. The real link is https://www.newideatest.site/DB_Tables.png. It’s tested and works.
As to the rest, I’m trying to understand what you mean, but perhaps I’m being dumb. I’m not trying to access everything from the main form. A sub-form is perfectly acceptable to me, as long as it has the forward/back buttons and a button to add an image. Most fields should be simple enough in defaults. The sub table only has four fields. One for a generic record number (since Base refuses to work with a database that doesn’t have that as a primary key), an integer to indicate the record number in the main table that the image belongs to, a counter for the sequence number of the image (i.e. image number 2), and a blob to contain the actual image. And please don’t say I shouldn’t use a blob. For this instance, a blob is really the only workable solution.
Stated:
…since Base refuses to work with a database that doesn’t have that as a primary key…
This is databases in general. You need a Primary key. It is how they efficiently work. Nothing to do with Base.
So the answer provided is just what you have asked for. As noted in my last comment just expand the sub form table control to show all fields from the sub table except for the blob. That should be attached to the image control.
I will look into that. But what I meant was that I HAD a primary key. It was just a multi-field Primary key which is easily supported my MySQL (or MariaDB) in this case. But a multi-field primary key doesn’t work in Base, so I have to create a single field key for Base to use. It’s annoying, but not overly troublesome.
Not certain you understand. Base has nothing to do with keys. That is for the database used (Base is NOT a database). LO provides both HSQLDB v1.8 and Firebird v3.x embedded databases which can be used without further user installation. The database used can be what the user chooses.
Both of the embedded databases allow multi-field primary keys. This is extremely common in a many-to-many relationship. Do not see why you would want/need a multi-field key in your situation.
Example using HSQLDB embedded:
Also, to answer your list box comment, in the question you note:
…have each main record populate a drop-down box…
In LO the control for a drop-down is typically a list box. A combo box can be somewhat similar.
Okay, so let me explain a bit about the multiple field key. As I showed in the png above, the picture field has an integer field to point to the record # of the person the image is for, and also has a counter field for which number that is for that person (i.e. it’s their 3rd image). Taking either one on its own would cause duplicates, but combined the two fields and you will never have a duplicate. That’s what I was HOPING to do anyway. MySQL is fine with that. But when I load the table into BASE it refuses to allow me to add records unless I have a single field unique key.
This was posted before AS A COMMENT, but somehow it was shown as an answer. Don’t know why, but I deleted the answer and re-posted as a comment.
Also, one thing that troubles me about your solution seems to be read-only for the images. I don’t mind using a Macro if need be, but my goal is to have everything on ONE screen, including the ability to add images for the individual shown on the main form.
Don’t know what you mean by read only for images. Want to add a new image? Point the table control at a new record and add away. Want to change an image? Right click the image control and change it.
Also see edited answer for multiple field key
This looks like it should work, thanks.