Multiple fields in table

[thart21]

I'm am stumped as to how to do this.

I have a table "tblStaff" and a table "tblProjects". My form is bound to tblProjects and contains 6 cbos all bound to tblStaff which represent different job roles on the project. The "staffid" is what I am storing in tblProjects for each of the six fields. I need to create a query which will output the actual names of the staff but cannot figure out how to link the two tables together.

If you know of a thread that explains this, please point me to it. I wasn't sure what to even call what I am trying to do but all my searches have failed.

Thanks!

 

[thart21]

Thanks Pat, I think I have it.

I have tblStaff and tblJobRole, created a junction table with the projectid, staffid and jobroleid and can tie this to my main project table with the project id and eliminate the 6 fields in my project table.

Sound right?

Toni

 

[thart21]

Can't seem to get my form to update my tables. Would appreciate someone giving me some suggestions on how to make this work.

I have tblProjects and tblStaffJunction (which contains AutoNumber id fields and the PK from tblProjects,tblStaff and tblJobs). I created a query from tblProjects and tblStaffJunction and based my form on that.

There are six combo boxes (one for each job role) each with the row source SELECT [tblStaff].[staffid], [tblStaff].[lastname] & ", " & [tblStaff].[firstname] FROM tblStaff ORDER BY [tblStaff].[lastname];
How should I bind these fields?

I just want to be able to update each job role combo (along with all of the other form fields from tblProjects) with the appropriate staff name and have it update the jobroleid and staffid fields in my tblStaffJunction table and I cannot figure out what I am doing wrong. I can change the fields, but my tables will not update.

What do I use as the Control Source for my six cbo's? If I use jobroleid from my query and change one field in the form, they all change to the same choice.

Thanks!

 

[thart21]

Unfortunately,the layout requirement of the form won't allow me to use a subform so I will try the ADO/DAO code. I am struggling with this however. If my tblStaffJunction only has fields staffid and jobroleid and my 6 fields are named projectname,pce_eng, ffpbuyer,ccmbuyer, etc., how do I set up an INSERT INTO statement to create 6 new records in tblStaffJunction with the jobroleid and staffid for each, or better yet, what "keyword" should I look up in the forum to find a possible thread on this?

Thanks for your help Pat!

 

[thart21]

You're right, that IS complicated. But, if I use a subform, how do I get a column for each job role since the job roles are just records in my jobs table?
It's just ging to shows up in the subform with the column jobrole and then 6 records? Or, are you saying that I can only have one combo on the form?

I don't see how this is possible. I would also have to make them combo boxes and fill them with the data from tblStaff.

Thanks

 

[thart21]

I've got something that works somewhat but I know there must be a better way. I have been through hundreds of posts on AddNew and ADO and SQL Insert and can find nothing about inserting/creating multiple records into fields in a junction table and then tying that to my main table.

I used this code and copied it 6 times for my 6 cbos, changing the relevant values.
DoCmd.RunSQL "INSERT INTO tblStaffJunction(id,staffid,jobroleid)" & _
"VALUES('" & Me.ID & "','" & Me.projectleader & "','" & "1" & "')"

This works to update my junction table but how do I bind the newly entered data to my form so it appears the next time I open it? What am I missing here??
I have 1 to M relationships between tblStaff and tblStaffJunction, tblJobRole and tblStaffJunction and tblStaffJunction and tblProjects.

My form is based tblProjects.

I think I'm chipping away on this, just ran into a wall.

Thanks for your time and help.

Toni

 

[tone21]

Thanks for the suggestion. To make sure I understand correctly, I have done the following:

Created 6 queries using the tables - tblJobs,tblNames,tblJunction. Fields are tbljobs.jobid,tblJunction.projectid, tblNames.name, giving projectid an alias for each job role. I then created a query using all 6 of these queries plus qryprojects which contains all the project details from tblProjects. I used a left join from the projectid in qryprojects to the projectid in each job query.

The query looks right as it shows the project id, each job role and the corresponding nameid assigned to the job role.

I then went to my form and bound each of my six job role cbos to the corresponding job role in the query and used my SELECT statement to fill the cbo choices from tblNames

Now, if I leave the form "recordset type" to Dynaset, I cannot change any of the fields. If I change it to Dynaset-Inconsistent Updates" it will let me change them, but won't update and I get the message "changes requested to the table were not successful because they would create duplicate values in the index, pk or relationship....I'm sure you know the rest.

In the Before Update event for cboleader I have Me.leader = "leader"(the alias name I gave this field in my query) . I think I need some clarification on this one. I know that the Me.leader is my cbo, but the = is the alias name I gave in the query? I am getting a runtime error message "Macro or function set to the Before Update or ValidationRule property for this fields is preventing MS from saving data in the field." when I use this code.

Any ideas? Is it trying to create another project with the same id?

I think I may end up having to go with the subform as much as I don't want to. But I would like to figure this out someday for future use.

Thanks for your help again,

Toni

 

[thart21]

Time to swallow my pride and just get this thing done....

Here are my queries:

On the Projects form, I have bound each job role cbo to the field alias in "qryProjectsandStaff". I put in the Before Update and On Enter code and am still getting the runtime error "Macro or function set to the Before Update or ValidationRule property for this field is preventing MS from saving data.."

Before Update
Me.leader(name of cbo) = "1" (criteria set in the individual job role qry) I have tried '1' and 1 as well-no change.

Thanks for your help.

Toni

 

[thart21]

Thank you so much Pat - this worked great! I now understand much more about how to relate fields to each other and am using this in many of my db's now. Your help and patience as always is appreciated

Toni