Drop Down menu selection populates continious form

[Homegrownandy]

Do you need to store additional info if he is a supervisor? Or do you just want to know he is one?



If you dont need any addtional info there may be no needfor the supervisors table at all. (feel free to correct me if im wrong).

you could have a checkbox for each record where yes = supervisor and no = not.

OR.. lets say you want to know supervisors/managers/labourers/whatever. I wouldnt do a checkbox for each, I would create a table for defining their "role".

you would then be able to select the role for each member of staff, the table would only store the ID.

If you require more information than just what their job title is, then look at creating a junction table inbetween "staff" and "job title".

Thats how i would approach it anyway.

[June7]

The article referenced in post 9 provides an example db structure and sample query.

[BRASILO]

The article referenced in post 9 provides an example db structure and sample query.

Hello,

Based on that recursive explanation. I was able to get a form partially working. In this form, I can pull employee data associated with supervisors. However, that is the extent of it. I was hoping to pre-populate a list of employees based on the supervisor selection of the drop down menu inside a Data entry form. This is where I'm still stuck on. If the table has data, the continuous form will show that. Not my end goal at this point. I was hoping for the list to show as a selection was made and the rest of the fields that need to be filled will be blank except the employee name.

Any ideas?

[BRASILO]

Do you need to store additional info if he is a supervisor? Or do you just want to know he is one?

If you dont need any addtional info there may be no needfor the supervisors table at all. (feel free to correct me if im wrong).

you could have a checkbox for each record where yes = supervisor and no = not.

OR.. lets say you want to know supervisors/managers/labourers/whatever. I wouldnt do a checkbox for each, I would create a table for defining their "role".

you would then be able to select the role for each member of staff, the table would only store the ID.

If you require more information than just what their job title is, then look at creating a junction table inbetween "staff" and "job title".

Thats how i would approach it anyway.

I'll give this a try, I'll let you know how it works. If you happen to have an example Db so I can understand better; that would be awesome.

[June7]

What do you mean by 'pre-populate a list of employees'? Do you want a combobox to list only employees associated with selected supervisor? This is conditional (dependent) combobox. A very common topic.

Or do you want to create a set of new records for all employees associated with selected supervisor? This can be done with an SQL action executed in VBA. Do you also need a date saved in the new records? Something like:

CurrentDb.Execute "INSERT INTO datatable(DateField, EmpID) SELECT Date(), EmpID FROM Employees WHERE SupID=" & Me.cbxSub

The real trick is figuring out what event to put code in.

[BRASILO]

What do you mean by 'pre-populate a list of employees'? Do you want a combobox to list only employees associated with selected supervisor? This is conditional (dependent) combobox. A very common topic.

Or do you want to create a set of new records for all employees associated with selected supervisor? This can be done with and SQL action executed in VBA. Do you also need a date saved in the new records? Something like:

CurrentDb.Execute "INSERT INTO datatable(DateField, EmpID) SELECT Date(), EmpID FROM Employees WHERE SupID=" & Me.cbxSub

The real trick is figuring out what event to put code in.

Thanks for the quick reply.

The drop down menu contains the supervisors names. The subform or form would populate the list of employees associated with that supervisor selected and then the others fields to the right of the employee field name would show up blank and ready for data input. a button in the form would be there for saving all the entries as a record.

I like that SQL you provided, I'll try to use that and modify to see if I can get it to work with command button for saving the record entries. However, before I can get there, I still need to get my continuous form to populate the names correctly. I'm still not getting it to work right. Only 1 line shows up and it's not allowing me to select or load the employee name. I'm slowing trying different approaches.

[June7]

Would need to know more about your data structure and form design and code.

If you want to provide db for analysis, follow instructions at bottom of my post.

Also, need to determine whether saving supvID into records is desirable. The supv associated with emp can always be retrieved in query but I presume supv assignment could change over time and might not want that to impact existing records.

[BRASILO]

Would need to know more about your data structure and form design and code.

If you want to provide db for analysis, follow instructions at bottom of my post.

Also, need to determine whether saving supvID into records is desirable. The supv associated with emp can always be retrieved in query but I presume supv assignment could change over time and might not want that to impact existing records.

In the past my computer didn't allow me to upload Db samples to this site. Not sure why, I've tried multiple times. However, I'll give it another go.

[BRASILO]

Would need to know more about your data structure and form design and code.

If you want to provide db for analysis, follow instructions at bottom of my post.

Also, need to determine whether saving supvID into records is desirable. The supv associated with emp can always be retrieved in query but I presume supv assignment could change over time and might not want that to impact existing records.

I was able to find a solution to this. However, I'm unable to upload a copy of my solution. It was very complicated. it required queries, VBA and form with sub form to get where I was trying to get.

[DevilDawg]

On your continuous form, set the recordset to the table that contains your employees. Then add the continuous form as a sub form and link it by the supervisor ID. I definitely agree with Minty on the normalization. All you'd really need to do is add a new Boolean field that says they are a supervisor. Then, your drop down merely needs to be based on a query where

supervisor = -1

. You are one more step closer to normalization and save yourself a potential headache down the line.

[mike60smart]

You are replying to a 5 year old thread!!