Data entry via query subform

[AKawser]

Hi Guys,

I am currently creating a database based on University projects. As an overview, the tables i am using are:


Projects: Contains details of projects such as costs, dates, department etc
Staff: Contains a list of staff members within the university
AssignedStaff (Associate table): Contains project and staff IDs, staff role for that project and whether they are leading the project.
Staff and Project have a many-to-many relationship.

I have created a project main form. In order to show the staff member associated to this project (name etc) i have created a query which puts the staff details (Staff table) and role (AssignedStaff table) together. From this i have created a subform on the projects form. My problem is, how do i actually insert data into this subform appending the associated table. I have created combo boxes which are linked to the tables however I get a "cannot add record: Join key of *table* not in recordset".




Thanks

[rpeare]

if you're using a form/subform arrangement it's by definition a one to may relationship (one project, many staff), although you can have staff assigned to multiple projects that does not make it a many to many. A many to many example might be if you had debits and credits on an account stored on separate tables, a person might have many credits and many debits associated to the same person. Your restrictions on entering data may be in relation to your table setup. For instance if you have a required field and you leave it blank or choose an option that is not in a combo box list you will likely get an error. I suspect this is more likely that you are using a query for your subform when it likely does not need to be, you should be able to choose a staff member from a single combo box rather than choosing first name/last name. In other words, in your subform you would have 2 combo boxes (person would be a combo box with the StaffID and the first and last name concantenated, like Staffname: Lastname & ", " & firstname) containing the person and the role. Then your subform should only need to be based on your assigned staff table and not a query.

[AKawser]

Hi, thanks for the reply. Does this mean that the associate table is incorrect as it is not a many to many? I will attempt to construct a sub-form as you have suggested. Also i will be having a form for staff members and the project they are associated to, wouldn't that cause duplication?

[rpeare]

I try not to use multiple field primary keys in my table structure, I would have a 'assignedstaffID' in the tblAssignedStaff as the primary key and use the staffID/projectID as the unique key. I also don't set up relationships, I don't really find them that useful. But other than that the only issue I see is your 'grant' table. What is that supposed to be tracking, if it's a subset of the project data why not include it on the project table. If it's tracking something else what is it supposed to be tracking. Shouldn't have you have a 'grant source' table with a primary key and assign the project and grant sources (if it's possible to have a project with more than one grant source) in the 'grant' table?

As to your question about causing duplicates, no, nothing will 'cause' duplicates you can limit what gets into your tables by either your table design or your data entry design and it is always possible to design queries to show just what you want, in this case you would be looking up from the MANY side to the ONE side of a table and showing just the projects relating to a specific staff ID.

[AKawser]

The grants table is a subset of the project table, however it is only used based on a whether a projects status is "Successful" and therefore applies to a handful of projects. Not sure if this is the correct practice or not but i can easily fix that, i was just concerned about having a lot of blank fields in the project table .

I feel like i'm drawing closer to a solution with my question where the staff and role is shown with the subform. However now i have other problems:
1. From my staffAssigned table i use the StaffID field to identify the staff, concatenating first and surname, however It doesn't allow me to store this data as the staff ID is numeric only. Hmmm...
2. The list which comes from the combo-box is a small selection associated to the AssignedStaff table as opposed the whole staff list which i want, and its duplicating some names.

Thanks and sorry i really lack this knowledge haha

[rpeare]

Ok, if the grant only applies to a handful of projects and it's a 1 to 1 relationship then there's no problem with that part of your table structure.

With your StaffID the SQL driving that combo box should be

SELECT StaffID, Surname & ", " & ForeName as StaffName FROM Staff

Just set the number of columns to 2, set the column widths to 0", 2" (or whatever an appropriate width would be) to suppress the staffID from showing. This allows you to store the staffID but show the staff name.

[AKawser]

I can see the whole list now however i am still unable to select them. I assume this is due to the field being set to number? so i will need to get rid of the relationships and set them to text in order for this to work?

[AKawser]

I have changed it to text but i get a:

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

Expression i have put in is :
SELECT [StaffID] & ", " & [Surname] & ", " & [Forename] AS StaffName FROM Staff;

[AKawser]

No problem i have it sorted. Thanks!!!