Error 2105 -1, , New

[doddy88]

Hi,

I have just begun to create a new database for a placements office in a university. Students have the option to undertake a year's placement in their 3rd year.

I have created a form with tab control so that you can see all information for one record (student) from all the tables. The main tab page names are student details, contact details and employer info. These are also three separate tables on the database.

Student details has a primary key of student ID which is set to text.

Contact details has a primary key of contact reference number and is an autonumber. It also has a student ID field which has been linked to the student details table by a lookup wizard.

Employer info has a primary key of employer number which is also an autonumber. Similarly it has a student ID field which is linked to student details through a lookup wizard again.

Initially a student could have more than one employer but only one set of contact details. This worked fine on a form with tab. I then realised a student could also have more than one contact details as there contact details are generally their work address, work number and work email address. The relationships have been set up for this new set up and it works fine, I can add the data through the tables no problems.

When I come to click the add new record button on the tab in the form it brings up the error message 'you can't go to the specified record', error 2105 and argument -1, , new,.

The form allows additions and all required records have been completed. I noticed that in the contact details table I had set student ID as required, I changed that to unrequired as it is not on the form, as the student ID is already displayed in the student details table. I'm not sure if this is where the problem comes from.

Below is the record source if it helps.

SELECT [Student Details].[Student ID*], [Student Details].[Course*], [Student Details].[Forename(s)*], [Student Details].[Surname*], [Student Details].[Gender*], [Student Details].[Status*], [Student Details].[Visiting Tutor (Academic)], [Student Details].[Visiting Tutor (Pastoral)], [Student Details].Area, [Student Details].[CV Status], [Student Details].[Student H&S Declaration], [Student Details].[H&S Declaration Last Action Date], [Student Details].[Placed Through], [Student Details].[Employer Form], [Student Details].[Employer Form Last Action Date], [Student Details].[Additional Comments], [Contact Details].[Student Work Phone Number], [Contact Details].[Student Mobile Phone Number], [Contact Details].[Student Emai Address], [Contact Details].[Personal Student Emai Address], [Contact Details].[Line Manager Title], [Contact Details].[Line Manager Forename(s)], [Contact Details].[Line Manager Surname], [Contact Details].[Line Manager Job Title], [Contact Details].[Line Manager Contact Number], [Contact Details].[Line Manager Contact Emai Address], [Contact Details].[Work Address Line 1], [Contact Details].[Work Address Line 2], [Contact Details].City, [Contact Details].Postcode, [Contact Details].Country, [Contact Details].County, [Employer Information].[Company Name*], [Employer Information].[Employment Sector], [Employer Information].Department, [Employer Information].[Job Title*], [Employer Information].[Salary (£)], [Employer Information].[Start Date], [Employer Information].[Finish Date], [Academic Tutor Details].[Name*], [Academic Tutor Details].[Extension Number], [Academic Tutor Details].[Emai Address], [Academic Tutor Details].[Office Location], [Academic Tutor Details].[Mobile Number], [Pastoral Tutor Details].[Name*] AS [Name*_Pastoral Tutor Details], [Pastoral Tutor Details].[Extension Number] AS [Extension Number_Pastoral Tutor Details], [Pastoral Tutor Details].[Emai Address] AS [Emai Address_Pastoral Tutor Details], [Pastoral Tutor Details].[Office Location] AS [Office Location_Pastoral Tutor Details], [Pastoral Tutor Details].[Mobile Number] AS [Mobile Number_Pastoral Tutor Details], [Contact Details].[Student ID] AS [Student ID*_Contact Details]
FROM [Pastoral Tutor Details] INNER JOIN ([Academic Tutor Details] INNER JOIN (([Student Details] INNER JOIN [Contact Details] ON [Student Details].[Student ID*] = [Contact Details].[Student ID]) INNER JOIN [Employer Information] ON [Student Details].[Student ID*] = [Employer Information].[Student ID]) ON [Academic Tutor Details].[Tutor ID (Academic)*] = [Student Details].[Visiting Tutor (Academic)]) ON [Pastoral Tutor Details].[Tutor ID (Pastoral)*] = [Student Details].[Visiting Tutor (Pastoral)];

Regards

 

[GinaWhipp]

I see a small problem with the tables... Since a Student can have more than one Contact and more than one Employer two tables are missing...

tblStudentContacts
scStudentID - link to Students table
scContact - link to Contacts table

tblStudentEmployers
seStudentID - link to Students table
seEmployerID - link to tblEmployerID

Once those are created and your query is redone you should be able to enter a new record.

As a side note, you are making more work for yourself with spaces in your field and table names. That requires bracketing field/table names while no spaces = no brackets.

 

[boblarson]

as a side note, you are making more work for yourself with spaces in your field and table names. That requires bracketing field/table names while no spaces = no brackets.

^^^^^^^^^^ +1 ^^^^^^^^^^^^^

 

[doddy88]

Hi Gina,

Thanks for your tip in regards to field and table names etc.

I'm having problems with the table and relationship setup. As I mentioned one student can have many employees and one student can have more than one contact details but an employee can only have one student and contact details only belong to one student. So I believe I only need one-to-many relationships. Do I still need the joining tables?

I currently have it as:

tblStudents
StudentID - Number

tblContactDetails
ContactReferenceNumber - Autonumber

tblEmployerInfo
EmployerID - Autonumber

tblStudentContacts
scStudentID - Lookup Wizard linked to tblstudents - StudentID.
scContactReferenceNumber - Number/Long Integer

tblStudentEmployers
scStudentID - Loookup Wizard linked to tblstudents - StudentID.
scEmployerID - Number/Long Integer

Joining type wise, from studentID to scStudentID they both set as one-to-many but from EmployerID to scEmployerID and ContactReferenceNumber to scContactReferenceNumber they join as a one-to-one relationship. Is this right?

It doesn't allow me to add a contact details record on the tblContactDetails because of the relationship I think.

Regards

 

[GinaWhipp]

Yes, you do because of the MANY side. The *JOIN* tables are Many-to-Many not One-to-Many Please do not use the Lookup wizard, it will give you issues down the road!

You can't add in the JOIN tables unless it exists in the PRIMARY table. The JOIN tble is simply to join two tables together because you do not want to repeat records in the PRIMARY table. Am I making sense?