DBLookupComboBox enigma

[Delphard]

Two DBLookupComboBoxes on the Form: DBLookupComboBox1 connected with "parent" Table (for instance: Countries), DBLookupComboBox2 with "child" Table (for instance: Cities).
DBLookupComboBox1 is just for easier selection (has just ListSource/ListField), and DBLookupComboBox2 actually writes (has DataSource/DataField) in the third Table (for instance: Customers). Now:
How to "enforce" DBLookupComboBox1 to display correct Country, when Customers are browsed.

Thanks in advance!

 

[hilfy]

What types of data controls are you using?

You might be able to do this by setting up a master/detail relationship from the parent to the child. You could also potentially do it with a filter, but that might be slower than the master/detail.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Delphard]

ListSources for both DBLookupComboBoxes are Queries, and DBLookupComboBox2 writes value into Table. Query1 (which provides Countries) is DataSource for Query2 (which provides Cities) ,with foreign key as "link" in WHERE section.
The "only" problem is when I browse Table, DBLookupComboBox1 doesn't display any value for Country.
So, question remains open...

 

[svanels]

How many datasources you have? Did you link the property datasource of your look-up queries to your main table datasource? When you browse the table, its datasource modifies and this must trigger your look-up tables

An example can be found in:

How to use Look Up fields with Tables /Queries
faq102-1212

A DbLookupComboBox needs to get his values from another table.
if the values are stored in the same table, just use DbComboBox...

Regards

Steven van Els
SAvanEls@cq-link.sr

 

[Delphard]

Simply:
- Query1 provides names of Countries (from 'COUNTRY' Table)
- DbLookupComboBox1 has just ListSource/ListField Property and lists Countries from Query1 (its function is just to simplify/restrict selection of Cities)
- Query2 provides Cities (ID,Name) and has for DataSource Property Query1 (so it lists just Cities from selected Country).
- DbLookupComboBox2 has both ListSource/ListField and DataSource/DataField Properties: it lists Cities from Query2 and writes their IDs into third Table ('CUSTOMERS')

So, its obvious that there isn't direct connection between CUSTOMERS Table and Query1, and therefore DbLookupComboBox1 can't show right Country when Customers is browsed.

I guess this is frequent situation, so I'm wondering that nobody has solution.

 

[svanels]

I would extend the Cities table with one field, named country that would resolve the whole issue of displaying the correct country on your customers forms, without having to place this specific in your customers table.

Steven van Els
SAvanEls@cq-link.sr

 

[Delphard]

@svanels:
Client Table ,off course, have field 'Country_ID'.
Adding field 'Country_name' into Client Table is, you know... redundancy, and I would like to avoid that.

 

[Delphard]

Mistake!
I mean: Cities Table have field 'Country_ID', and Customer Table have just 'City_ID'. So:
Adding field 'Country_name' into Client Table (or into Cities Table) is redundancy.

 

[svanels]

You do not need to add the field country in the Client table to display it in a query or so. Your link will be city, I do not see any redundancy.

You can make the information visible just by linking the two tables. Example you want to bill the client by mail, just call the field country from your cities table.
Imagine you client lives in a spanish, portuguese or italian (latin) speaking country and lives in Santa Maria...
There must be at least a dozen towns called Santa Maria in the States, or even in Angola...

Steven van Els
SAvanEls@cq-link.sr

 

[svanels]

Sorry after re-reading your posts, I think you are using the wrong controls.

You have 3 tables, Clients, Countries and Cities..

Could you reformulate what you need.

Steven van Els
SAvanEls@cq-link.sr

 

[Delphard]

Once more:
I have 3 tables, Clients, Countries and Cities...
Each Client belong to some City (had 'City_ID' foreign key). Each City belong to some Country (had 'Country_ID' foreign key). So,for each "Santa Maria" DB "knows" in which Country she is, there is no need to put any Field from Country Table into Clients Table because this is redundancy. This was DB part.
Now about Delphi part:
On the Form for Clients I put two DBLookupComboBoxes: one (DBLookupComboBox1) for choosing Country (its function is just to simplify/restrict selection of Cities, it have NOT a DataSource/DataField Properties). When I select some Country in DBLookupComboBox1, next DBLookupComboBox (DBLookupComboBox2) lists just Cities for that Country (this works fine because Query1 which is ListSource for DBLookupComboBox1 is DataSource for Query2 which is ListSource for DBLookupComboBox2). Because this DBLookupComboBox2 have DataSource/DataField Properties, it put 'City_ID' into Clients Table. So, inserting of data is OK.
But, when I list (browse) existing Clients, BLookupComboBox1 don't display Country name (there is some logic, because DBLookupComboBox1 have NOT any direct connection with Clients Table).
So, I need a method to "enforce" DBLookupComboBox1 to display correct Country.
To quote myself:
I guess this is frequent situation, so I'm wondering that nobody has solution.

 

[svanels]

I would remove DbLookupCombobox1 since it is only a selector for the possible cities and use a plain combobox or listbox. The OnDataChange event (which is fired when you browse or modidy a record in the dataset), of the "client" datasource could be used to syncronize the value of the combobox, just by reading the country field in your look-up table/query.

Steven van Els
SAvanEls@cq-link.sr