473,327 Members | 2,025 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

problem with autonumber FK in data entry form

Hello,

I have a data entry screen that is giving me the following error when I try to save a record."field studentinfo.personid cannot contain a null value because the required property is set to true."

this makes sense because the particular field IS set to required. but it is a FK from an autonumber.

the screen collects person information (addresses, etc), student information (programs, etc), and assigns a role as a default value (student). There are no subforms, everything is on main form.
My recordsource for the form is:
Expand|Select|Wrap|Line Numbers
  1. SELECT studentinfo.*, person.*
  2. FROM (person INNER JOIN studentinfo ON person.personid = studentinfo.personid) INNER JOIN tblperson_role ON person.personid = tblperson_role.personid;
  3.  
if i remove tblperson_role from the recordsource and use the following code everything works fine. of course, no role is assigned this way.
Expand|Select|Wrap|Line Numbers
  1. SELECT studentinfo.*, person.*
  2. FROM person INNER JOIN studentinfo ON person.personid = studentinfo.personid;
  3.  
This is very annoying and does not seem like it should be a big problem. I list pk/fk table structure below. Thank you for your time it is much appreciated.


tblPerson: personid (pk) - autonumber
tblStudentInfo: studentinfoid (pk) - autonumber, personid(fk)
tblPerson_Role: personid(fk), roleid(fk)
tblRole: roleid (pk) - autonumber, role
Jan 23 '07 #1
13 1732
MMcCarthy
14,534 Expert Mod 8TB
Unless these tables have a one to one relationship (that is that there is one and only one record in each table corresponding to the record in the joined table) you cannot add a record to this query.

This means that the query is not updatable and you will have to split your form into main and subform(s)
Jan 24 '07 #2
Unless these tables have a one to one relationship (that is that there is one and only one record in each table corresponding to the record in the joined table) you cannot add a record to this query.

This means that the query is not updatable and you will have to split your form into main and subform(s)
Yes I started to see that after I wrote everything out in the post. Using subforms should fix the problem. However, the relationship in question (person -> studentinfo) really SHOULD be a one-to-one but is currently one-to-many. Is there any easy fix to this? The autonumber type does not seem very flexible when applied this way.
Jan 24 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
Yes I started to see that after I wrote everything out in the post. Using subforms should fix the problem. However, the relationship in question (person -> studentinfo) really SHOULD be a one-to-one but is currently one-to-many. Is there any easy fix to this? The autonumber type does not seem very flexible when applied this way.
Delete any duplicate personId records from the studentinfo table. Then in the studentinfo table design change the indexed property of personId to Yes (No Duplicates).
Jan 24 '07 #4
Delete any duplicate personId records from the studentinfo table. Then in the studentinfo table design change the indexed property of personId to Yes (No Duplicates).
I am still having trouble adding the foreign key for a record.

table structure:
person
personid (pk) - autonumber
studentinfo
studentinfoid (pk) - autonumber
personid (fk) - number long integer

I have an unbound main form with two subforms on it. One subform contains table person fields, other subform contains studentinfo table fields. Everything populates when I save an entry except for the personid field in studentinfo. Maybe I have to link master fields here??? I am a little lost now.

Another question - is it necessary to have the primarykey of studentinfo be an autonumber or can i use the feoreign key personid as PK?

thanks for your time
Jan 24 '07 #5
MMcCarthy
14,534 Expert Mod 8TB
I am still having trouble adding the foreign key for a record.

table structure:
person
personid (pk) - autonumber
studentinfo
studentinfoid (pk) - autonumber
personid (fk) - number long integer

I have an unbound main form with two subforms on it. One subform contains table person fields, other subform contains studentinfo table fields. Everything populates when I save an entry except for the personid field in studentinfo. Maybe I have to link master fields here??? I am a little lost now.

Another question - is it necessary to have the primarykey of studentinfo be an autonumber or can i use the feoreign key personid as PK?

thanks for your time
If you've made the changes I suggested in the previous post then open the relationship window. Make sure there is a relationship between the Person.PersonID and studentinfo.PersonID field. Double click on the relationship line to open the properties. Make sure the relationship is descibed (bottom of the window) as One to One. Then click all the cascading options. When you save/close this should establish the relationship.
Jan 24 '07 #6
If you've made the changes I suggested in the previous post then open the relationship window. Make sure there is a relationship between the Person.PersonID and studentinfo.PersonID field. Double click on the relationship line to open the properties. Make sure the relationship is descibed (bottom of the window) as One to One. Then click all the cascading options. When you save/close this should establish the relationship.
I have a one-to-one, am enforcing integrity, and have cascading update/delete
Jan 24 '07 #7
MMcCarthy
14,534 Expert Mod 8TB
I have a one-to-one, am enforcing integrity, and have cascading update/delete
Ok you should now be able to join these two tables as follows and use for your main table (leaving person role in a subtable if it is not one to one)

