Limit data entry libreoffice base

samuel_ribeiro · August 9, 2023, 1:36pm

I have this form where the user can entry a color of car in a combobox that is linked to the CarColor column, but I want it in a way that the user cant enter a color that he has entered before.

Example: the user typed White some days ago, but now he has forgotten that he typed it, and when he tries to type White on the combobox again, I want it to pop up an error window because that value is already on the table.

Basically I need a way to limit a combobox based on the table column that it is linked to.

Im willing to use macros.

I just need this bcz Im not the one who will be using the database, so i need to limit the data that enters it. This “DBColor” table is linked to the “DBCar” table, where I have a listbox of the colors that were input, but i need the user to enter them, bcz I dont know the colors of the cars that he will need to use.

I want something that looks like this:

REM  *****  BASIC  *****

Sub LIMITDATAENTRY

forms = ThisComponent.DrawPage.Forms.getByName("MainForm")
comboboxColor = forms.getByName("comboboxColor")
comboboxText = comboboxColor.Text

if comboboxText <> values on column color
then let the user add the value
else error message duplicate value

end if

end sub

Villeroy · August 9, 2023, 2:40pm

Poor table design, I guess.

Macros make poor table design even worse.

samuel_ribeiro · August 9, 2023, 2:46pm

all the tables are normalized properly, I just need this bcz Im not the one who will be using the database, so i need to limit the data that enters it. This “DBColor” table is linked to the “DBCar” table, where I have a listbox of the colors that were input, but i need the user to enter them, bcz I dont know the colors of the cars that he will need to use

RobertG · August 9, 2023, 2:54pm

Without any example: Why don’t you set the field as UNIQUE? Then you will get an error when choosing the same value the second time.

samuel_ribeiro · August 9, 2023, 4:33pm

how can i do that?

RobertG · August 9, 2023, 4:47pm

Open the table for editing, not for input data.
Tools → Index Design
Choose New Index. In Index Details choose UNIQUE and the field, which should be UNIQUE.
If you want to close the dialog you will be asked for saving. Save this index.
You could also put two fields together in this index. If you will only allow unique colors for one user, you might allow the same colors for other users. Then the fields “Color” and “User” should be together in one index.

samuel_ribeiro · August 9, 2023, 5:16pm

It worked, thanks, I think a lot of the base users dont even know this tool exists, great knowledge.