is there a way to save a record in a table even if nothing is entered manually in the associated form? The primary key is automatically copied from the previous form.

I have 2 forms, each bound to a table. I would like to display the recordsets of the tables in a query. But this does not work, because if nothing is entered manually in my second form, no record is saved. Therefore the record for the query is missing and the entry is then missing in the query.

CodePudding user response：

As a general rule, the need to write out blank reocrds is not required, but is also VERY bad idea.

Lets take somthing simple:

We have say some customers (table customers), and then say we have some invoices for each customer. (table invoices).

So, I can build a query to say list customers, and say their invoice numbers.

But, what happens if I want that query to ALSO include customers that don't have a invoice just yet?

We would not start creating a whole boatload of empty invoices. That going to not only mean we have a gazillion blank records, but then all kinds of issues will crop up (such as rules stating we can't create a invoice without a invoice number!

So, you find that about 90% of queries in such cases?

You build your query as what we call a left join. just think of say all the records on the "left side" table will show, and optional the child records (the table on the right side).

So, a table with a join from customers to invoices looks like this:

But, we want the left side records to show, even when no child record(s) exist.

So, right click on join line like this:

And now we choose this:

Now, you see a "arrow head" in the query like this:

Note the arrow head, and note the option you have when building that query.

So, it very common to build a query, and very common to build such queries that work without child records.

but, we did not, and would not start tossing in boatloads of blank/empty records for such operations. that just creates a mess, tons of blank records now start appearing in reports.

And it even worse, since then you can't for example make invoice number a required field. The more tables, the greater the mess of blank records you will require.

For example, for each invoice record, then I have a bunch of child invoice details records!!!

As you can see, everything is going to fall apart VERY fast if we have to adopt a concept of creating blank records throughout the database.

Think of the huge pile of records when we have 3 tables or even more in the query!!!

The solution is not to create boatloads of blank records all over the place in your database, the solution is to build a query that works, and one that works even when no child records exist.

So, regular join (or often called inner) means a child record must exist.

But, a lot of times we want all the rows and records from the "left side" table in above, and don't care nor require that child records exist or not.

Use adopt a left join.

And another tip? Always drop in your parent table on the left most side of the query builder. Then as you add child tables (when required), you work your way to the right, and those multiple tables that you drop into the query builder will VERY often need a "left" join, since as you noted, records might not exist in those child tables.

You should be free to type in, add new customers as you wish. The fact that you have customers without a invoice (just yet) should not matter for general operation of your database.