Solved Problem writing subform data to table. (1 Viewer)

[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

 

[Pat Hartman]

Please upload the database using the site tools if you want us to look at it.

 

[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

 

[Pat Hartman]

The message is quite clear. There is no related client in the Clientnames table. If you can post the database, it will be easier for us to help. Otherwise post a picture of the relationship window and make sure we can see all the relationships.

 

[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

 

[Pat Hartman]

One of the bad things that Access does (and this has gone back and forth several times since Access was first introduced) is that it is currently defaulting ALL numeric fields to 0 rather than null. This makes some sense when the field is NOT a foreign key and might be helpful to people too new to know the implication but it makes NO SENSE at all to default foreign keys to 0. Except of course Access has no way when you are defining the table what a particular field will be used for so it makes a consistent choice and defaults ALL numeric data types to 0. Just FYI, 0 has a meaning so when you default something like a student's test grade to 0, that means he got a 0. However, a default of null would indicate that the student does not yet have a grade and so this record should not be used in the calculation of his average grade. Say he has 80, 0, 70 for grades, that leaves 50 as his avg grade but 80, null, 70 would result in 75 as the avg grade.

Does ClientID in the history log default to 0 or null? It should default to null and you MUST provide a valid value.

Also, I don't understand why you are using a form to create a record in the history log. When you keep history, the app should be creating records in the background whenever some record in the main table is being changed.

 

[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

 

[Pat Hartman]

Mike, foreign keys are always long integers if they point to an autonumber, and they should always default to null. If you set the field as required, Access will not allow the record to be saved until a valid foreign key is entered (usually via a combo). But sometimes foreign keys can be optional. For example, a company has an asset database and the asset is a car. If the car is not assigned to an individual, the AssignedToEmpID could be null.

When you are entering the Client record, the ClientID will be automatically generated if you have defined it as an autonumber. That isn't what the message is referring to. The message is referring to trying to save a "child" record, a dependent of tblClient and not filling in the foreign key with a valid ClientID.

Mike60Smart didn't say what he fixed so I'm going to assume that what ever the problem with the form was, it is now fixed. I prefer to explain the changes I make if I make them for you rather than just blindly fixing things. It is very hard for you to learn how to solve your own problems when you didn't learn what was wrong initially.

 

[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.

 

[Pat Hartman]

Thanks for elaborating Mike

 

[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.

 

[Pat Hartman]

You are very welcome. Now the problem is truly solved and hopefully you learned a little bit about how relationships work

 

[MikeT1941]

certainly did!