To implement the library system first developed with data files in an ADO environment we first need to set up the tables in Access:
Note that we have added two further fields to the database to record the fines due on an overdue loan and the total fines owing by a borrower.
We can now implement these Access tables in Delphi. On the first form we set up an ADO connection to the database. In the code we add the other forms so we can access them:
implementation
uses unit2, unit4, unit5; //or whatever they
are called
We also add some buttons to take us to the three forms in the system. The code for the buttons is:
procedure TForm3.Button1Click(Sender: TObject);
begin
form2.visible:=true;
form3.visible:=false;
end;
It is a good idea to set the Login property to False so that you don't get the Login dialogue box every time you try to use the connection control.
We can now set up forms for each table. First we need to add code to include the base form:
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ExtCtrls, DBCtrls, DB, StdCtrls, Mask, ADODB, unit3 ;
Note that unit3 is included in the first uses section for this module rather than in the implementation section.
Next we add an ADOTable control and set its Connection property to the ADOConnection we set up on the first form. Without the uses unit 3 (the home form) this connection would not be available. Choose the table we want (borrowers). We then double-click the ADOTable icon and add all the fields from the form that opens (right-click it). Select all the fields on the form and drag them onto the main form. Add a DBNavigator control from the Data Controls palette and set its data connection property to DataSource1 (this control was added automatically when the fields were added to the table control). Set the Active property of the ADOTable control to True - the data should appear in the controls.
The Borrowers control can be made read-only as the value will be calculated automatically.
Repeat the above actions for the Library Items and Loans forms.
The navigation bar can be used on each form to add, edit and delete new records. The fine field can be set to read-only as the fine will be calculated automatically.
The DateDueBack field can be changed to a DateTimePicker control so that it is easier to choose and enter a valid date. The existing control, DBEdit3, is retained but made invisible. When the date changes the date chosen is copied into the DBEdit3 box so that it is still available for saving in the database.
When the value in the DateTimePicker changes we want the original text field to be updated by moving the date into it. This will then be copied into the database:
procedure TForm4.DateTimePicker1Change(Sender: TObject);
begin
dbedit3.text:=datetostr(datetimepicker1.Date);
end;
Notice that the date field is set text so we use the datetostr function to turn the date output of the DateTimePicker into text. If we had set the Access field to a date (a better strategy!) then we would not need the conversion function.
To create a loan we can use the DBNavigator control.
To display hints on the navigator bar set the ShowHint property to True.
To change a date click the Edit button on the Navigator bar and change the date. Click the tick to change the entry in the datbase.
One possible routine for the system might be to display a list of items on loan to each borrower. To do this we need to open the borrowers table and, for each one, search the loans table for a match between the borrower ID. When a match is found we have to open the items table and search for the item details so we can display the title and author.
Create a new form and add ADOTable controls for all three tables. Add the code to include the home form and set up the link to the ADOConnection control. Add all the fields for each table. Add a memo to the form. We will create the report of loans in the memo. The memo will be filled with the report data when the form is opened.
To display loan details we need a form with a memo and a routine that will pick out all borrowers in turn, pick up the codes of their loans from the loans table and pick up the item details of each loan from the items table. The table design might look like this:
The code might look like this:
procedure TForm6.FormCreate(Sender: TObject);
begin
AdoBorrowersTable.Open;
while not AdoBorrowersTable.Eof do
begin
ADOLoansTable.Open; //find loans for
each borrower
while not ADOLoansTable.Eof do
begin
if AdoBorrowersTableBorrowerCode.value = ADOLoansTableBorrowerCode.Value then
begin
memo1.Lines.Add(AdoBorrowersTableBorrowerName.Value);
memo1.Lines.add(ADOLoansTableAccessionCode.Value);
ADOItemsTable.Open; //lookup
loan item details
ADOItemsTable.Locate('AccessionCode',ADOLoansTableAccessionCode.value,[]);
memo1.Lines.add(ADOItemsTableTitle.Value);
//item details found
memo1.Lines.add(ADOItemsTableAuthor.Value);
ADOItemsTable.Close;
end;
ADOLoansTable.Next;
end;
AdoBorrowersTable.Next;
end;
end;
Note how the field values from the tables are extracted by writing them immediately after the table name, followed by the 'value' property, for example: ADOTable3Title.Value.
We can print the contents of a memo with a standard printing routine.
You could replace any field on the form with a comboBox. The contents of the comboBox can be read from any table in the database so you could choose from a list of borrowers or books on the respective form (in a real library these tables would be enormous and not suitable for use in a combo box: combo boxes would be used for a list of items of limited length e.g. football teams. Combo boxes are typically created when a form is created or opened using the OnCreate or OnOpen events.
See the music example for more details.
This is a common task in database applications. Users will often want to serarch for an item such as a book or author. One option is to run a search when an item in a combo box is chosen while another is to use a text box and a button to start the search.
A more advanced technique involves the use of a TADOQuery and a piece of SQL code.
See the music example for more details.