[Solved] Designing database forms

[peterswain]

I've used Open Office a little in the past but am by no means fully familiar with it. I wish to populate a database table with data including people's names and addresses, and design a form that will select from that table all the entries that include the name of a particular street. I could set up a separate form for each street, but the number of entries in the table will be quite large and this option would be very time- and memory-consuming.
Is it possible in Open Office database to design a form with a "user-defined-entry" field into which the desired street name could be entered each time before saving the form or - better still - can clicking in the "street name" field produce a drop-down menu of street names from which to select the required street ? I'm using a laptop computer running Microsoft Windows 11 and version 4.1.11 of Open Office, and if either of the above options is feasible I'd welcome some detailed directions on how to set them up.

[Villeroy]

Do you have some list of street names which is mostly complete for your purpose? How big is it?

[peterswain]

There could be up to about 250 names

[Villeroy]

Go to the tables section
Tools>SQL...

Code: Select all

CREATE TABLE "Streets"("N" VARCHAR(32) NOT NULL, "ID" INT IDENTITY);
ALTER TABLE "Streets" ADD CONSTRAINT "UniqStreeetNames" UNIQUE ("N");

menu:View>Refresh tables.
Now you have a new table "Streets" with a name column "N" and an auto-ID as primary key. Enter your street names. This list won't accept any duplicate street names.

In another table where you want to reference these existing street names, add an integer column for the ID from the streets table. This is a foreign key.
Add a relation between the primary key of the streets table and the foreign key in the other table.
Add a query in SQL view:

Code: Select all

SELECT "N","ID" FROM "Streets" ORDER BY "N"

Add a list box to your form where you want to pick a street name.
Data properties of listbox:
Linked field: the foreign key field
Source type: Query
Source: name of the query with the above SELECT statement
Bound field: 1
Now you can pick a street name from a list box (e.g. by typing the first letter names) which writes the corresponding ID number of the selected street name into the foreign key field of your form.
It is not possible to enter any street that is not in the street list.

[Villeroy]

For a less restrictive strategy without any unique list of names, just type the street names into the same table and use a combo box with the following statement:

Code: Select all

SELECT DISTINCT "StreetName" FROM "Table" ORDER BY "StreetName"

where "StreetName" is the column name where you write the names and "Table" is the name of the containing table.
The combo box is more like a text box with auto-complete functionality and an optional drop-down.

[peterswain]

Thanks for your help. Villeroy

[Villeroy]

I tweaked a very old database of mine. Table STRASSEN is a list of all Berlin streets (9,500 streets as of year 2000).
The "Filter Form" allows searching street names by substring or by listbox. I added the listbox today. Since there are many duplicate street names in Berlin, a second field specifies the ZIP codes ("PLZ") a street belongs to.

Table1 is a table where you can store a street's ID number together with a comment.
The form named "Table1" is a form with a listbox where you pick a distinct street by its name. Duplicate names can be distinguished by the appended PLZ numbers. The listbox writes the corresponding ID number into Table1.

Table2 is a table where you can store street names.
The form named "Table2" lets you pick a street name by means of a combobox. The name is written directly into the table.

The database can be downloaded here: https://www.mediafire.com/file/jq7dl1pw ... 7.odb/file