Expand|Select|Wrap|Line Numbers
  1. SELECT Person.*, studentinfo.*
  2. FROM Person INNER JOIN studentinfo
  3. ON Person.PersonID = studentinfo.PersonID;
  4.  
Jan 24 '07 #8
Ok you should now be able to join these two tables as follows and use for your main table (leaving person role in a subtable if it is not one to one)

Expand|Select|Wrap|Line Numbers
  1. SELECT Person.*, studentinfo.*
  2. FROM Person INNER JOIN studentinfo
  3. ON Person.PersonID = studentinfo.PersonID;
  4.  
Thank you for your help. The foreign key (personid) is filling in properly. I am now working on getting person_role to work. The relationship between person and person_role is one-to-many. What do you mean by subtable?
Jan 29 '07 #9
MMcCarthy
14,534 Expert Mod 8TB
Thank you for your help. The foreign key (personid) is filling in properly. I am now working on getting person_role to work. The relationship between person and person_role is one-to-many. What do you mean by subtable?
Sorry my fault. I meant of course a subform based on this table which has a master child relationship with the main form based on personId
Jan 30 '07 #10
Sorry my fault. I meant of course a subform based on this table which has a master child relationship with the main form based on personId
I still havn't gotten it to work properly. Mainform is set up with fields from person & studentinfo with recordsource
SELECT person.*, studentinfo.* FROM person INNER JOIN studentinfo ON Person.personid = studentinfo.personid;
I then have a subform with one textbox on it bound to roleid and default value of 1. Have tried several combinations of recordsource & master/child to no avail. The person & studentinfo tables will populate but not person_role.

person -> studentinfo is 1:1
person -> person_role is 1:many
Feb 1 '07 #11
MMcCarthy
14,534 Expert Mod 8TB
I still havn't gotten it to work properly. Mainform is set up with fields from person & studentinfo with recordsource


I then have a subform with one textbox on it bound to roleid and default value of 1. Have tried several combinations of recordsource & master/child to no avail. The person & studentinfo tables will populate but not person_role.

person -> studentinfo is 1:1
person -> person_role is 1:many
The master/child join you need is on personId. Therefore personId as a foreign key in the person_role table needs to be on the subform.
Feb 1 '07 #12
The master/child join you need is on personId. Therefore personId as a foreign key in the person_role table needs to be on the subform.
I believe that the reason this wasn't working is because I had a default value on the subform. I think that the subform may need to actually have a value typed in on a field in order for it to update. To get around this I just ran a INSERT INTO statement attached to the save button. Thanks for your help.
Feb 5 '07 #13
MMcCarthy
14,534 Expert Mod 8TB
I believe that the reason this wasn't working is because I had a default value on the subform. I think that the subform may need to actually have a value typed in on a field in order for it to update. To get around this I just ran a INSERT INTO statement attached to the save button. Thanks for your help.
Glad you got it working.
Feb 5 '07 #14

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

Similar topics

12
by: deko | last post by:
Is there a way to reset the AutoNumber sequence? I have several tables that use the AutoNumber field as the Primary Key, and I'd like to somehow do an Import/Export that will make remove the...
3
by: Ralph Birden | last post by:
Hi all, Here's my situation: I have 2 tables, let's call them TableA and TableB. TableA has a primary key called AffNo, TableB's primary key (--> foreign key in TableA) is called ID, and is...
35
by: Traci | last post by:
If I have a table with an autonumber primary key and 100 records and I delete the last 50 records, the next record added would have a primary key of 101. Is there any way to have the primary key...
2
by: David | last post by:
I have a table which is to hold 70 memo fields to contain notes on data changes to corresponding fields in a form. The problem is I dont want to have to create 70 forms to input notes into. Is it...
12
by: Riley DeWiley | last post by:
I have a project that is using a Jet backend and having trouble with Jet's tendency to bloat it's MDB file. I can compact it but it is a hassle. I am considering switching to a Fox backend but have...
3
by: shawnews | last post by:
Ok...I'll first describe briefly what I've done. Working from a paper form with over 200 fields - broken into 10 areas, I created a database with 10 tables. I then created a form using those 10...
4
by: JohnnyMid | last post by:
I am working on a database, and have been having some trouble with the auto number feature. Occasionally, the data entry form is saving blank records into the table. I am trying to figure out a way...
2
by: micksitup | last post by:
Without using autonumber, how can i generate the next available record number to appear in the primary key field on a data entry form? For example, i want to add a new supplier, but the primary...
3
prn
by: prn | last post by:
Hi folks, I've got something that's driving me crazy here. If you don't want to read a long explanation, this is not the post for you. My problematic Access app is a DB for keeping track of...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.