Problems with Combo Box in Base

[pauly]

Dear Villeroy, thank you so much!!! I tried download the Tiny Macro Refreshing Forms, List/Combo Boxes and movies_genres.odb file. I installed the Macro… it works like a charm. And because of you I found the forum.openoffice.org forum, lots of useful materials and inspirations there. I didn’t know it before.

Did you read Ratslinger’s reply. He offered a solution which even doesn’t need to install anything, which is more simple and safer to some degree.

Anyway, I appreciated very much your insight and help. Thanks again very much with respect.

[Villeroy]

A LibreOffice combo box is just a text box with auto-complete. It can not map any relations.
You want a listbox.
Create a query with 2 columns

SELECT "FirstName_LastName", "Member_ID" FROM "tbl_Members" ORDER BY "FirstName_LastName" ASC

Replace the combo with a list box.
Linked field: Member_ID (of tblCertificate in your form)
Source type: Query
Source: the query
Bound column:: 1 (which is the second one. The visible 1st one would be 0)

The list box will display the first field of member names and write the bound column’s primary key to the linked field’s foreign key.

[pauly]

Thank you Villeroy!
Yes, the List Box according to your suggestion works perfectly.
But what I want to achive is the possibility to directly input the members’ first names and last names here in this box. Namely to write down a first name and last name, if they are not inside the list, then an event will be triggered so the first name and last name can be right inserted into the “FirstName_LastName” column of the table tbl_Members, then refresh the form.
I did it long time ago inside Access, but I don’t know if it is doable with Base. I hope I made myself understood. Any help will be much appreciated.

[Villeroy]

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.

[pauly]

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.

[Ratslinger]

Hello,

@pauly

The Combo Box was created exactly like the List Box

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?

[pauly]

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)

[Ratslinger]

@pauly

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.

[pauly]

@Ratslinger

Create a new module in the Standard library there

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)

[Ratslinger]

@pauly

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.

[Ratslinger]

@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)

[pauly]

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!!!

[Ratslinger]

@pauly

so John’s name can appear multiple times in the Certificate table.

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.

[pauly]

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.

[Ratslinger]

@pauly

student can have more certificates

Try this → Certificate1PostedModified.odb (16.5 KB)

On the Combo, hit enter to select.

[pauly]

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.

[Ratslinger]

@pauly

Members form will be a sub form of the MainForm,

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.

[Villeroy]

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.

[pauly]

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)

[Villeroy]

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.