BASE - Searching SubForms

[Justin100]

Hello. I’ve never been able to find or perhaps understand a solution to the following problem. I have a MainForm containing many fields from my Company table. It also has a SubForm containing a few fields from my Products table. Each company deals with one or more products.

Entering data on the Mainform and its SubForm is easy and table filters work well to find and edit Company table data and see and edit the company’s related products details in the SubForm.

My question: Is it possible to use this form to search for Product table data?

If one of the companies in the Company table deals with “Apples” I thought I could have the MainForm as described above be based on a Query with a WHERE clause to select company records WHERE products equal “Apples”. I have tried this and it partially works. The data displayed is perfect except it is read-only and cannot be edited which is for me a show-stopper.

Since the products of any one company are usually limited to a few items, I could simply have a text field in the company table, shown on the MainForm, called product_items and enter the products as text separated by commas. LO tables filters will work, and all field and subform data would be editable so this is a solution.

A subform though would offer more options and be more elegant, thus preferred.

Does anyone have an idea on how to implement this or should I just use the simple text field?

In passing I do have other SubForms on my MainForm, such as Contacts and Call History, so everything should be editable for any solution.

My apologizes in advance for posting a similar question previously but perhaps my question is clearer this time!

Thanks in advance for all help.

[Ratslinger]

Hello,

The answer is based strictly on the question - Selecting companies which deal with specific products.

It is just SQL (see Query1) & a table filter. For the sample used a past example of yours. Added a new form Sample. Included Company, Contacts and Products (left off Call History - just another SubSubForm).

Sample ----- QueryWithSubForm3.odb

Fields or table controls on the forms doesn’t matter. Should be the same result. Using table controls was easier for demo.

Edit 2018-06-11:

Your comment is NOT correct. Have modified the sample to now include form named SampleModified. Same results but this time Company is NOT a sub form. I believe your problem stems from SQL.

Modified sample ------ ModifiedQueryWithSubForm3.odb

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[Justin100]

Hi @Ratslinger. What I have been trying to do is have a form, say Form1, with Content Type being a Query and Content being a Table, say, Company. The query searches for something in the company table and perhaps something in the related Contacts or Products table. The results are visible on Form1 but only data in subforms is editable. Data from the Company table cannot be changed . Is this to be expected? If so, I need to put my company table data in a subform.

[Ratslinger]

@Justin100 Your question asked to select companies based upon products. The sample provided uses a query & all is editable INCLUDING Company. As stated in answer it is based upon your question. Isn’t this what you asked? Is something not working in sample provided? I see no problem.

[Justin100]

Your sample works perfectly! What I am trying to explain is that it works because the data needed to be edited is in subforms based on table content different from the mainform content. I will make my database more like your sample. Thanks Ratslinger for pointing me in the right direction.

[Justin100]

@Ratslinger. You’re a genius! After looking seriously at your Modified Sample, I see the solution to my two year problem with LO Base is that I needed to use a table filter along with a second main form. Never thought that was possible. You did it so I must be able to do it too! Thank you very much for always being there to answer my questions!!!

[Ratslinger]

@Justin100 Have forms with 4 or 5 main forms (eluded to this in another post of yours - tabbed form). Again, I don’t believe either using a form or sub form is your problem with not being able to update the Company records. This is more a SQL (query) problem. Be very cautious in this area. Even turning on pass through in the query will cause it to be non-updatable.