By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,965 Members | 1,136 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,965 IT Pros & Developers. It's quick & easy.

Error message on primary key field being null

100+
P: 418
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
Expand|Select|Wrap|Line Numbers
  1. ECHOID – Pk (auto)
  2. EchoNo – Text
  3. SubmitDate – Date/Time
  4. ReceiveDt – Date/ Time
tblMTAP
Expand|Select|Wrap|Line Numbers
  1. MTAPID – PK (auto)
  2.  VendorID – Number
  3. AccountID – Number
  4. GrantFundID – Number
  5. GrantID – Text
  6. ProjectID – Number
  7. MTAPAmount – Currency
tblSPAP
Expand|Select|Wrap|Line Numbers
  1. SPAPID – PK (auto)
  2.  VendorID – Number
  3. AccountID – Number
  4. GrantFundID – Number
  5. GrantID – Text
  6. ProjectID – Number
  7. 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.
May 12 '09 #1
Share this Question
Share on Google+
21 Replies


Denburt
Expert 100+
P: 1,356
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)?
May 12 '09 #2

100+
P: 418
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.
May 12 '09 #3

Denburt
Expert 100+
P: 1,356
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.
May 12 '09 #4

100+
P: 418
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
May 12 '09 #5

missinglinq
Expert 2.5K+
P: 3,532
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)>
May 12 '09 #6

100+
P: 418
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.
May 12 '09 #7

Denburt
Expert 100+
P: 1,356
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.
May 12 '09 #8

100+
P: 418
Here you go.

Please open frmECHOEnter. Thanks.
May 12 '09 #9

Denburt
Expert 100+
P: 1,356
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
May 12 '09 #10

100+
P: 418
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.
May 12 '09 #11

Denburt
Expert 100+
P: 1,356
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. ;)
May 12 '09 #12

100+
P: 418
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?
May 12 '09 #13

Denburt
Expert 100+
P: 1,356
In answer to question 1 and 2 yes that sounds fine.
May 12 '09 #14

NeoPa
Expert Mod 15k+
P: 31,709
@MNNovice
Did someone delete the attachment?
May 13 '09 #15

Denburt
Expert 100+
P: 1,356
Yes she did request that. If you would like a copy I don't think she would mind though. M?
May 13 '09 #16

100+
P: 418
Definitely no problem. Please share. Thanks.
May 13 '09 #17

Denburt
Expert 100+
P: 1,356
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.
May 13 '09 #18

NeoPa
Expert Mod 15k+
P: 31,709
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.
May 13 '09 #19

100+
P: 418
NeoPa / Den:

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

M
May 14 '09 #20

NeoPa
Expert Mod 15k+
P: 31,709
Thanks M.

I have it now. You can remove it when you're done Den.
May 14 '09 #21

Denburt
Expert 100+
P: 1,356
Got it. I will try and take a look at this in a little while, things are a little busy over here.
May 14 '09 #22

Post your reply

Sign in to post your reply or Sign up for a free account.