One quick question. I know you’ve tagged questions with HSQLDB. Are you using the default embedded HSQLDB? IF so, before you go too far, consider changing. It is not for production use as there can be potential data loss. Backup!
Thank you for bringing up HSQLDB. I’m using the internal HSQLDB as a development platform and for learning. I have a MySQL server sitting across the room, that I’ll move to for multi-user access (Wife & I) once I get the system working well enough to use. I see you’ve posted an answer, I’m about to look at it now.
Consider moving to MySQL now (I personally find PostgreSQL and Firebird to be better). You can place MySQL server software on any machine and migrate to another machine later. There are multiple reasons for changing NOW. Already mentioned embedded problem with data loss. Also, that embedded version is VERY old. Moving to MySQL or another DB allows you to take advantage of newer versions of SQL and capabilities. Later conversion may require you to re-write SQL you may already have as syntax may differ. Field types in tables can differ also. Well this is some. Hopefully you get the point.
Okay, you’ve made an impression. Will I have to add a connector to my workstation Base install to connect to say a Firebird instance, also installed on my workstation?
Well, first thing is to determine which DB you are to end up with. I believe I mentioned planning before. Consider in this choice the future and how e-commerce may affect this as hosting companies may allow for different DB’s (they host but you interface with) and this may be a factor in choosing. Migration and connection will depend upon the from and to and is best in a new question.
Not going to migrate this system outside our house.
Only going by you mention of:
...though it is not the eCommerce site we want it to be.
Not to say your entire system but the possibility of data between eCommerce & your system. Again, just planning & future consideration.
In answer to your question about connectors, there are some answers here in the forum. You can also check Chapter 2 - Creating a Database, section on Accessing external databases in the Base documentation.
Hello,
Notwithstanding the open questions in the comment, have modified your ‘Item’ form. It is simply adding a sub form here. A table control was used and the Color_ID field was changed to a List box to display the color (from color table) rather than the ID.
Sample ----- Many_To_Many_Question.odb
A few other points. Have seen you have macros (did not look at closely - yet). Since there are still some open bugs regarding form sizing, you can set the size and position of the form using a macro. See:
Base: How to define a form’s exact size?
and on Dutch forum (has sample files):
are the properties width, heigth, xpos, ypos of a form in base accessible via basic?
The other point is the button use for changing forms. Don’t see that as necessary here. You can have multiple internal main forms on a Base form. You can also hide/reveal controls, if wanted, when using different forms. See:
Hide combobox in starbasic with Macro
Libreoffice Base - Display Form based on Group Box Option selected (be sure to see link in comment under answer)
Tabbed forms within a Main Form
Edit 2019-01-17:
OK. Here is the modified form in edit view. Have used the Form Navigator and selected the SubFor and show the properties:
By selecting the ellipsis to the far right of either Link property brings up this dialog:
By using the drop down (actually a list of fields in each table) you can relate what is to be viewed in the sub form. Now although your intermediate table contains entries for many items, this linking process will only show those items in the intermediate table which match the selected field in the displayed Items (master) table.
Hope this helps.
I’ve looked at the file you uploaded and the ItemsModified form. I see the table shows the Item_ID and Color columns. I just realized I omitted something that is significant. I don’t see a way to “select” colors associated with a new Item and would then be saved as new record(s) in the intermediate rel_Item_Color table. I’ll now look at the other links you provided…
Maybe I don’t see something. On this modified form, if you enter a new item the sub form will be empty. You can add records to this sub form - this is the association and thus the intermediate table is updated.
Then I must be missing something. When I add a new Item record I don’t see how to scroll or how to select color choices…
I’ve added 2 new items and there are no associated intermediate table records for the new Items…
Many things to learn yet. When you enter a new item, the record is not in the database until it is saved. Until it is saved there is no connection in the sub form. Two ways to save on existing form. 1) go to a different record (new or otherwise) and back to this one which essentially saves this record, or 2) on the Navigation bar under the Item click on the Save Record icon which will display the new record & allow entry in the sub form.
This empty sub form now has the Item ID already filled in. To add an item, select the Color field & use drop down to select color or simply start typing color wanted. It is list box as discussed in another question.
Okay, I see it working now. But I don’t see how you did it… When I “edit” the ItemsModified form, I can bring up the Properties of the Navigation Bar, nothing to see there. When I bring up the Properties of the Table Control I don’t see anything there either…???
Sorry, should have mentioned in answer. This is how you typically use a sub form (see LO docs for more info if needed). You limit what is seen in a sub form by linking it to the main form. This limits records in the sub form to what is associated with it in the main form. Look at the sub form properties on the Data tab under Link master/slave fields items.
In this manner you can go even further with sub sub forms, sub sub sub forms etc.
My newbie-ism has me in a fog, I’m not understanding…
Okay, found the sub-form properties.
@Inkblotr Just looked at the existing macro in your sample. I started with type this of code (from docs if I remember correctly) years ago. Use of ‘Tags’ can become a problem when many forms are involved. You may want to look at the sample code in my answer on this post → Base ERROR - Open Form with macro.