Solved Problem writing subform data to table.

[MikeT1941]

This should be very easy but I am getting more and more stupid, so I can't solve it. Any help would be appreciated.

I have a main form, filled via a query, with a built in subform which references a table of names etc. which is also provided via a query.
There are two cases, assuming the main form data already exists:
Case 1 the name of the Client already exists- this works fine via a combobox dropdown and the data appears in the subform.
case 2 the name does not exist and has to be entered. This used to work but no longer does so- even worse I get an error on entering the first character saying I am trying to enter a Null value.
Also if I go to alter the dropdown- e.g. delete it, I get an error name not in list.

Obviously I have done all this wrong, but I have played with it for hours and am now just going round in circles, head scratching.

I have uploaded a mini version to Dropbox, only 7MB if anyone would take a look for me.
Well- I would apprend it but security says no
I will post this and try again

Mike

 

[MikeT1941]

Here it is in Notepad

 

[theDBguy]

Hi. Welcome to AWF!

Try zipping your db file first and then attach it again to your post.

 

[MikeT1941]

Thanks for that- I'll do it now.

 

[MikeT1941]

Had to delete the photos but left one!

 

[arnelgp]

this is my answer on your post#1.

 

[MikeT1941]

ArnelGP that's very kind of you.
However, if I try to enter a new Client using the subform, I can't do that because I get the following error

whichever box I try and enter first. Since I have another 500 pages of the ledger to enter with several on most pages, it's an important point. Is it possible to do that? Obviously I could enter them all from the Client form, but that is much more labour intensive.
Thanks
Mike

 

[MikeT1941]

Thanks Pat
Quite true, because there I am trying to create a new Client record from the sub-form window, but I'm not clever enough to work out how to set that up properly. ArnelGP's modified DB is at #7 and my original one is at #6.
Relationship table is here

I have also noticed that the last field (Attachmentfield1) in the main form appears to be a duplicate of filesreferred- I will have to look into that.

Thanks again

Mike

 

[MikeT1941]

Pat

Thanks- I totally understand the implication of null- well I did 50+ years ago!
In the table HistoryLog, from where the Main form gets its values, the ClientID is a Long integer, so I cannot change the default to Null
In the original Clientnames table the autonum Client ID is ditto
If I have a new record with, as it may happen, a new client, then I cannot create a new ClientID number because that is an autonumber
But I am creating this new record in the main form where ClientID it is a text box which in both cases I have altered to default value Null
But- it still complains


Please don't get worried about the titles of the various bits- they got altered.
The main form is HistoryForm titled History_Log. That records every little detail e.g.
Ancestor john worked for Liverpool Corporation and built a building
Ancester William 2 worked for many people and all recorded in this 540 page ledger from 1837 so really interesting for the history of Liverpool. I am trying to log it so can see if there are any patterns over the 10 years of who did what to whom!
So for the next several hundred records I assumed it was easier to do it all on one form.
The Client form was originally only for people associated with John, but that turned out to be quite a small number so they can be considered as clients to keep the data simple. If that makes sense. The difference will be that if there is no invoice value then they are "associated with" rather than "paying clients"- I hope. Maybe I will add another field to the Clients to differentiate.

Hope that is reasonably coherent.

Best
Mike

 

[mike60smart]

Pat

Thanks- I totally understand the implication of null- well I did 50+ years ago!
In the table HistoryLog, from where the Main form gets its values, the ClientID is a Long integer, so I cannot change the default to Null
In the original Clientnames table the autonum Client ID is ditto
If I have a new record with, as it may happen, a new client, then I cannot create a new ClientID number because that is an autonumber
But I am creating this new record in the main form where ClientID it is a text box which in both cases I have altered to default value Null
But- it still complains


Please don't get worried about the titles of the various bits- they got altered.
The main form is HistoryForm titled History_Log. That records every little detail e.g.
Ancestor john worked for Liverpool Corporation and built a building
Ancester William 2 worked for many people and all recorded in this 540 page ledger from 1837 so really interesting for the history of Liverpool. I am trying to log it so can see if there are any patterns over the 10 years of who did what to whom!
So for the next several hundred records I assumed it was easier to do it all on one form.
The Client form was originally only for people associated with John, but that turned out to be quite a small number so they can be considered as clients to keep the data simple. If that makes sense. The difference will be that if there is no invoice value then they are "associated with" rather than "paying clients"- I hope. Maybe I will add another field to the Clients to differentiate.

Hope that is reasonably coherent.

Best
Mike

Hi Mike
Your Data Input form should be as shown in the attached.

 

[MikeT1941]

Mike
Many thanks for your exciting form. the Admiral has called me to eat so I will have a better look later.
I was RNR 63-06. Always nice to see people proud of Service.

Mike

 

[mike60smart]

Hi Mike

Pat is right and I should have explained what I had done.

The Parent record is the Client and the History are the Child records.

Your problem was as everyone had suggested was that you were trying to enter Child records before you had created a Parent.

All I did was create a Main Form based on the Client and then created a Subform based on the History table.

Both of these Forms are based on the actual Table and not using queries.

This was as far as I went but there are other elements that are wrong.

 

[MikeT1941]

I have been through the problems with Mike and I think we can mark this one as solved- it now does what I want and I mostly understand it due to getting a full explanation. thank you Mike for spending so much time on it with me and thanks to you, Pat, for bringing the error into the daylight. Also to ArnelGP for his contribution.

 

[MikeT1941]

certainly did!