Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Database Programming

How to use Look Up fields with Tables /Queries by svanels
Posted: 26 Oct 01 (Edited 8 Aug 03)

1) How to place a combobox type look up field?

    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


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.


Back to Embarcadero: Delphi FAQ Index
Back to Embarcadero: Delphi Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close