BASE - Form based on Query, are fields ONLY read-only?

[Justin100]

BASE - I’ve reworded my original question. My problem is with forms based a queries. For example, a database with a form, based on two related tables: Companies and Contacts. If the form is set as TABLE the Company data and the subform Contacts data display correctly and all data can be edited. Now if I change the from from TABLE to QUERY and base the query only on fields in the Company table, then the form results display correctly and can be edited (along with the subform Contacts data). Now if the query is based on a Contacts field, then the form results display correctly and Contacts data can be edited, but Company data cannot be edited. Is there a solution to this? Attached is a sample db. Thanks @Ratslinger for the original sample db!

Sample -QueryWithSubForm3.odb

[Ratslinger]

Hello,

Really not certain as to what is wrong on your end. Attached is a sample (simple - only Company & Contacts) which does allow modifications to the Company records even though it is based upon a query. The query uses a user entered parameter for selection of country (LOCATION field in Company).

If you are still having problems a sample, without personal or confidential information, would be helpful.

Sample - QueryWithSubForm.odb

Edit 2018-05-01:

Your query makes no sense. It creates a company record for each contact in the contact table and therefore creates a temporary table not directly related to the original table.

Company table:

Query result:

Can’t understand reasoning for this. It does not change the subform results in any way. In fact it confuses the results. With this query it presents the identical subform information on multiple form records. With this query if there were 100 contacts you would have 100 company records all identical. Why?

Original question pointed to being able to select by country but this makes no sense at all. This type of query will not allow updates to the company table. It’s not a one-for-one.

[Justin100]

Hi Ratslinger! As always, thanks for your speedy help! I rewrote the question above and made a change to your sample to show the issue I am having. I should have added a search parameter to the contact query, but doing so would not make a difference, at least not the way I am doing it, which may be the problem!

[Hrbrgr]

You must put the field “ID” in the query.

[Ratslinger]

@Hrbrgr The ID is already there.

[Justin100]

Hi Ratslinger. I attached the wrong sample db and but now changed it to the correct one, which is QueryWithSubForm3. The result perhaps will not make a difference as what I am trying to do does not appear to be possible unfortunately.

[Ratslinger]

What I believe is missing here is what you are actually attempting to do. The sample provided is really a reversal of the original. Now you seem to be dealing with selecting specific contacts opposed to selecting specific companies originally. In the later case the form is to be reversed - Contact on form & company on subform. Then with the SQL changed to selecting the contact all works as wanted. Can provide sample if needed but real question is what are you really trying to do?

[Justin100]

You are correct, reversing the form by making Contacts the main form and Companies as a subform, would solve the problem. I’m just trying to use one form for searching both company names and contact names, and being able to edit all information on the same one form. Some of my Company data fields have many text characters and do not look or read well in the subform spreadsheet like format.

[Ratslinger]

Now you’re getting to the actual question. Third time (or is it the fourth) a charm?

You can do this with a single Base form. Searching can be done using a table filter ( see → Table Filtering) and a tabbed form (see → Tabbed Form - requires minimal macro code in sample).

[Justin100]

Ratslinger, even my teachers in elementary school asked my questions were not very clear!!! Thanks again for your help. I’ll look at your suggestions.