Cannot Add New Record Via Form

[batowl]

Hi All,

I recently created a database in Access 2003 (Windows 7 OS) and got the tables and forms just the way I wanted them, then discovered that I cannot add any new records to the tables via my data entry form (the add new records button is grayed out). I can add new records directly into the tables, so there must be something wrong with the form itself. All the appropriate permissions are set correctly (Allow Additions is set to "Yes" etc. etc.) so there is likely an underlying problem with the query that created the form, but I have no idea what the problem is or how to fix it.



The database consists of one main table (Campylobacter_VDH) and several (24) tables related to it via a one-to-one relationship. There is also one table (Campy_Epi_PFGE_Bridge) that has a one-to-many relationship with the main table. I could not put all the fields in these tables onto one form so I made one main form (with the main table and several of the one-to-one tables):

SELECT Campylobacter_VDH.State_Case_ID, Campylobacter_VDH.Investigator_Name, Campylobacter_VDH.Jurisdiction, Campylobacter_VDH.Investigation_Start_Date, Campylobacter_VDH.Report_Source_Name, Campylobacter_VDH.Report_To_State_Date, Campylobacter_VDH.Case_Status, Campylobacter_VDH.Interview_1_Date, Campylobacter_VDH.Interview_1_Result, Campylobacter_VDH.Interview_2_Date, Campylobacter_VDH.Interview_2_Result, Campylobacter_VDH.Interview_3_Date, Campylobacter_VDH.Interview_3_Result, Campylobacter_VDH.Investigation_Status, Campylobacter_Demographic.Respondent_ID, Campylobacter_Demographic.Respondent_ID_Other, Campylobacter_Demographic.Case_First_Name, Campylobacter_Demographic.Case_Last_Name, Campylobacter_Demographic.Case_Date_of_Birth, Campylobacter_Demographic.Age_at_Onset, Campylobacter_Demographic.Sex, Campylobacter_Demographic.Street_Address_1, Campylobacter_Demographic.Street_Address_2, Campylobacter_Demographic.City, Campylobacter_Demographic.State, Campylobacter_Demographic.ZIP_Code, Campylobacter_Demographic.County, Campylobacter_Demographic.Home_Phone_Number, Campylobacter_Demographic.Cell_Phone_Number, Campylobacter_Demographic.Work_Phone_Number, Campylobacter_Demographic.Ethnicity, Campylobacter_Demographic.Race, Campylobacter_Clinical.Physician_Name, Campylobacter_Clinical.Physician_Phone_Number, Campylobacter_Clinical.Specimen_Type, Campylobacter_Clinical.Specimen_Collection_Date, Campylobacter_Clinical.Deceased, Campylobacter_Clinical.Hospitalized, Campylobacter_Clinical.Hospital_Name, Campylobacter_Clinical.Admission_Date, Campylobacter_Clinical.Discharge_Date, Campylobacter_Clinical.Length_of_Hospitalization, Campylobacter_Clinical.GI_Symptoms, Campylobacter_Clinical.Urinary_Tract_Infection, Campylobacter_Clinical.HUS, Campylobacter_Clinical.Sepsis, Campylobacter_Symptoms.Illness_Onset_Date, Campylobacter_Symptoms.Illness_End_Date, Campylobacter_Symptoms.Length_of_Illness, Campylobacter_Symptoms.Diarrhea, Campylobacter_Symptoms.Diarrhea_Start_Date, Campylobacter_Symptoms.Bloody_Diarrhea, Campylobacter_Symptoms.Vomiting, Campylobacter_Symptoms.Nausea, Campylobacter_Symptoms.Stomach_Cramps, Campylobacter_Symptoms.Fever, Campylobacter_General_Exposure.Antacids_Use, Campylobacter_General_Exposure.Antibiotics_Use, Campylobacter_General_Exposure.Medication_Descript ion, Campylobacter_General_Exposure.Exposure_Start_Date , Campylobacter_General_Exposure.Travel_History, Campylobacter_General_Exposure.Attend_Special_Even ts, Campylobacter_General_Exposure.Special_Events_Desc ription, Campylobacter_General_Exposure.NETSS_International _Travel, Campylobacter_Surveillance.MMWR_Week, Campylobacter_Surveillance.MMWR_Year, Campylobacter_Surveillance.Data_Source, Campylobacter_Surveillance.Molecular_Era, Campylobacter_Surveillance.Outbreak_Associated, Campylobacter_Surveillance.Outbreak_ID, Campylobacter_Surveillance.NETSS_NEDSS_Species, Campylobacter_Notes.Investigation_Notes
FROM ((((Campylobacter_VDH INNER JOIN Campylobacter_Surveillance ON Campylobacter_VDH.State_Case_ID = Campylobacter_Surveillance.State_Case_ID) INNER JOIN Campylobacter_Demographic ON Campylobacter_VDH.State_Case_ID = Campylobacter_Demographic.State_Case_ID) INNER JOIN (Campylobacter_Clinical INNER JOIN Campylobacter_Notes ON Campylobacter_Clinical.State_Case_ID = Campylobacter_Notes.State_Case_ID) ON (Campylobacter_VDH.State_Case_ID = Campylobacter_Notes.State_Case_ID) AND (Campylobacter_VDH.State_Case_ID = Campylobacter_Clinical.State_Case_ID)) INNER JOIN Campylobacter_Symptoms ON Campylobacter_VDH.State_Case_ID = Campylobacter_Symptoms.State_Case_ID) INNER JOIN Campylobacter_General_Exposure ON Campylobacter_VDH.State_Case_ID = Campylobacter_General_Exposure.State_Case_ID;

and then 6 subforms consisting of the fields from the rest of the one-to-one relationship tables (here is an example of the SQL from one of these subform queries):

SELECT Campylobacter_VDH.State_Case_ID, Campylobacter_Disease_Control.Childcare_Attend, Campylobacter_Disease_Control.Childcare_Work, Campylobacter_Disease_Control.Childcare_Live, Campylobacter_Disease_Control.Childcare_Descriptio n, Campylobacter_Disease_Control.Residential_Live, Campylobacter_Disease_Control.Residential_Work, Campylobacter_Disease_Control.Residential_Visit, Campylobacter_Disease_Control.Residential_Descript ion, Campylobacter_Disease_Control.Food_Handler_Work, Campylobacter_Disease_Control.Food_Establishment_N ame, Campylobacter_Disease_Control.Food_Symptomatic, Campylobacter_Disease_Control.Healthcare_Work, Campylobacter_Disease_Control.Healthcare_Facility_ Name, Campylobacter_Disease_Control.Healthcare_Symptomat ic, Campylobacter_Disease_Control.Ill_Previous_Contact , Campylobacter_Disease_Control.Ill_Know_Currently, Campylobacter_Disease_Control.Ill_Contact_Details
FROM Campylobacter_VDH INNER JOIN Campylobacter_Disease_Control ON Campylobacter_VDH.State_Case_ID = Campylobacter_Disease_Control.State_Case_ID;

The table with the one-to-many relationship is present too as its own subform.

Everything is working fine but again, I cannot add new records and I am confident I screwed up in making this form, I just don't know how. This database represents a lot of work and the new data to go into it is piling up, so any help that could be provided would be greatly appreciated. Thank you!

[June7]

INNER JOIN requires related records in both tables to already exist. This means can't add new record in either table via form bound to that query.

Why are you using join?

[batowl]

Well, I built this using the form wizard (but posted the SQL code). I did not specify that a join be used. Is there a way to fix this without recreating the form(s) from scratch?

[June7]

I don't use the design wizards because they do stuff I don't like.

Just noticed the first query has many INNER JOINs. Is Campylobacter_VDH the 'parent' table?

Open the query in design view.

If this is SQL statement in RecordSource, click the ellipsis (...) to open the query designer. Could shorten the SQL with:

SELECT Campylobacter_VDH.* FROM Campylobacter_VDH;

[batowl]

Oh man, that is my bad. I choose the wrong join when setting up the relationships between tables. Ugh, cannot believe I did that. Thank you for pointing that out - it fixed the problem!