Help with a Null value in a foreign key field??

likelle

New member
Local time
Today, 11:24
Joined
Oct 31, 2012
Messages
7
I'm hoping someone can help me figure out how or why I can't put a null value in a foreign key field??

Here's my situation:
I have 4 table, each with a one to many relationship to one other

1-Customers-contains the billing information for jobs
2-Job Log-contains job opportunities, job number for each job, salesperson, sold/closed, job site information
3-Order Details-contains important dates as to the status of a sold order such as contracted date, completed date, install start date, install complete date, installer, invoice amount, type of product sold, etc.
4-Purchase Order-contains itemized list of what items were ordered from other vendors for each job, dates when ordered, receiving date, vendor information, etc.

(there are other tables that support the tables above, but i think they are irrelevant to my question)

The relationships are as follows: Customer is one to many with Job Log, Job Log is one to many with Order Details, Order Details is one to many with Purchase Orders.

Here is my forms:
Job Log form-contains data fields from both Job Log table and Customer table. I wanted to be able to input a job opportunity on one form and if the job has a determined billing/responsible party, also input that information on the same form. Sometimes a job opportunity comes that doesn't have a customer/responsible party immediately.

Order Details form-contains the Order Details fields and includes a subform from Purchase Orders Table.

Here's my problem:
Access won't let me input a Job Log in the form without also inputting a Customer information. It says that a foreign key field cannot have a null value. I have the CustomerID field as the foreign key field in the Job Log and it is set to NOT be a required field....so why is it acting as a required field on my form?? Can I fix this or work around it??

I have tried to create a "false" customer name "no responsible party" to get around it, but when the customer information is developed later on, I cannot change that "false" customer assigned....I can only edit or modify the "false" customer information which obviously changes for all the jobs assigned to the "false" customer. That idea won't work for me....so got any other options or solutions??

I hope I wasn't too confusing here.....

Likelle
 
If you have got a REFERENTIAL INTEGRITY (in the RELATIONSHIPS) between these two tables, you can't to write a record in the table "JOB LOG". You must
write the record in the table "CUSTOMERS" before.
You can't to build a house starting from the roof.
 
Should I lose the referential ingegrity in my relationship? I'm not sure that would be what I want.... The flow of work always starts with a job number which is developed on the Job Log Table. Often a customer requests a quote which immediately becomes a "job" and gets assigned a job number and recorded in the Job Log Table....however, that customer/client isn't necessarily the person or company responsible for paying for the project. The customer sometimes gets assigned later, after the job is contracted or sold. So....there are times when the Job on the Job Table doesn't have a customer to connect with at least until the job is sold or contracted. Maybe I don't need to have referential integrity on that Customer one to many Jobs relationship? What would happen if I remove the referential integrity option?

To be clear on this relationship need......One job will always have one job number assigned(IDJob_Log), but one customer could be responsible for several Jobs over time. So I built the relationship as a one customer to many jobs and enforced referential integrity and cascading changes.
 
MStef is not correct in that you in Access, even with stipulated RI, you still can create orphan child records.

What your system does and why is difficult to judge from the voluminous verbiage provided. Show your tables/relations and which form is used to create the error and its recordsource, or even better, attach a stripped example db with instructions for how to create the "error".
 
I can't to say anything, I dont know your task.
You have to decide this.
 
Sounds like you need a third table that joins the Customer and Jobs.
You would not need a Foreign Key in tblJobs.

The third table would be.
tblCustomerJobs
CustomerJobsPK (Autonumber)
CustomerFK Number and equal to the Primary Key in tblCustomers
JobFK Number and equal to the Primary Key in tblJobs
Other fields as required

This way you can create Customers and Jobs independently and match them when required.
 
I apologize for all the verbiage. I find it difficult to be clear and yet be brief. :D

I also can't seem to find a way to attach an image of my table or form....i'll keep working on it. Hopefully, I'll figure it out soon.

I really appreciate the help and advice!! I'm obviously very new and have lots to learn about a database.

Likelle
 
spikepl

I can't see anything wrong with what MStef wrote.

As it currently stands you can't create a Child Record without a Foreign Key.

Am I missing something?
 
Likelle

Go to the FAQs for help with attachments.
 
I may try that third table option, but I'd rather not create excessive complication.....lol

This issue ONLY occurs when entering data in the form....when I enter data directly into the Job Log Table, I do not need to have a customer assigned. I get NO error message when I leave the CustomerID/foreign key field blank.

So, it seems that this is more of a form design issue??
 
I think I've solved this issue...... :)

I created a new form....from the same Job Log table but I added the Customer Table as a subform instead of just adding the customer table fields to the form. This appears to let me add a new Job without having to assign a customer to the job.

As far as the theory of whether a foreign key field should be left blank, I understand that may not be "best practices" but in my situation it is very logical to have a job without having a customer.

Thanks so much for the great input!! I appreciate your patience with my ignorance :)

Likelle
 
@RainLover

As it currently stands you can't create a Child Record without a Foreign Key.

Not quite.

I created a new form....from the same Job Log table but I added the Customer Table as a subform instead of just adding the customer table fields to the form. This appears to let me add a new Job without having to assign a customer to the job.

QED.

You can in a subform create a child record with a null as foreign key and Access lets you save it quite happily, whether there is RI imposed or not.
 
out of interest, you can even set up a multi-field key and make it unique - and access will actually let you have "duplicates" if part of the key (ie one field) is null
 
is this like a combo box? would I put that in my form or in my table?

I tried to use a combo box in the form but Access still refused to let me leave the customerID field blank(which is the foreign key field in job log)

My idea of converting the customer information into a subform has some issues too.....apparently as a subform, I can't necessarily add a new customer record...but I can edit an existing customer assigned in the job form which cascade changes to the customer table directly.

So far, I have worked around the issue but its not an ideal form design for me. I created a separate customer form which becomes the subform in my job log form. I have disabled the subform so its you can only view it and not click in it. I added a combo box that uses the customer table information to choose the assigned customer when necessary. The ONLY real issue here is if there is not an existing customer in my database and I must create a new one, then I HAVE to open the customer form and create the new customer there. I can't enter the new customer information on the job log form using the customer subform......but I can live with that, if i must....lol
 
You can't post a picture or the database because you haven't yet made 10 posts. Once you cross that threshold, you can show us your problem with pictures.

There is no normalization violation to have a null foreign key. This happens in many applications. If you were getting an error message, something was incorrect in your definition and it is difficult to figure out what it might be without seeing it.
 
@ spikepl

The foreign key should be set in the Table as required.

Orphaned records should be avoided. But to each their own.
 

Users who are viewing this thread

Back
Top Bottom