Capture first record with queries linked to other tables with no records.

[Perfac]

a. In the seven years I used time to teach myself Access, I almost always used tables that store records.
b. Now if I clear my app of all records to start a new set the next challenge came up on which I need advice.


c. Some forms and their supporting queries are linked to one or more other queries or tables, surely like every database you may find.
d. Even if I set the primary link and secondary link to Null, the error below appears. If you open the attachment and post one record on form f01EntityTypes you will see.

e. If there were records in tables x01EntityTypes and x01EntityFeatures then I can add a record to f01EntityTypes, if I set the default value to a value that is in one of those tables.
f. I know a way to work around it. But I feel I am missing something easy. I want to be able to capture a record in f01EntityTypes without records in the tables.

[CJ_London]

you need to be using left joins

[Perfac]

Thanks. I will appreciate if you tell me what that means.
I find it uncomfortable when in my opinion Access is inconsistent. In my app there are many such links which do not return the error. I looked at it over and over to try and see why. I will look again right now. But I would like to learn a solution whenever it comes up.

I want it to just do nothing when the secondary link in f01EntityTypes is left Null, like many other places in my app.

[isladogs]

See Query Join Types (isladogs.co.uk)

[Perfac]

Thank you. I've studied your reference, and learnt a lesson, which I hope will always solve a challenge like this. I accept it is beginner stuff, and I look stupid. In my app there are many queries that join more than 6 tables or queries, and always come right. I do think my app is great, and in three months' time I intend making a presentation to the largest software company in SA, very close to my house. Something good may happen? Over time I downloaded more than 500 clips about Access and learned a lot from you good guys.

At this time I changed those joins to left link and it works.

[isladogs]

No problem.
All of us have gaps in our knowledge.
I've been using Access for over 25 years and that is still true - see my signature lines below

Out of interest, does that fix the blank forms issue you contacted me about yesterday?

[Perfac]

I agree with your signature lines. Don't think I know 50% of what is possible with Access, but my app is still good. I am a business engineer(auditor) and I did own 2 businesses over 32 years that employed cumulatively 1000 employees. My wife was a programmer, and so are my two sons.

So far, the blank forms issue and the issue in this thread seems to be solved and I should be able to fix it if ever comes up again.

[Perfac]

Yesterday I changed the left joins of two queries, after testing it worked and still work. I went to sleep at 6 am this morning after putting in the hours. Attached, is an image of a query that is linked to 9 other queries or tables. The same error than in post 1 comes up.
a. The arrows may show that the flow is in the same direction.
b. I got all the attached queries to have the same left join.
c. No fields have default values. On the running system most fields have default values, and do not have these errors, and the joining works.
d. But it is the first time in years I test my app with no records, and I have to get it not to bring up errors posting the first record.
e. I do not want to use anybody's time unfairly, but my challenge is now big.
f. I am trying to post a cut down version with this query and form, no success yet.

[Perfac]

Sorry I found the issue. Hakuna Matata!