Base: How do I Add Fields as Columns in a Subform with a List Box?

[jdanniel]

Hello, may I please request some help with adding columns to a subform?

I’ve designed a rough draft of a database that has vendors and client companies. Nothing fancy.

The form has the company’s name and its district. I’ve created a subform that has a list box for the vendors that correspond to the company. It seems to be working properly.

However, I cannot figure out how to add a Gender column that corresponds to the vendors’ names, and I can’t figure out how to add a Warehouse column, either. I can only get the Vendor name added. I’ve tried inserting columns and editing the column properties, but I only see the foreign key fields in the “Join” table that I used to create the list box.

Am I doing anything wrong? Can the Gender and Warehouse columns be added? If so, what is the procedure?

Thank you very much. Jd

https://tinyurl.com/mu8u6t89

[skyandrews]

@jdanniel

https://tinyurl.com/mu8u6t89

The “Query_Vendor” sql should be something like this:

• SELECT (“Name” || ’ - ’ || “Gender” || ’ - ’ || “Warehouse”) AS “Vendor”, “VendorPK”, “Name”, “Gender”, “Warehouse” FROM “Vendor” -

[jdanniel]

This is what happens when I try that SQL.

[skyandrews]

(post deleted by author)

[jdanniel]

Thank you for taking the time to do this, but it’s not what I was after. Why are all three fields together in the list box? Is it not possible to have the other two fields added as separate columns?

I created the query using Query Wizard, and I created the form/subform with Form Wizard. I didn’t hand code any SQL.

The Form Wizard did not even display the Gender or Warehouse fields. When I tried to insert columns in Edit mode, they were unavailable? Why is that? Is there a specific reason why additional fields cannot be added?

[skyandrews]

Removed all of my sample posts as cpb may have provided the perfect solution near the end.

[jdanniel]

Yes! We are on the same wavelength now. Thank you!

As of right now, I believe I am very, very close to getting this. But I’m not there yet, as you can see.

I modified my query to equal yours. And I based the subform on that query.

Instead of selecting JOIN to create the subform (using the Form Wizard), I selected the query.

(I’m not sure whether or not to include the NAME field when I select the field of the subform.)

But there’s a point in the Form Wizard where it asks to select joins. I’m not sure what to do here.

So, I selected VendorFK and CompanyPK. Is that correct, or should I select something different?

Now…the problem I have to solve is creating the drop down menu for the Vendor Name. When I didn’t have the other two fields in the subform, it worked. Now that I’ve managed to get the other two fields in, I’ve lost the drop down menu.

How do I fix that? I’m pretty sure that once I get that up and running, I’m good to go. What do you think?

Here’s the updated database.
company_vendor.odb (14.5 KB)

[skyandrews]

First, edit the “qry_Vendor” to include the “CompanyFK” field from the “Join” table. Then you can make the join between the subform field “CompanyFK” and the main form field “CompanyPK”. Include all fields when creating “table control” forms. You can delete what is not needed later.

[skyandrews]

(post deleted by author)

[jdanniel]

I really do appreciate your words of encouragement, because this has been frustrating.

Please let me show you exactly, precisely, what I want. I created a similar database in Microsoft Access. It took me just a few minutes. The data isn’t exactly the same, but I think you’ll easily see the similarities.

This database contains bands, their members, their genders, and their role in the band.
I already populated the MEMBER table. Here is what it looks like:

Here’s what the form and subform look like:

There is a combo box for the BandMember field. Here’s what it looks like if I select a band member:

And when I’ve made the selection, here is what the form looks like:

This is what I want to do in Base. This is exactly, precisely, what I’m after.

I don’t know if you’re familiar with Access or not. If you are, and if you have access to Access, here’s the database I did in it.

Bands.zip (38.1 KB)

[iplaw67]

I don’t recall ever having seen this in Base. The Listbox control doesn’t appear to be capable of displaying 2 selectable linked fields side by side. The UI controls in Base are fairly simple structures with limited properties.

See, e.g.:

https://www.openoffice.org/api/docs/common/ref/com/sun/star/form/component/DatabaseListBox.html

Only 1 column can be bound.

[skyandrews]

(post deleted by author)

[jdanniel]

I had this gut feeling it just wasn’t something that could be done. Base isn’t Access; I know that. I was just hoping there was a workaround.

Thank you so much for the time you took to help me, for your patience, and for your words of encouragement. Your patience is sincerely appreciated. And I will continue to study, and use, Base. This is little more than a minor setback. There is still much I can do with this program.

Jd

[Villeroy]

Listbox: SELECT "Name"|| '(' || "Gender" || ')' AS "Visible", "ID" FROM "Vendors" ORDER BY "Visible"
Or add another subform showing the vendor details of the selected record.

[cpb]

@jdanniel,
I believe this does exactly as required.
the table “tJoin” has two columns “CompanyFK” and “VendorFK” which together form a composite primary key (this ensures no duplicates).
.
the query “qJoinInput” is data source for the subform which in turn is bound to the table “tJoin”, its content is very basic but it does the job without any need for macros etc.
.
I added a new customer called ‘Dummy’ to the table “tCustomer” for testing purposes.
Form_Two_Unbound_Fields.odb (14.2 KB)

[skyandrews]

I have removed my sample posts as cpb may have just provided the perfect solution.

[jdanniel]

It looks like we have a winner. I haven’t examined the database closely yet, but I’ll study it when I can. I’ll try to recreate what you’ve done, and let’s hope I succeed. I’m sure I’ll have questions. Thank you. I was on the verge of admitting defeat.