Problems with Combo Box in Base

[pauly]

Hi there! I am new to Base, and am overjoyed about and grateful to this friendly community.

Today while learning how to make forms, I came across a problem with Combo Box.

I made 2 tables: tbl_Members and tbl_Certificate
I made 1 form: frm_Certificate
In the form frm_Certificate, there are 4 fields: Certificate_ID, Register_No, Start_Date, End_Date from the main form and FirstName_LastName, Gender, Birth_Date and Address from the sub form.
The .odb file is in the attachment.

(What I found with big surprise is that a form in Base is not a form but something like a big container or canvas where many forms, subforms and others controls can be put all inside)

In the form frm_Certificate I add a List Box and a Refresh button for select members (Refresh button is for the case of modification). Both worked well. Then I tried many times to change the List Box to a Combo Box, so I can input the new members’ first names and last names directly through a Combo Box, thus to some time, namely no need to open a members form to add new members. But I failed. The Combo Box was created exactly like the List Box. It was created in the form without the wizard, manually set “Data filed” to “Member_ID”, “Type of list contents” to “sql”, “List content” to SELECT “FirstName_LastName”, “Member_ID” FROM “tbl_Members”. The result is, when I select any member and then refresh the form, the Member_ID in the Combo Box always turns to “0”, which is the first member. Beside that, there is no possibility to input new members’ first name and last name in the Combo Box.

How is that the Combo Box doesn’t function as expected?
Where did I make mistakes?

Many thanks for all of your insight and kindness!

Certificate1.odb (23.2 KB)

[Villeroy]

Have a look at http://forum.openoffice.org/en/forum/download/file.php?id=2879 which demonstrates a many-to-many relation (each movie can belong to many genres) plus an additional form where you can add a genre. The hierarchy of forms and subforms can be arbitrary complex. You need to use the form navigator from toolbar “Form Design”.
The extra form for new genres belongs to the “Forms” node which is the root of the hierarchy. The etra form is set up to take new records only.
The default button saves the record (should better jump to next new entry).
When you focus the list box of genres again, the second refresh button on the navi toolbar refreshes the list box to include the new entries.

After you got this manual setup right, https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=88831 is a most simple Python macro which can auto-refresh any element(s) in a form hierarchy whenever a record set has changed. It works without customization. Setup is done by means of configuration data in a “hidden field”.

[Villeroy]

OK, OK, that old example is not optimal. I fixed it and added a macro driven one.
Now it has 3 forms:

1. “Movie_flat” writes to table “Movie_flat” with ganres as direct text values written with a liitle help from a combo box.
2. The relational “Movie” form where you can enter a new genre name, hit enter, click the subform with the movie’s assuciated genres, refresh that listbox and add the new genre (or refresh the entire subform).
3. The relational “Movie_AutoRefresh” form where you can enter a new genre name, hit enter and add the new genre because a macro inserts the new genre name to the listbox.

---

In order to install my AutoRefresh macro

1. Tools>Options>Security>[Macro Security…], choose highest security level and add a trusted directory where you put macro driven documents.
2. download the *.odt file from above linked forum topic, move it to the trusted directory, open it and click the install button. That’s it.
3. Put the database into that directory as well. It does not embed any macro code but it is prepared to call the code you have installed in step 2.
4. now you can use form “Movie_AutoRefresh”.

[Villeroy]

I can’t upload files anymore. Here is a link to the new movies_genres database:

movies_genres

[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.