Populate text box with unbound column of combo box

[bartjeman]

My combo box has sql:
SELECT "CUSTCODE", "CUSTNAME" FROM "CUSTOMER" ORDER BY "CUSTCODE" ASC

When the user selects the customer code from the combo box, I want the customers name, "CUSTNAME", to appear in the text box beside it.

Another question please
In the combo box properties I do not see any way to select the bound column. The bound column appears to be first field in the SELECT statement. Is there a way to select the bound column as the 1st, 2nd, 3rd etc. field from the SELECT?

Thanks

[DACM]

Well the quick way would be to use a List Box instead of a Combo Box. And based on your current 'SQL command' as the List Box Content, also bind the Text Box next to the List Box to the same Table field. So your List Box is saving the "CUSTNAME" from "CUSTOMER" to the Form's Table, and your Text Box is bound the the same Table field, so it has no choice but to reflect the selected "CUSTNAME".

However, with a relational database, we normally transfer the primary key of the customer to the Form's underlying Table using a List Box. The primary-key value saved in this manner becomes a "foreign key" in the Form's Table. We can then used a second List Box (typically disabled; as desired) in place of the Text Box to read the foreign key and display the "CUSTNAME" as desired. So both List Boxes have the same Data field assignment. Clear as mud?

See if this helps:

List Box 1 List Content with Data field "CUST_ID" (foreign key column in your Form's Table):

Code: Select all

SELECT "CUSTCODE", "CUST_ID" FROM "CUSTOMER" ORDER BY "CUSTCODE" ASC

List Box 2 (disabled) List Content with the same Data field "CUST_ID" (same foreign key column in your Form's Table):

Code: Select all

SELECT "CUSTNAME", "CUST_ID" FROM "CUSTOMER"

BTW, a Combo Box doesn't have the Bound field option
.

[bartjeman]

Perhaps my dBASE tables are not "relational" because the the two list boxes are not in sync.
I have attached 2 pictures.
Thanks

[bartjeman]

On the topic of table relations, I thought I would try basing the table on a query. My SQL included a JOIN instruction. Running the query results in an error window "The query can not be executed. It contains more than one table" WTF?

[DACM]

When the user selects the customer code from the combo box, I want the customers name, "CUSTNAME", to appear in the text box beside it.

Here's a quick example with two Forms (see the download below):

• List+Text form is a non-relational approach. A List Box used to select the CUSTCODE field while saving the CUSTNAME. After saving the record, the Text box then reflects the saved CUSTNAME. Is that what you want?
  List+List form reflects a relational database design. A List Box is used to select the CUSTCODE field while saving the CUSTCODE field. A second List Box then reads the CUSTCODE field and displays the CUSTNAME.

You may also want to look into "concatenation" to display the customer name next to the customer code in a single List Box.
http://user.services.openoffice.org/en/ ... 07#p200607

Otherwise, if you're interested in a relational database design, perhaps read:
. . . [Tutorial] Database design: Normalization
. . . [Tutorial] OOo Database Tutorials
. . . [Tutorial] Mid-level Base tutorial
. . . [Example] Relations reflected by list boxes in forms
.

[bartjeman]

Thanks for the reply DACM
I think we are talking about your second example.

The form I am creating is to add records to table WARRANTY. The WARRANTY and CUSTOMER tables have key CUSTCODE.
The source for the form is WARRANTY. I can create a list box that will display CUSTCODE from CUSTOMER, but the user needs to see CUSTNAME as well so they can pick the right customer and write the correct key to the new WARRANTY record.

I thought I might try a form based on a query, but when I tried to connect WARRANTY & CUSTOMER with a query, I got the error I posted earlier. BTW, My old DOS db software allowed me to make complex multiple table based queries.

I suppose what I need to know is...
are my problems due to a limitation with dBASE, should I try to move my tables to another db (like postgresql)
or is there a way to do what I need within the form or with a macro?

Thanks

[DACM]

I think we are talking about your second example...

...but the user needs to see CUSTNAME as well so they can pick the right customer and write the correct key to the new WARRANTY record.

Okay, that's the best option: a relational database design with a List Box used to display the CUSTNAME (and CUSTCODE if desired through simple or more sophisticated concatenation) while saving the CUSTCODE key to the WARRANTY table. Notice that List Box Content can be based on multi-table queries in all cases.

I thought I might try a form based on a query, but...are my problems due to a limitation with dBASE, should I try to move my tables to another db (like postgresql)

I think so, but we need o confirm your configuration. Are you actually using separate "dBase" files (one per table) in a folder? Are you aware of the built-in SQL database engine (HSQLDB) that's installed with Base? It can certainly handle complex queries on multiple tables, but not necessarily with legacy "dBase" tables. HSQLDB has it's own, advanced, table structure saved within the Base file (.odb) or separately in a folder. If you simply use the Base wizard to 'Create a new database' then you're using an "embedded database" with the "HSQL database engine" as reflected on the status bar in Base. The posted examples here utilize the built-in HSQLDB engine.

...or is there a way to do what I need within the form or with a macro?

Well so far, this is a very basic Form design (and underlying database structure) issue. As Forms get more advanced, we often use queries as the basis of sub-Form structures (MainForms and SubForms, also known as "data forms"). However, such queries will often be based on one table because a multi-table query is read-only in Base. This is not normally a limitation because the primary input vehicle is Forms. We can build a Form using multiple MainForm and linked SubForm components, with each component based upon a different Table or Query. This has the illusion of a multi-table query, complete with interactive data filtering -- such as selecting a Customer by name and seeing all the Warranties associated with that customer.

[Duanem]

I am also trying to update a text box from a listbox which contains supplier infromation i would really aprieciate a 1,2,3 on how to do this i am very much a beginineer in relation to base but i am encountering a use for this function all the time.

On another point i am very dissapointed on the complete lack of templates in relation to base i thought there might be a basic orders template to review and copy parts from but i am cannot seem to find anything to reference from.

Thanks
Michael.

[bartjeman]

Thanks for the help DACM
I seem to recall reading that HSQLDB was not multi user?
My dBASE files are on a Linux server, with 7 clients all using Windows XP

[DACM]

I seem to recall reading that HSQLDB was not multi user?

That's not accurate. HSQLDB can be run in server/listener mode to serve dozens of users. See my signature links for details. But I wouldn't have any idea how to set this up in OS/2.

There's some mis-information circulating these forums to include my statement above about multi-table queries being read-only in Base. It's been brought to my attention that Base 3.3 (and newer) supports multi-table write-through queries as long as the query includes the primary keys of each table AND any keys used to define the relationships between the tables involved (i.e. related foreign keys). My testing reveals that this works but it's a little quirky. I haven't tested with Forms...yet.

Edit: Here's a dedicated thread with additional details on write-through queries involving multiple tables ...

[bartjeman]

Thanks DACM!
The OS/2 port is current, but does not work with the HSQLDB , something about needing a current version of Java (which is being worked on I believe)
Anyway, I have moved all my clients to XP so it is not an issue.
Now all I have to do is convert my dBASE tables to HSQLDB, perhaps I can convert the dBASE tables to CSV. Is there a CSV import utilty/script for HSQL?
Regarding your multi-mode link, I'm not sure if I just need to install hsqldb2_OOo.exe or if I am also req'd to install hsqldb2_mydb.exe after that?

[DACM]

See: Migrating from Base+dBase to Base+HSQLDB 2.x

[bartjeman]

thanks!