Lookup Fields

[SwissMac]

How can I put a Lookup field into a Form? Eg instead of typing the name of the country every time an address is entered or typing out Telephone or Phone instead of looking the correct method of contacting up, I want a drop down list of countries/contact methods that are read from a seperate Lookup table and displayed in the form for the main form for entering personal data.

All help welcome

TIA

Mac

[freemc]

if your form is in spreadsheet style, you can right-click the column header, and select change to... Then choose either listbox or combobox based on if you want to allow new values to be added.
_________________
Can't win if you don't play

[SwissMac]

No, it's a Form. And they're really hard to manually edit!

Mac

[daddieke]

I hope I understood the problem correctly.

Link the data field to the correct field in the source table and write a query for the list content of your box.

In your case the source table might be something like t_address with several fields among which a field country. In the address form provide a combo box for the country field. If you already have a text field there is an option to replace it by another type of control. In the combo box properties select country as data field. Suppose you have a catalog table tc_country listing all countries in a field country, you then write a query like "select country from tc_country order by country" and enter it in the property list content of your combobox. As type of list contents you have to select SQL. Now you can select a country and the selection will be entered in the country field of the address.

[SwissMac]

Thanks daddieke, your idea gave me some success but I haven't had to enter an SQL statement anywhere yet, nor found anywhere that looked like an obvious place where I should. Having said that, when I view the form the field I denoted for the combo box does show the contents of the field I wanted it to from the lookup table. I did create a relationship between the two tables though I know this shouldn't be necessary as this can be done in an SQL statement.

I selected the option "save in field of database table" rather than "show only in form" so I hope this was right.

Where should I add the SQL statement? I'm a bit lost there. And should it say something to link the two tables along the lines of:

[daddieke]

Mac,

Now I'm a bit lost. I'll try to tell you in more detail how I proceed.

Open form in edit mode. Select the combo box and right click. Choose the option "control" which opens the properties window for the combo box. If you're dealing with a table control in a subform, choose the option "column" which gives you the same properties window. Select the tab "data".

There you find several properties :
Data field = The field where the selected value must be entered. If a value was already entered, it is displayed in the box. In your case this is the country you selected or entered in the address itself.
Empty string is null = Boolean to select if an empty string should be considered null at db level or not (never mind).
Type of list contents = What type of list represents the values presented for selection. You can select "table" if your table only has the values you need. Else you better select "sql" to enter any query you like. Tablefields gives you a list of column names for the table you select, this is not what you need.
List content = The description of the values presented for selection in the box. This is your catalog, in your case the list of countries. It can be the name of the table containing the values if you have selected "table" before or it can be an sql expression if you selected "sql". There should be no link to the destination table and field where the selected value will be stored.

I hope this helps. If it doesn't, let me know and I'll send you an example so we can discuss more specificly.

Wouter
[/list]

[SwissMac]

Thanks Wouter, I think I could follow what you are saying if it represented what I can see on my form, but it doesn't.

I created a new database with 2 tables and limited fields in then created a relationship between my two tables, then created the form and when I went to the properties dialogue for the form to try and recreate what you are saying there were already entries in there for some of the things you mentioned. I've now created four different databases to test out this functionality, and I can tell you I wish it were easier to format and edit the form!

Also, the kind of SQL statement that is automatically created is different to that which I am used to with MySQL. Can I have a look at your example somewhere please?

TIA

Mac