Mega Code Archive

How to use Look Up fields with Tables Queries

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