That is where the problems started. Originally you had it that way and I interpreted it (design dictates it) as one-to-many certificate-to-members. Later you clarified Member can have many certificates. That is how this is constructed. MainForm and Members are just names. The mainform is Members and the subform is for certificates. Reversing this with destroy the one-to-many.
1 Like
Turning a one-to-many relation into a many-to-many relation is a major change to your database structure and then you have to rebuild the forms. It involves a 3rd table mapping students to certificates as demonstrated in my Movies_Genres.odb. Then you need a subform with a table control having a column of listboxes. The table control shows all related item-IDs represented by the visible listbox strings.
My approach with the auto-refresh macro assumes that you already have your tables, relations and indices ready to use and some hierarchy of forms and subforms reflecting these database relations. The auto-refresh macro saves you some clicks because it updates all dependent form elements when some record set has changed. The handy thing is that it does not require any modifications in the Python code. Writing macros is hard work. You have to work out every single line of code and spend a lot of time with testing and rewriting.
1 Like
Good evening Ratslinger, wee are having misunderstanding. 
I attach a simple Invoice database example, where there is a 1:n relationship between Customers and Invoice, just like that between Members and Certificate (Letâs think Members = Customers, and Certificate = Invoice).
I understand it is crucial to a database, so I really want to make it clear.
I hope I finally made myself understood.
Link to the .odb example file found here: LibreOffice: Base
odb74-Invoice-pt2-Forms.odb (21.2 KB)
Each invoice belongs to exactly one customer. There is no invoice which belongs to 2, 3 or more customers. However, many invoices may belong to one customer but not more than one. This is one-to-many.
Each of your certificates may belong to zero, one, two or more students and each of your students may own zero, one, two or more certificates. This is many-to-many.
Dear Villeroy, thank you very much for your help. Yes, THAT IS GREAT! I will try them later when I fully get through Ratslingerâs method.
Writing macros is hard work. You have to work out every single line of code and spend a lot of time with testing and rewriting.
You are right, macro is indeed hard. But I started to learn it. I believe that is the way.
Turning a one-to-many relation into a many-to-many relation is a major change to your database structure and then you have to rebuild the forms. It involves a 3rd table mapping students to certificates as demonstrated in my Movies_Genres.odb. Then you need a subform with a table control having a column of listboxes. The table control shows all related item-IDs represented by the visible listbox strings.
Fortunately it is not a job, but a hobby so far. It is a made up, imaginative case⌠for the purpose of better understanding BaseâŚ
The relationship in this case between Members and Certificate can be considered parallel to a relationship between Customers and Invoice, which is a very common case, for example here in this link:
https://thefrugalcomputerguy.com/downloads/15/odb74-Invoice-pt2-Forms.odb
VilleroyEach of your certificates may belong to zero, one, two or more students and each of your students may own zero, one, two or more certificates. This is many-to-many.
No. Each certificate belongs and CAN ONLY BELONG to just one student, just like each invoice belongs to exactly just one customer.
But the same student can have more than one certificates with different Certificate_ID and different Registration numbers.
The parallelism is shown in the pic below:
Alright. If the certificates have individual IDs and the studends collect these unique certificates then we have a one-to-many relation. I thought of generic certificates (titles, degrees).
1 Like
That is just what I gave you in the last sample. I donât understand what the issue is.
1 Like
Yes! I am so happy that it is clear now. It can be quite confusing.
YES! I am so happy that I finally made it clear. It is indeed a very important thing.
Hi Ratslinger, though the tables are correct, there is still a issue inside the form, where the MainForm Certificate becomes a sub form of Members form (vid pic attached). Unfortunately I donât know how to make a change so that the Members form is a sub form of the MainForm, together with the corresponding changes in the Macroâs code.
Now the form doesnât work as I intented. For example, in the form, if one clicks the New Record button in the navigation bar, there is a possiblity to add new records of Certificate table, which is not my intention.
Hope you get it and you can help. Thanks!!!
Yes, that is how the one-to-many works. If you use forms and sub forms, you cannot enter a certificate before knowing which Member it will be assigned to. Of course you can completely eliminate any automation and linking by doing all manually - Member and Certificates each on a main form. Lot of possibility for error.
As for navigation bars, this is all personal choice and design but I donât comprehend why you seem to think clicking the New icon would not add a new certificate. That is what new does.
With this form, select a Member (or enter a new one) then add one or more certificates. When done you can select a different member and enter certificates for that member.
Itâs so sad that I am not understood. In the certificate form, in every record, to every Certificate_ID there should be only one Member_ID (Member). This is what I want.
No, that has been understood and that is what you have been provided. The one issue you have not dealt with is making the Certificate column unique so a second certificate with the same number cannot be entered.
Do that from the Base menu â Tools->SQL. Here is the statement which works with the last sample provided:
ALTER TABLE "tbl_Certificate" ADD CONSTRAINT MYCONSTRAINT UNIQUE ("Register_No");
Other than that, what you asked for is in the last sample. I know of no way to accomplish what you want by reversing Member and Certificate. Will not work.
1 Like
In the last sample you provided, in the Certificate form, the main form is based on Member table, and sub form is based on the Certificate table. But the relationship between Member_ID of tblMember and Member_ID of tblCertificate is one to many. Thus the form based on tblMember should be a sub form. There lies the contradiction.
Yes - so where is the issue?
It has been noted all along a Member can have many certificates. A single certificate can be issued to only one Member SQL (in my last comment regarding a constraint of âUniqueâ completed this - you did not provide in your original design).
The original question was regarding a combo box and adding new members. This has easily been answered. It appears you are having a problem understanding relationships.
Letâs forget about the Members and Certificates, which caused so many confusions, instead, letâs consider Customers and Invoices, like in this video: https://youtu.be/qsYdu8Mn5_M
In the certificate form Certificate_ID should be primary auto-incrementing and Member_ID must NOT be primary nor auto-incrementing and can be NULL. And the main form is based on Certificates table while the sub form is based on Members table. Is that possible?
I am really helpless. Very sad. But I am still feel grateful and appreciate very much your help!!!
Respect 
First mention of Member_ID in Certificate table being NULL. Records with no connection.
If you want (and you stated this many times) one-to-many as Members-to-Certificates then main form is not Certificates.
If you want Certificates as the main table (and NULL numbers) you donât have the correct relationship and the use of the Combo box in the original question becomes mute. In fact, for this you would not even need a Member table. Just place the member name in the Certificate record.
Also had a second look at that sample you presented (twice). That is simply a list box in a form. Customer/Invoice has no form/sub form. The sub form there is Tasks or as I would rather call them items of the invoice.