• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 598
  • Last Modified:

Delphi Combo Box and MSAccess

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
Sebastion
Asked:
Sebastion
  • 4
  • 3
  • 2
  • +1
1 Solution
 
aikimarkCommented:
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
 
Kyle FosterCEOCommented:
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
 
Ivanov_GCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Kyle FosterCEOCommented:
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
 
SebastionAuthor Commented:
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
 
Ivanov_GCommented:
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
 
Ivanov_GCommented:
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
 
SebastionAuthor Commented:
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
 
Ivanov_GCommented:
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
 
SebastionAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now