Solved

Delphi Combo Box and MSAccess

Posted on 2006-07-08
10
559 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
  • 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now