New to Base - question about how form shows up in database

[charlesvk]

Hi,

I´m new to Base and don´t know if this topic has been dealt with before, sorry. I hope that I can make my problem clear.

Situation: I am making a database for specific plants. I made several sub databases for repeating plant characteristics. I created relationships between them. I made a form to enter the plants in the database. Embedded the sub databases in the form. When I make a record and choose a characteristic from the sub databases (roll down in the form), in the main database a number shows up for that characteristic and not the actual characteristic.

Let´s say the sub database is colour. "Yellow" is the second color I entered in that database.

In the form I choose yellow.

Next, in the main database under colour it says 2 and not yellow.

Can I change this, because this makes my database hard to read? Or can´t I and do I have to scroll through the records to see this?

I hope that you understand and can help me out.

Kind regards,

Charles

[UnklDonald418]

A List box control will do what you want.
there is a tutorial
Combo vs List Box
In your case you don't need to worry much about combo boxes, but it's good to understand the differences.

To begin with, write a query something like

Code: Select all

SELECT "ColorName", "ColorID" from "tblColors" order by "ColorName"

You will have to substitute the specific column and table names from your database.
When you Run it, It should display an alphabetized list of all your colors with the color number next to it.
Save that query

Open your form in the Edit/Design mode.
Right click on the form control displaying the color number. If it's a Column in a table right click on the heading for the column displaying the color number.
from the menu select Replace with > List box
Right click again,
if the form control displaying the number is a stand alone control select Control
if it is a table column, select Column.
This should open a Properties: List box dialog
On the Data tab
change Type of list contents to Query
from List content select the name of the query you entered above.
Save the form
Turn the Design mode Off
Now your form should display the color matching the number you saw before.

[charlesvk]

Hi, Thanks for your reply. I tried making the query. In my case it would be:

SELECT "KleurNaam", "KleurID" FROM "tblKleur" ORDER BY "KleurNaam"

(it changed to capitals automatically. I don´t know whether that´s a thing?)

But I get this message when I run the query:

SQL-status: S0002
Foutcode: -22

Table not found in statement [SELECT "KleurNaam", "KleurID" FROM "tblKleur" ORDER BY "KleurNaam"]

So I guess I must be doing something wrong? In the screenshot you can see the table, if that´s any help.

Kind regards, Charles

[UnklDonald418]

Based on your screenshot, try

Code: Select all

SELECT "KleurNaam", "KleurID" FROM "Kleur" ORDER BY "KleurNaam"

[charlesvk]

Thanks, this fixes the query; it now runs and shows the list. I´m afraid this doesn´t solve the question I had though.

In the form the names of the colours were visible already. It is the main database that (still) shows the numbers for all the list box columns. See screenshot please.

And I understand that (if this can be solved with a query) a query might have to be made for each column that has this problem.

[UnklDonald418]

The Table Data View displays raw data, getting that data directly from the HSQL Database Engine and bypassing the Base front end, so List box controls are not available.

The Table Data View is handy for troubleshooting, but it has very few formatting options, so is not really suitable for general use.
Create a Base form from "Planten Hertenvraat", for this simple case the Form wizard is more than adequate. On the form you can change the column "Kleur" to a Listbox as described earlier and then it should display the color name.

By the way, including non-printing characters like spaces in Table and Column names is bad form and can to lead to problems. It is better to use an underscore if you want to include a word separator i.e. "Planten_Hertenvraat" or just do without "PlantenHertenvratt".

[charlesvk]

Ok, I see. Well, thanks once more. I appreciate your help.

Kind regards, Charles

[Villeroy]

Base tutorials and examples: viewforum.php?f=83

In particular: [Example] Relations reflected by list boxes in forms