You have connected the wrong macro to the List box. S/B the key handler & not the Check & Save.
You have other issues. Will create a sample a bit later and post when done. Main issue is the link between tables - it is backwards.
1 Like
Have done some changes to your process.
Deleted Member_ID from Certificate table and added Certificate_ID to Member table. Reverse or your original. Each certificate with many members.
Combo Box is part of the record and modified the macro to reflect reloading the correct record. Dropped the “Check for RETURN” as executed upon losing focus of the control.
All seems to work with the limited testing I did.
Excuse me but I would do this somewhat differently.
Have First and Last names separate. SQL can always join. Easier to sort later. Also keep other info (address, gender, DOB) in another linked table. Currently you duplicate all this.
Sample ---- Certificate1.odb (16.9 KB)
1 Like
Dear Ratslinger, thanks SO MUCH!!! 
The .odb file after your correction now works perfectly! You have my deepest respect! I am so grateful for your support in the struggling process from Access to Base.
Excuse me but I would do this somewhat differently.
Have First and Last names separate. SQL can always join. Easier to sort later. Also keep other info (address, gender, DOB) in another linked table. Currently you duplicate all this.
This I totally agree, thanks for the reasonable suggestion.
The only issue i find is that by deleting Member_ID from Certificate table and adding Certificate_ID to Member table, now under one certificate (one Certificate_ID, one Register_No) there are two (or could be more than two) persons, which is not my intention. My plan is to award a unique certificate with a unique Certificate_ID (Register No) to only one person, i.e, two or more persons can not share a common Certificate_ID (Register NO).
Imagine in a language school, 100 students will take a German language course from 01.06. to 30.07, all will receive a certificate with a unique Certificate_ID (Register NO) after the course. But the same student, say John, can participate more than one course, so John’s name can appear multiple times in the Certificate table.
I hope I made myself understood.
Anyway, I am really delighted and thank you again!!!
So this sounds to me as one student and many certificates. The opposite of what is here?
Hopefully I have given you enough to go by.
1 Like
Sorry Dear Ratslinger! Probably I made this relationship too complicated. 
Yes, the same student can have more certificates, but one certificate with a unique Registration Number can be given to only one student.
But in the above attached pic of my last post shows that one certificate with a unique Registration Number has been given to more than one student which was not my interpretation. 
Dear Ratslinger,
really appreciate your help. Could you please make a small change to the form so that the Members form will be a sub form of the MainForm, corresponding to the change in the Macro’s code, where I have difficulty to modify.
Thanks a lot.
I attach the screen shot here.
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.