Title: How to use Look Up fields with Tables /Queries
1) How to place a combobox type look up field?
Scope
We have an order table with the IDnumber of an employee (the vendor). We want to see the name of that employee, rather then just a number. In our order table only the ID number of the employee is defined, but the name and other data of the workers is stored in the table employee
Solution
Place on a form:
a) a TDatabase
properties: AliasName = DBDEMOS, Connected = True, DatabaseName = myDATA
b) a TTable //this is the main table
properties: DatabaseName = myDATA, TableName = orders.db, Active = true
Name = tblMain
obs: in Case of a Query set the SQL propertie to: Select OrderNo, CustNo, EmpNo, SaleDate, Itemstotal FROM "orders.db" Orders
Right Click on the tblMain or qryMain and add the fields:
OrderNo, EmpNo, SaleDate, EmpNo, ItemsTotal and AmountPaid
Drag the just placed fields onto the form, and Delphi will automaticaly put a dbEdit and label for the fields. Also a datasource is automaticaly placed, rename it to dsMain.
Place a dbNavigator on the Form and put its datasource property to dsMain
Run the form, and you have an input form for the order tables. This form has several embedded dangers. The EmpNo field can be modified, in other words, a non existent employee can be entered. Only when we are linking the employee with the order table this will be evident.
Solution: set the readonly property of EmpNo to true
The actual look-up process
Drop a TTable //this is the lookup table
properties: DatabaseName = myDATA, TableName = employee.db, Active = true
Name = tblLookUp
Drop a Datasource
properties: Dataset = tblLookUp
name = dsLookUp
Right Click on tblLookUp and add the fields EmpNo, LastName and FirstName with the the fields editor.
drop a DBLookupComboBox and a DBLookUpListbox on the form and select both of them
properties: DataSource = dsMain (main table)
DataField = Empno
the lookup part
ListSource = dsLookUp (look up table)
KeyField = EmpNo (this is the link to main table, here are only elements from the main table)
ListField = LastName (What will be displayed in the combobox, the elements of the Lookup table) for the dbLookUpCombobox but
ListField = EmpNo;FirstName;LastName for the dbLookUplistbox
You want to screw things up?
Set the readonly property of the EmpNo dbedit field to false, and enter 0 or another non existent employee number in the dbEditBox. The link with the look-Up table will be lost.
Regards