You can type directly into the focussed list box which will select the first matching item.
Alt+Down drops down a listbox with drop-down button.
You may also try a tall list box without drop-down button which scrolls as you type.
2 Likes
You can type directly into the focussed list box which will select the first matching item.
Alt+Down drops down a listbox with drop-down button.
You may also try a tall list box without drop-down button which scrolls as you type.
Thanks a lot and yes, I tried all those types. All works. But they are not what I want to achieve.
For the purpose which I want may probably need a macro, but I am not familiar yet with Base Macro.
Does anybody of you guys know where I can find some examples of related function? Books? Videos? That will be a big help. 
Hello,
That is not likely as they are different controls and perform similar but not identical things. If they did there would be no need for two different controls.
As for a sample, most often a search of the site will produce at least some results which may help. See my answer in this post for a working sample of what you want: Add entries to listbox or combobox
Also, for links to documentation see → To learn LibreOffice Base are there introductions or tutorials?
1 Like
Dear Ratslinger, thank you SO MUCH!!!
That is not likely as they are different controls and perform similar but not identical things. If they did there would be no need for two different controls.
You are right. It makes sense.
As for a sample, most often a search of the site will produce at least some results which may help.
Yes, I did search with the keyword combo box, but I didn’t have the luck to find such useful post like yours.
See my answer in this post for a working sample of what you want: Add entries to listbox or combobox
This solution from you is like magic. I have studied the case carefully. It is great!!!I need some time to learn how to apply this macro to my odb file.
I got this error when I run the macro. It is about the 2nd form with the name of “Selection”. I don’t know where is the problem. Can you help?
Certificate1.odb (23.5 KB)
You have placed the macros in My Macros & Dialogs. They belong in the Base file. Create a new module in the Standard library there:
and place macros there. The posted sample has no macros in it as My Macros & Dialogs does not move with the Base file.
You then need to re-attach the macros with the new location.
Edit:
Have taken a closer look at your macro. It does have some issues. Tested with this (and placed in the Base file):
Sub CheckAndSetName(oEvent)
Dim sSelectedItem As String
Dim sSQL As String
Dim oForm As Object
Dim oForm2 As Object
Dim oStatement As Object
Dim result AS Variant
Dim CursorTest AS Variant
REM Get text from combo box
sSelectedItem = oEvent.Source.Text
REM Get the forms
oForm = ThisComponent.Drawpage.Forms.getByName("MainForm") 'Get Form
oForm2 = oForm.getByName("Selection") 'Get Form
REM Create for SQL
oStatement = oForm.ActiveConnection.createStatement() 'Create an SQL statement object
oStatement.ResultSetType = 1005
sSQL = "SELECT ""Member_ID"", ""FirstName_LastName"" FROM ""tbl_Members"" WHERE ""FirstName_LastName"" = '" & sSelectedItem & "'"
result = oStatement.executeQuery(sSQL)
CursorTest = result.first
REM Check if selection already exists
If CursorTest = "False" Then
REM Name does not exist - add to table
sSQL = "INSERT INTO ""tbl_Members"" (""FirstName_LastName"") VALUES ('" & sSelectedItem & "')"
oStatement.executeUpdate(sSQL)
End If
REM Insure combo box is up-to-date
oForm2.getByName("Combo Box 1").refresh()
REM Set form for selection & retrieve data
oForm.filter = """FirstName_LastName"" = '" & sSelectedItem & "'"
oForm.reload()
End Sub
Not quite certain how you want this process to work. Hope this is what is wanted.
May be your form is incorrect also. Seems the “Members” should be the form and “Certificate” the sub-form.
Yes, dear Ratslinger, thank you so much. I created now a new module there, with your code from above post. But I get an error while running the macro, says, “BASIC runtime error. Argument is not optional.” to the line 20 (attached pic). What could be the problem?
Not quite certain how you want this process to work. Hope this is what is wanted.
For the purpose of simplicity, I would like in a Combo Box of the opened Certicicate form to type a name, if the name is not in the list, then the typed name can be inserted to the name field (column) of the tblMembers table and refresh the form, so i could continue input this member’s date of birth, gender etc., without the need to open the frmAddMembers form to add the data of the member and close the form. Just to save some time. It is very similar to your example of Add entries to listbox or combobox .
May be your form is incorrect also. Seems the “Members” should be the form and “Certificate” the sub-form.
A member can appear multiple times in the tblCertificate table, so the relationship of tblMember of tblCertificate should be 1:n. Thus “Certificate” should be form and “Members” subform. Am I right? I hope it is correct. But I could be wrong. What do you think? Thanks.
Updated: Now after input a name into the Combo Box, there returns many values into the FirstName_Last Name field of the Members table. For example, if I input “Test”, there will be inserted “T”, “Te”, “Tes”, and “Test”…
Certificate1.odb (27.2 KB)
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