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]

Is this error a result of opening only the “Query_Vendor” query? The form “Company” may have been changed since first version of your sample odb. Everything works as expected in the original odb with only the suggested “Query_Vendor” sql changes! I have now edited the sql to include a field name “Vendor” in my first post, which should void the “field name” error. With the form in edit, check that the “Vendor” column of the table control is linked to the correct data field and the bound column is 1.
vendorV2.odb (14.5 KB)

[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]

Ok, after your latest post I think I understand what you may be trying to do! Here is my latest version of your sample. All of the sql queries can be created and edited by the wizard, or by hand coding, whichever is preferred. The reason no Gender or Warehouse fields are shown is because the subForm being edited is based on the table “Join”, which has no such fields.
vendorV3.odb (15.9 KB)