We shall begin once again by creating a database in Access to provide a source of information on invoices and the items on each one. Create a new database in a directory and then create a table for invoice data like this:
Now create a table for the invoice items like this:
Set up the relationships between the tables:
Notice once again the use of two different names for the same data item in the two tables, 'Invoice ID' for the primary key in 'Invoices' and 'Invoice' in 'Items'; this is to ensure that the Master Fields setting will work correctly. Note also that the second table in particular has NOT been normalised, there are issues with the Description and Unit Cost fields that will be sorted out in Part 3 of this tutorial.
From the relationships diagram we see that each invoice has many items. Enter some sample data and then close the database and turn to Delphi. This time we will ignore the tables as separate items and move straight to the form where we will place both tables.
This time we can use the navigator bar because only one record at a time is visible. To add a new record click on the '+' icon and fill in the blank fields. Notice that the ID field, an Autonumber, is set to ReadOnly and its value is set when you click either the 'Post' button (the 'tick' icon) or the Refresh button (the 'curly arrow' icon). The Post button saves the data but does not add a Total Cost while the Refresh button updates the display and does add the Total Cost, albeit set to zero. You can look at the Access database to check that the new data is there.
When the actions above are carried out the data in currency fields is unformatted. To display currency items in the correct form proceed as follows:
Double click the ADOTable component for the item concerned and select it from the list of fields added earlier:
Now find its DisplayFormat property in the Object Inspector and set this to '£'#.00. Repeat this for any other currency fields (there are two in the Items table).
Rather than work out and enter the Total Cost of an item ourselves we should arrange to have the computer do this for us. We can achieve this by catching the OnChange event for the Quantity field in the Items table so that, when the user enters a value the required calculation is performed and the data is entered. To do this, select the Quantity field in the field list for ADOTable2 and double click the OnChange property in the Property Inspector. The code for the calculation is as follows:
procedure TForm1.ADOTable2QuantityChange(Sender: TField);
begin
ADOTable2TotalCost.Value:=ADOTable2UnitCost.Value * ADOTable2Quantity.Value;
end;
We also need to calculate the total value of all the items on an invoice, which will not produce a result until one or more items have been entered on the Items list. Unfortunately we cannot attach the code to update the total value in table 1 from the code that calculates the total cost of an item in table 2 (this produces an error). Instead we have to find other events on the form such as a click on the navigator bars:
procedure TForm1.DBNavigator2Click(Sender: TObject; Button: TNavigateBtn);
var totalvalue:real;
begin
ADOTable2.DisableControls;
try
totalvalue:=0;
ADOTable2.First;
while not ADOTable2.Eof do
begin
totalvalue := totalvalue+ADOTable2TotalCost.value;
ADOTable2.Next;
end;
with ADOTable1 do
begin
Edit;
FieldValues['Total Cost'] := totalvalue;
Post;
Refresh;
end;
finally
ADOTable2.enablecontrols;
end;
end;
This code will be run when the user clicks on the navigator bar for the data grid, which is what happens when the user clicks the Post ('tick') button to send the record to the database or the New button ('+') to create a new record.
We can attach the same code to DBNavigator1 so that the total for the current record is calculated before a new one is created. The user might close the form without clicking either of the navigator bars so we should attach the same code to the form's OnClose event and we might also attach it to the form's OnActivate event or the Table's AfterOpen event as well, though these last two are not really necessary.
Notice the code at the end of the procedure that updates the Total Cost field in Table 1:
with ADOTable1 do
begin
Edit;
FieldValues['Total Cost'] := totalvalue;
Post;
Refresh;
end;
The three methods shown above, Edit, Post and Refresh, are taken from the TDataSet object. The Edit method is used to activate editing for the active record in a dataset. Editing cannot proceed unless the CanModify property of the dataset is True. The FieldValues property provides access to individual fields of a dataset. The Post method writes a record to the dataset and the Refresh method fetches data from the database to renew the display.
One further refinement we can make to the form is to add a 'Record m of n' label so that the user can see their position in the dataset. A dataset has the properties RecNo and RecordCount, which are updated when the user scrolls from one record to the next. When a new record is being added, before it has been posted, the value of RecNo is -1, but we would not want to display this. Instead, we use RecordCount +1, as if the new record had already been posted. The code is attached to the AfterScroll event of ADOTable1:
procedure TForm1.ADOTable1AfterScroll(DataSet: TDataSet);
begin
if ADOTable1.RecNo = -1 then
Label10.Caption:='Record ' +
inttostr(ADOTable1.RecordCount + 1) + ' of ' +
inttostr(ADOTable1.RecordCount + 1)
else
Label10.Caption:= 'Record ' + inttostr(ADOTable1.RecNo) +
' of ' + inttostr(ADOTable1.RecordCount);
end;
We should set those fields that take automatic or calculated values to read only: Invoice Number, Item ID, Invoice.TotalCost, but NOT Item.TotalCost, this produces an error.
Our form so far should look something like this:
It would be very useful if we could find records in the datasets based on search criteria such as name of payee (customer) or some other property. To achieve this we can use an ADOQuery. Set up a new form, as shown below, and add an ADOQuery component, a DataSource and a DBGrid. Add Unit1 to the Uses part of Unit2 and set the Connection property of the ADOQuery to Form1.ADOConnection1. Set the DataSet property of the DataSource to ADOQuery1 and set the DataSource property of the DBGrid to DataSource1.
To generate the SQL you can open the database in Access and create a simple query like this:
Choose View/SQL and copy the code ready for pasting:
SELECT Invoices.[Invoice ID], Invoices.Date, Invoices.Payee, Invoices.[Total Cost]
FROM Invoices;
Back in Delphi, select the ADOQuery component, find the SQL property and click on the dieresis to open the String List Editor and paste the SQL code into it.
Notice that a line has been added: WHERE Payee = :payer;
':payer' is a parameter, which will be passed from the EditBox to the SQL code when the query is run. To add the parameter to the query click on the dieresis in the Parameters property. The parameter should be in the edit window but if it is not right click the window, choose 'Add' from the popup menu and change the Name property to match what you have used in the SQL code (select the name in the list and use the Object Inspector).
Click on the parameter to select it and change its DataType property in the Object Inspector to the field type which matches it most closely.
Double click the button and add this code to its Click procedure:
procedure TForm2.Button1Click(Sender: TObject);
begin
ADOQuery1.Parameters[0].Value:=edit1.Text;
ADOQuery1.Open;
ADOQuery1.Requery;
if ADOQuery1.RecordCount=0 then showmessage ('No records found');
end;
This code is a simple sequence of operations using properties and methods of the ADOQuery object:
Here is a specimen search:
The various search routines can be added to a menu structure in the normal way. Here is a copy of the main form during processing:
