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