Creating Sub record in a query.

[Perfac]

Question.
By entering EntityName and Tradingname alone in query q01BusinessInfo we would like the address to be created as well. Is it possible in the query after entering these two fields that an address record is created already?

Reason.
Sometimes a new record needs to be created in a hurry by just entering the name, and then come back later to complete information like “address” and “bank particulars”.

Remark
We made it work using the forms. Am I missing something that it can be achieved in the table and query? In the form we still have to at least enter one field each in the two main tables, t01Combinedentity and t01BusinessInfo. I am not sure why in the query the default values are set the same as on the form. On the form it creates an address record. When I test it in the query entering the 2 fields it doesn’t create a record in address.

Background

1. Our database is setup where all entities i.e. Banks, Employees, Customers, Suppliers and Agents have their own tables. They are combined in t01Combinedentity table.
2. There is one table to keep all addresses and one table to keep all Bank particulars.
3. In t01Combinedentity there is a local link addrss_ID001. I can’t put a default value since a new record must be created for every address of the different new entities being created.

Test 200225.zip

[ranman256]

the ClientID is created when user enters client's name/addr/etc in the tClient table.
a subform on the Master form would show all tClient info and be bound by the ClientID.
when user enters client info in the subform the client id is created and can now be used in the master form.

you can always have the master form have a button for user to pick the ClientID via search.

[Perfac]

I spent a lot of time a couple of years ago trying to get myself to know exactly how it works. Frustratingly my efforts resulted that two separate queries that are set up exactly the same way reacted different. I also experienced that the same form acted differently on entering two separate records. At the moment all forms in our DB does what we expect. One of those times where we are not sure why we got it right. I think the final answer is it can not be done to get the result of entering one field in a query that is linked to a couple of others and there are enough default values, to create a complete record. As you say at least one field should be entered of each linked table. I also experienced different results when rather linking with tables, than queries. We followed a rule where we always create a query for every table, even if there are no links to other tables or queries. We experienced challenges which were solved when we rather went for linking tables with one another than queries. It is all a bit complicated. It works now, because of forms to enter one field on a form with many fields, just to come back later to complete.