Auto-complete in DBEdit? 1

[Faded]

Hello!

I'm pretty new to Delphi, so please bare with me. I'm using Delphi 5 Pro.

I have a master/detail scenario on a form. When selected via a DBNavigator, the form displays a tour company with all of it's addressing and contact information (the master), as well as all of the tour groups (the detail) that company has booked.

The table containing the Master information has about 150 records and therefore is very tedious to sift through one at a time (even though they are sorted alphabetically in the TQuery) to find a specific company. I would like to put an auto-complete DBEdit or DBListBox or something that enables a user to start typing a company name in the field and the closest matching company name will appear.

ex: In the DBEdit (or whatever) you type "d" and the company "Dale's Tours" comes up because it is the first "D" alphabetically. Then as soon as you add the next letter "o" the company will change to "Don's Tours" etc.

Any suggestions on how I can accomplish this would be greatly appreciated.

Thanks, Faded

 

[RSi2]

put in the OnChange event of an Edit a Filter on your Query ... this will restrain the records that are shown, to those correponding to your "mask"
clear the Edit to view all the records

 

[Faded]

Thanks RSi2, but since a DBEdit doesn't have a filter property, I tried the following from the Change event of the DBEDit:

procedure TfrmMasterList.dbeCompanyLookupChange(Sender: TObject);
begin
with dmMasterList do //DataModule containing qryMasterGroup
begin
qryMasterGroup.Filter := dbeCompanyLookup.Text
end;

end;

When the program is running and I type "d" in the DBEdit, I get an error something like "qryMasterList field d not found"

I also tried:

begin
qryMasterGroup.FieldByName("companyName&quot.???
end;

but the fieldbyname doesn't have a filter property either.

Sorry, but could you be more specific in how to handle this.

Thanks alot, Faded

 

[LucieLastic]

Use a TDBComboBox and set the Style property to csDropDownList.

This should jump to relevant companies automatically.

lou

 

[Faded]

Uurgh! Thanks Lou!

So I dropped a TDBComboBox on the form, set it's DataSource and DataField properties as well as the Style property to csDropDownList. I checked that the TQuery is active, and when I run the app, the Combo is showing nothing. when I try to enter something I see the "SQL Hourglass" cursor briefly and then nothing.

??

Faded

 

[LucieLastic]

Bugger! sorry couldn't get it to work either, sure I'd done it that way before though, apologies.

Alternatively, you could use a TCombobox and populate it's items property with the field values, say, in the onShow event. Then if you have a 'retrieve info' button, it loads up the relevant data.

I personally would have a 'retrieve button' as it can get annoying for the user if the data is constantly being filtered when they're trying to type something in.

Hope that's a bit more helpful )

 

[svanels]

The dbCombobox and the dbListbox have the autocomplete property.

But this is only usefull when entering a new record. For a master-detail query with more then 150 records, I would modify the the query at run-time.

Imagine you have a big table, you need to give the end user search capability and network trafic is a problem.
In this case we can use an TComboBox to determine which column to search and TEditBox for the search criteria.

Normally I group the ComboBox, the editbox and the search button on a separate toolbar.

Example

table structure:

| CODE | NAME | LOCATION | GENDER |

Needed

1) a form

2) TQuery named MyQuery

3) TDBGrid or other db aware fields, TDataSource, DataModule etc.

4) TEditBox named edtSelect

5) TComboBox named cbSelect

6) TBitButton named bbtnSelect (to fire the query)

7) Some global variables
SString --> string
RecordsRetrieved --> integer

8) A statusbar (optional)


The Query is a parametrized type.

Properties:

SQL = Select CODE, NAME, LOCATION, TYPE from
MYTABLE MYTABLE
where CODE like :SearchString

Active = false

Parameters --> SearchString --> ftString



As mentioned before, cbSelect will indicate the column searched.
In the sql statement the column is located after 'where'

Items of cbSelect:
CODE
NAME
LOCATION
GENDER


Events:

On form create

procedure TForm1.FormCreate(Sender: TObject);
begin
inherited;
SString :='%%';
cbSelect.ItemIndex := 0;
end;


Typing in the search criteria in the TeditBox

procedure TForm1.edtSelectChange(Sender: TObject);
begin
inherited;
SString := '%' + edtSelect.Text + '%';
end;


Firing the query

procedure TForm1.bbtnSelectClick(Sender: TObject);
begin
inherited;
with Myquery do
begin
close;
SQL.Clear;
SQL.Add('SELECT CODE, NAME, LOCATION, TYPE');
SQL.Add('FROM MYTABLE MYTABLE');

case cbSelect.ItemIndex of
0: begin
SQL.Add('WHERE CODE LIKE :SearchString');
ParamByName('SearchString').AsString := SString;
end;
1: begin
SQL.Add('WHERE NAME LIKE :SearchString');
ParamByName('SearchString').AsString := SString;
end;
2: begin
SQL.Add('WHERE LOCATION LIKE :SearchString');
ParamByName('SearchString').AsString := SString;
end;
3: begin
SString := 'M';
edtSelect.Text:= 'M';
SQL.Add('WHERE GENDER = "M"');
end;
4: begin
SString := 'F';
edtSelect.Text:= 'F';
SQL.Add('WHERE GENDER = "F"');
end;
End; {case}

SQL.Add('ORDER BY CODE, GENDER');
open;

end; { with }


end;


The user do not have to use wildcards to find for example edward, any character will satisfy the search. Leaving the edtSelect blank, will retrieve all the records.
Steven van Els
SAvanEls@cq-link.sr

 

[Faded]

Steven,

Thanks for the SWEET example. It works great, and was very easy to follow. It's not exactly what I was thinking of, but will work brilliantly in place of what I was thinking of.

Cheers, Faded

 

[LucieLastic]

You don't really need to use the parametised query, just concatenate the strings directly

ie


SQL.Add('WHERE LOCATION LIKE :SearchString');
ParamByName('SearchString').AsString := SString;

becomes


SQL.Add('WHERE LOCATION LIKE '+SString);


On a side: in the past I've found using parambyname much slower than rewriting the sql line, anyone experienced this themselves?

lou