Solved

Delphi Combo Box and MSAccess

Posted on 2006-07-08
10
568 Views
Last Modified: 2010-04-05
Hi,
 
 I am currently converting the forms from an MS Access database to Delphi.  For simplicity sake, I'll only be using two tables:
 
 The Titles table has two fields and three records:
 TitleID   Title
 1           Mr
 2           Mrs
 3           Miss
 
 The Contacts table has four fields and two records:
 ContactID    Title    FirstName     LastName
 1                 1       John              Smith
 2                 3       Jane              William
 
 Ive added an ATOTable control to my form and linked it to the Contacts table.  I've also added a combo box for the titles and linked this up using the field editor.
 
 I then populate the combo box using the following code:
 
 var
   Control_Data : TADOTable;
 begin
   Control_Data := TADOTable.Create(frmMain);
   with Control_Data as TADOTable do
   begin
     Name := 'adoData';
     ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Delphi\Data.mdb;Persist Security Info=False';
     TableName := 'Titles';
   end;
   Control_Data.Open;
   Control_Data.Active;
   while not Control_Data.EOF do
   begin
     cboTitle.Items.Add(Control_Data.FieldByName('Title').AsString);
     Control_Data.Next;
   end;
 
 Now obviously, when the form is loaded the combo box isn't sitting on the correct entry as I store the titles by their ID instead of their String value.
 
 Is there a way that I can link the Title's ID number stored in the Contact table with the TitleID for when I populate the combo box so that the correct entry is displayed when switching between records?
 
 I've spent abit of time looking around for a solution for this without any luck and I feel that it's something simple that I must be missing.  If it isn't as easy as I thought then I am willing to increase the points.
 
 Thanks in advance,
0
Comment
Question by:Sebastion
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 17066691
create a query in the MSAccess database that joins the titles and contacts tables.  This will give your program all the data it needs.
0
 
LVL 7

Expert Comment

by:kfoster11
ID: 17067031
The items list of the combobox has an objects list that coincides with the item list.   Theoretically this is a list of pointers.  A pointer is 4 bytes and so is an integer in win32 so try this.
--------------
var
   Control_Data : TADOTable;
 begin
   Control_Data := TADOTable.Create(frmMain);
   with Control_Data as TADOTable do
   begin
     Name := 'adoData';
     ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Delphi\Data.mdb;Persist Security Info=False';
     TableName := 'Titles';
   end;
   Control_Data.Open;
   Control_Data.Active;
   while not Control_Data.EOF do
   begin
     cboTitle.Items.AddObject(Control_Data.FieldByName('Title').AsString,TObject(Control_Data.FieldByName('ID').AsInteger));
     Control_Data.Next;
   end;

-------------------------

notice the change to your add method.  Now each item in the string list has the id associated with it in the objects list

I use a global function to set my ids throughout my coding.  here it is

Function SetCombo(cb:TComboBox; ID : Integer ) : Boolean;
var
    ndx : Integer;
    tmp : Integer;
begin
    result := False;

    for ndx := 0 to cb.Items.Count-1 do begin
        tmp := Integer(cb.Items.Objects[ndx]);
        if  tmp = ID then begin
            cb.ItemIndex := ndx;
            Result := True;
            Break;
        end;
    end;
end;

------------

now when you want to set the combobox to the string with ID 1 then call
SetCombo(cbWhatever,1);
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 17068629
Why don't you user TDBLookupComboBox? It will save you a lot of code when using standart TComboBox, because you can link the 2 tables without having to write any code at all, just set few properties.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:kfoster11
ID: 17068717
While the TDB controls are easier, one of the rules of programming is to seperate the data from the user interface.  Obviously, a lot of internal applications are developed using these controls, but I have never worked on a product that did.  If you keep the data and the UI seperated then it is much easier to change the UI say to a web interface.

But, as Ivanov_G stated, the TDBlookupComboBox will do the work for you.
0
 

Author Comment

by:Sebastion
ID: 17071581
aikimark,

I was hoping to avoid using Queries as the database in question is in fact quite large (lots of tables, relationships, etc...) and I wouldn't want to create a Query everytime I come across another situation like this.


kfoster11,

I tried your suggestion but I noticed that I would have to trigger the SetCombo function for each time a record navigation takes place to keep the combo boxes updating.  I was hoping for something similiar to MSAccess's "Bind/Show" feature where I could bind a column for data storage and show another column for output and let the record link take over.

Another example is in HTML where one could declare a combo box as:
<option value=1>Mr<option>
<option value=2>Mrs<option>

By the sounds of it, the LookupComboBox might be the way to go.


Ivanov_G,

I have looked at the LookupComboBoxes before but I haven't had much luck with them.

So as to avoid adding an extra TDataSource object to the form for each combo box, I assume it would be best to declare one through the code and simple re-use it for each box?

Would you be able to provide some sample code to populate a LookupComboBox in my example above?
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 17071726
One more TDataSource and TDataSet is not a big deal. You can create new TDataModule for lookups only. Why you didn't had success with it ?

Re-using the same DataSet will not help you if you have 2 lookups on the same form, for example: Title: Mrs, Mr, Mss and Phone Type: Home, Work, Mobile, etc
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 17071779
there are few properties you have to take care about

DataSource - for e.g. CLIENTS
ListSource - for e.g. TITLES (lookup)

properties DataField, KeyField and ListField imply what they are used for.
0
 

Author Comment

by:Sebastion
ID: 17079579
Hi Ivanov_G,

I didnt have much success with it because I may have coded it up incorrectly.

You make it sound so easy, would it be possible for you to provide some sample code for a LookupComboBox?
0
 
LVL 12

Accepted Solution

by:
Ivanov_G earned 125 total points
ID: 17088475
Here it is. Of cource you can do all this without having to write code, just in Object Inspector set the properties

  ADOConnection1.Open;
  // first dataset - main data
  ADOQuery1.SQL.Text := 'SELECT ContractID, ProgramStatusID FROM Contracts';
  ADOQuery1.Open;
  DataSource1.DataSet := ADOQuery1;
  // second dataset - lookup table
  ADOQuery2.SQL.Text := 'SELECT ProgramStatusID, ProgramDescription FROM nom_ProgramStatus';
  ADOQuery2.Open;
  DataSource2.DataSet := ADOQuery2;
  // TDBLookupComboBox
  DBLookupComboBox1.DataSource := DataSource1;
  DBLookupComboBox1.ListSource := DataSource2;
  DBLookupComboBox1.DataField := 'ProgramStatusID';
  DBLookupComboBox1.ListField := 'ProgramDescription ';
  DBLookupComboBox1.KeyField := 'ProgramStatusID';
0
 

Author Comment

by:Sebastion
ID: 17107393
Ivanov_G ,

Thanks for the sample code.  I made some adjustments and I managed to get the thing working.  I was on the right track with the LookupComboBoxes, it's nice to finally get the thing working.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question