Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Delphi Combo Box and MSAccess

Posted on 2006-07-08
10
Medium Priority
?
579 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 46

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 500 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

722 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