Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 589
  • 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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