Sebastion
asked on
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.OL
TableName := 'Titles';
end;
Control_Data.Open;
Control_Data.Active;
while not Control_Data.EOF do
begin
cboTitle.Items.Add(Control
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,
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.OL
TableName := 'Titles';
end;
Control_Data.Open;
Control_Data.Active;
while not Control_Data.EOF do
begin
cboTitle.Items.Add(Control
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,
aikimark
create a query in the MSAccess database that joins the titles and contacts tables. This will give your program all the data it needs.
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.OL
TableName := 'Titles';
end;
Control_Data.Open;
Control_Data.Active;
while not Control_Data.EOF do
begin
cboTitle.Items.AddObject(C
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[n
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);
--------------
var
Control_Data : TADOTable;
begin
Control_Data := TADOTable.Create(frmMain);
with Control_Data as TADOTable do
begin
Name := 'adoData';
ConnectionString := 'Provider=Microsoft.Jet.OL
TableName := 'Titles';
end;
Control_Data.Open;
Control_Data.Active;
while not Control_Data.EOF do
begin
cboTitle.Items.AddObject(C
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[n
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);
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.
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.
But, as Ivanov_G stated, the TDBlookupComboBox will do the work for you.
ASKER
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?
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?
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
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
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.
DataSource - for e.g. CLIENTS
ListSource - for e.g. TITLES (lookup)
properties DataField, KeyField and ListField imply what they are used for.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.