Base Attempting to select from list box sub-form to populate main form field - how to make this work?

Inkblotr · January 15, 2019, 3:27am

I’ve created a “Sales Entry-Maintenance” form (created with Wizard) with a sub-form of the ItemMaster table in a list box. No queries, no macros are used. The “Sales Entry-Maintenance” form is where I intend to be entering sales. When it is time to enter a value in the main form Sold_Item field, I need to select a record from the sub-form representing the item sold. I will need to scroll up or down to find (or search for) the correct ItemMaster record in the list box. Once selected, the ID (key) of the ItemMaster record would populate the Sold_Item field in the main form.

At least I think that’s how it should work - how I want it to work…

I’m new to Base, please be kind as I struggle to explain what help I need. I’m afraid I’m lost this time…

Thank you so very much, in advance!

Ratslinger · January 15, 2019, 3:51am

Your file for uploading is incorrect. It is just words not a link.

Not sure I understand your thinking. Usually you would select a master record & obtain the sales from there. Main form = master with sub form = sale. Care to elaborate?

Inkblotr · January 15, 2019, 3:58am

File = OOPS! First time I’ve tried to do that. How can I upload it now? I think you’ll understand my question much better once you can get into the file…

I thought (could be way off base) entering a sale record would be the main form. The item sold would be linked in by selecting the appropriate ItemMaster record from the sub-form.

Inkblotr · January 15, 2019, 4:00am

Please let me know if my re-upload didn’t work…

Ratslinger · January 15, 2019, 4:47am

Hello,

Upload now worked fine. While waiting kind of imagined what was really wanted. Turned out to be correct.

You don’t need a sub form here. All that is needed is a list box on the main form for the sold item. The list box is based on SQL of the ItemMaster:

SELECT "Item_Name", "ID" FROM "ItemMaster"

The first field in the select is what is displayed with the second field being stored in the record. The selection of what is stored is determined in the properties of the control on the Data tab. This is the Bound field. Here it is set to 1 with the number relative to 0. So the second field in the select statement is what is stored.

Also, with a list box, there is no need to scroll down the list to find the wanted item. Just start typing and the list will bring up the items as the characters change. For more information see the documentation in Section (Chapter) 4 - Forms - List box. You may want to also look at a Combo box to see the similarities. They are different but worth noting. Your choice seems to be good.

Have attached your sample back with a modified version of your form and the list box inserted.

Sample ----- ListBoxSales.odb

Inkblotr · January 15, 2019, 2:56pm

@Ratslinger - YES!!! That’s exactly the functionality I am needing! I clearly see how you did that and I’ll be applying the knowledge to the other two linked tables in the Sales Entry-Maintenance form. Your instructions are clear and on point to my ability and understanding of Base. Thank you VERY MUCH!

Inkblotr · January 15, 2019, 3:05pm

@Ratslinger - Did you happen to notice the “rel_Item_Type” table in the file? With your previous advice, I have gotten the intermediate table working. Record entry, so far, has been directly into the table. After I finish with the Sales entry form, I have to tackle getting the n::m relationship working between the ItemMaster and Types tables in the “Item Master Maintenance” form. I feel I owe you a debt of gratitude and I want to repay it.

Ratslinger · January 15, 2019, 5:23pm

@Inkblotr In actuality, until you mentioned it, had not looked at anything in the sample other than the question at hand. Just now went back and looked. It is good to see you are making progress. The “Item Master Maintenance” form will be a challenge for you depending upon what you expect it to accomplish.

Your thanks are enough. I and others answering questions on this and other forums do so as our part to give back to the community. It is a choice and only something demanded by ourselves. It is always good to get a response, especially when it actually resolves a problem.