Problems with Combo Box in Base

[pauly]

Sorry my fault Member_ID cano not be Null. Since each certificate MUST be awarded to a Member. Sorry for my mistake.

What you said about invoice form is right: there is no customer sub form, just a list box for selecting customer.

In the invoice case if at the beginning one is not sure whether the customer is new or not, it is a little awkward because

1. he can open a customer form to check if the customer is new or not, if the customer is new he then add the new customer’s name, address etc. to the customer table but if the customer already exits then he close the customer form and open the invoice form but he wasted some time.
2. he can open the invoice form first but again he can click the list box to check if the customer’s name is there. If it’s there then he is lucky, he just chooses the name to finish making the invoice, but if not, he has to open a customer form to add the new customer’s info first in order to continue to finish the invoice.
   That’s why triggering an event (macro) to save the new customer or member is meaningful. Good morning and have a beautiful day

[Ratslinger]

@pauly

each certificate MUST be awarded to a Member.

So what I have presented to you works. If you have issues with code, I have given you links for documentation. You should not use macros if you cannot maintain them.

Have presented reasons for not reversing as you asked. Also would not use a combo box instead of the list box in the example. Once you leave the form and move to something different like sub form, it is saved which can be an issue since the member is not chosen. That is most likely the reason the author has used the separate form.

I have nothing further on this topic. Please refer to the documentation or ask a new question. You have stated:

@pauly

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 original question has been answered.

[pauly]

Thank you!!! Much appreciate!!!

[pauly]

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)

Hi Ratslinger, I have studied the case whole day and finally realized that it is me who didn’t understand the relationship between the main form and the sub form. What have you done is therefore perfect. I marked this post from you as the solution to my original question. I want to thank you again from my heart for your help and great patience with a Base beginner.

[Ratslinger]

@pauly

I have studied the case whole day

Glad all is good. I thought the best sample was:

@Ratslinger

@pauly

student can have more certificates

Try this → Certificate1PostedModified.odb (16.5 KB)

On the Combo, hit enter to select.

along with the SQL to set Certificate as Unique. Regardless, you have a basis to go forward.

[pauly]

along with the SQL to set Certificate as Unique. Regardless, you have a basis to go forward.

YES! I changed to this post as the better solution. Thank you again, Ratslinger! You are really great!!!

[pauly]

@Ratslinger

ALTER TABLE "tbl_Certificate" ADD CONSTRAINT MYCONSTRAINT UNIQUE ("Register_No");

Hi Ratslinger!

1. What does “MYCONSTRAINT” mean here? Must it be there? Can it be lower case?
2. Will it be enough to use “ALTER TABLE “tbl_Certificate” ADD UNIQUE (“Register_No”);” , since the constrain is related to only one column?
   Thanks!

[Ratslinger]

@pauly

What does “MYCONSTRAINT” mean here? Must it be there? Can it be lower case?

You should refer to the documentation for the database you are using. I believe this is HSQLDB embedded v1.8 → Hsqldb User Guide. On the second page you can find links to download. With the PDF version you can search for CONSTRAINT.

In short, it is a unique name created by you the user. Yes it needs to be there. Yes it can be lower or mixed case.

@pauly

Will it be enough to use “ALTER TABLE “tbl_Certificate” ADD UNIQUE (“Register_No”);” , since the constrain is related to only one column?

This is SQL - code for the database. All elements required (see manual for details) must be present or else you will get an error. You cannot leave out the table name just because you only have one table.

[pauly]

Thanks for the perfect answer and the download link for HSQLBD users guide, which is really useful.

[Villeroy]

You can drag&drop the elements on the forms navigator.

[pauly]

@Villeroy

You can drag&drop the elements on the forms navigator.

Yes, but I don’t know how to modify the code in macro accordingly (suppose there must be some small modifications).