Error message on primary key field being null

[MNNovice]

I am getting error message while entering data using a sub form. Here are the details of the tables associated with this form. Basically I am trying to enter expense data.

tblECHO has these fields

ECHOID – Pk (auto)
EchoNo – Text
SubmitDate – Date/Time
ReceiveDt – Date/ Time

tblMTAP

MTAPID – PK (auto)
 VendorID – Number
AccountID – Number
GrantFundID – Number
GrantID – Text
ProjectID – Number
MTAPAmount – Currency

tblSPAP

SPAPID – PK (auto)
 VendorID – Number
AccountID – Number
GrantFundID – Number
GrantID – Text
ProjectID – Number
SPAPAmount – Currency

The main form is based on tblECHO which has a two-page sub form that is based on tblMTAP and tblSPAP. As I enter data for expenses I am getting an error message

Primary Key or indexed field cannot contain a null value….

Well, I find out that as data is entered it’s updating all the primary keys except for MTAPID and SPAPID

Both the tables are related to tblECHO (join type 1, one – to – many). What can be wrong? How do I fix it?

Thanks for your help.

[Denburt]

Both the tables are related to tblECHO (join type 1, one – to – many).

O.K. and what fields in tblECHO are used to join these tables? I don't see it.
Check that these fields are listed properly in the Link Child Fields and Link Master Fields I think that is probably the problem.

Also why are you using GrantID – Text shouldn't that be the number from the autoID in the grants table (just curious)?

[MNNovice]

Denburt

Both the tables have ECHOID as a FK which I forgot to mention. This the field that connects the tables.

Also, I am using GrantNo and NOT GrantID - that was an error on my part too.

Thanks.

[Denburt]

Did you check the Link Child Fields and Link Master Fields in the forms properties to make sure that EchoID is in both places? I think if you do that you should be ok.

[MNNovice]

Yes, I did. And they look okay. They read as for example:

Clicking on page 2 of the sub form
Source Object: sfrmSPAP
Link Child Fields: ECHOID
Link Master Fields: ECHOID

Page 1 reads as
Source Object: sfrmMTAP
Link Child Fields: ECHOID
Link Master Fields: ECHOID

[missinglinq]

You're not trying, by chance, to enter a Child record before you've entered a record in the Parent form, are you?

Linq ;0)>

[MNNovice]

Missingling:

No, I am not trying to do so. As a matter of fact I cannot even activate the sub form if I don't select an ID for the main form.

The field ECHOID is listed on all the three forms. In the sub form (datasheet view) I have ECHOID and MTAPID and SPAPID placed on the form header and therefore remains invisible while entering data.

Hope one of you can help me. If needed I can post the DB for a quick review. Thanks.

[Denburt]

Please feel free to upload it if you wish but I am pretty sure that from what you have said that there is a field in one of the tables that is required to have data and somehow you are overlooking it and not entering information into this field.

[MNNovice]

Here you go.

Please open frmECHOEnter. Thanks.

[Denburt]

ok apparently your using the Echo No. field to search for data and as such it is unbound. When you go to enter a new record into the parent "Echo" no number is recorded so that is one error that I have found. Now if you filter for an echo no. then the parent (main) form has a record and you want to add records to the subform you get the error you mentioned and it looks as if the field MTAPID is listed as your primary key but it isn't set to Autonumber so you will to fill that in manually once I did that and entered the fund bingo new record no problem. Setting the Primary key to an autonumber helps things in a lot of ways. Let me know if I missed anything

[MNNovice]

Denburt:

I believe using EchoNo for an Unbound control is okay because it's tied to ECHOID field of tblECHO. And actually it works just fine.

However, I really don't know how the MTAPID and SPAPID got changed to a number field as oppposed to auto. I am trying to avoid re entering data and re create a new table by exporting data to excel and then importing it back. So far no luck, somehow the first field MTAPID changes to a different data type on its own. Go figure.

I kept trying until I succeeded. I got the SPAPID taken care of because there was no data and it's working on the sub form as well as on the report. It's all working now. Thanks a lot for your time to help me out.

Thanks for checking and finding this error for me. I shall keep you posted. Your greatly needed help is much appreciated. Thanks.

[Denburt]

Glad you have it working. Just as an FYI the autonumber field can be started at a particular number but once a record has been entered that ID number can't be changed so if your importing records into that table and try to import data/numbers into that autonumber field then that is probably how it was changed. In other words you cant just assign a number to that field it is designed to do it automatically. If something gets mismatched along the way it is usually easier to let autonumber do its thing then you change the FK in the other tables to match. I hope that makes sense. ;)

[MNNovice]

This is what I did. I created a new table called tblMT which has the similar table structure of tblMTAP. Then I exported tblMTAP to Excel. Next I imported tblMTAP excel data and added to the new table. Deleted tblMTAP and renamed tblMT as tblMTAP. All is working okay now. Couldn't do without your help though.

New question 1: I will have several types of expenses other than AP expenses. e.g., payroll expenses, Sales tax, etc. Do you think it's okay to add more pages to frmEcho as I need? Like say, if I create tblPayroll and tblSalesTax and then create subform out of these two new table and then add them to the main form frmECHO it should be okay?

New question 2: Right now I was able to create individual reports for MTAP and SPAP for a given ECHO. I created a query and then created a report based on that query. Can I do a similar report combining these two expenses into one single report?

[Denburt]

In answer to question 1 and 2 yes that sounds fine.

[NeoPa]

@MNNovice
Did someone delete the attachment?

[Denburt]

Yes she did request that. If you would like a copy I don't think she would mind though. M?

[MNNovice]

Definitely no problem. Please share. Thanks.

[Denburt]

M it may help if you send the most recent version and we can remove it as soon as we both get an updated copy.

[NeoPa]

8-)

Sounds like you got it sorted anyway Den, but for my curiosity, and to keep my copy up-to-date.

PS. Den, you just went offline on Skype. I was hoping to catch you this evening if you're around as we haven't spoken for so long.

[MNNovice]

NeoPa / Den:

Here is the latest version. Thanks for all your help.

M

[NeoPa]

Thanks M.

I have it now. You can remove it when you're done Den.

[Denburt]

Got it. I will try and take a look at this in a little while, things are a little busy over here.