Drop Down menu selection populates continious form

[BRASILO]

I have 3 tables. table one contains supervisors info, table 2 contains employees and table 3 stores the input from the continuous form.



I have a drop down menu that has the supervisor names on it. I'm trying to get my continuous form to auto populate the employees assigned to each when a supervisor is selected. I've tried several different ways. I tried creating a query that is based on drop down menu selection to auto populate the continuous list. However, every time I select I'm prompted again for the assigned selection. Can someone help getting this to work? Any new ways is welcomed as well.

[June7]

Have to show something for us to analyze. Query SQL, VBA, whatever is related to the issue.

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

[BRASILO]

Have to show something for us to analyze. Query SQL, VBA, whatever is related to the issue.

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

Sounds good June7. I'll prepare a Db and upload it tomorrow. Thanks for tackling this issue.

[BRASILO]

Have to show something for us to analyze. Query SQL, VBA, whatever is related to the issue.

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

I'm having some issues uploading my test DB for you to look at. I guess I'll have to explore a different way to explain how I want my form to populate the list of people whom are assigned to particular team when that team is selected from a drop down menu.

[June7]

I never use dynamic parameterized query. See if this helps http://allenbrowne.com/ser-62.html

[BRASILO]

I never use dynamic parameterized query. See if this helps http://allenbrowne.com/ser-62.html

is there a way to turn that form into an input form/Data entry? I believe I have explored that particular search form.

[aytee111]

Guessing here - is table3 the lookup between employeeID and supervisorID? Main form would have supervisor table as its record source, if you want to make changes to the supervisor table, otherwise it could be an unbound form. It will contain the supervisorID in a combobox.

The subform will have table3 (joined to employees to get the name - if no editing is allowed) as its record source and will be linked via the supervisorID to the main form. When a supervisorID changes on the main form, the subform will automatically populate based on this format.

[BRASILO]

Guessing here - is table3 the lookup between employeeID and supervisorID? Main form would have supervisor table as its record source, if you want to make changes to the supervisor table, otherwise it could be an unbound form. It will contain the supervisorID in a combobox.

The subform will have table3 (joined to employees to get the name - if no editing is allowed) as its record source and will be linked via the supervisorID to the main form. When a supervisorID changes on the main form, the subform will automatically populate based on this format.

Thanks for the advise. I certainly did try that. I can populate to show with previously entered record. However, the goal is to have a population to show at data entry.

[June7]

Yes, the form demonstrated in the Allen Browne tutorial can also be used to enter/edit data as well as to search/filter existing records.

Really need a better understanding of your data structure. A database that associates employees with supervisors is often a recursive type utilizing table self-join. Review http://www.tomjewett.com/dbdesign/db...=recursive.php

[BRASILO]

Yes, the form demonstrated in the Allen Browne tutorial can also be used to enter/edit data as well as to search/filter existing records.

Really need a better understanding of your data structure. A database that associates employees with supervisors is often a recursive type utilizing table self-join. Review http://www.tomjewett.com/dbdesign/db...=recursive.php

Thanks for the link. I found it very helpful. I tried again today to upload the test Db. It's not working for me. Could be the policies on my comp prevent it.

The idea of that form is perfect. I played around trying to figure out a way to create an input/data entry form based on that example yesterday. However, it was an epic fail. I'm not very skilled with writing SQLs, but I'll give it a try today. Maybe I can create a query that will allow the population of the continuous form once I select an item from the drop down menu. I'm mostly exploring subform input approach, however, I'm sure there is ways to do this without it.

I'll try to breakdown the tables structures below.
Data Structure: They are either short, date and number formats.
Table 1: supervisors: 3 columns - ID, supID, supName - This data is for the Drop down menu
Table 2: employees: 4 columns - ID, supID, empID, empName - This data is being referenced to populate the subform's field employee name
Table 3: inputData: 6 columns - ID, empID, startDate, endDate, inputType, inputNumber - this store the input from the subform

[Minty]

As a suggestion, you don't really need the supervisor table, as I would assume that any supervisor is also an employee.

So simply store the EmployeeId as the SupervisorID in your Employees table.
If you need to record Who supervised who over time then you will need a third table, with EmployeeID,SuperId(Which is actually the EmpID of the supervisor), StartEnd and EndDate

I'm also confused as to why you have an ID field and and EmpID field in your employees table. Are they not the same thing, or is it a Payroll number type thing?

[BRASILO]

As a suggestion, you don't really need the supervisor table, as I would assume that any supervisor is also an employee.

So simply store the EmployeeId as the SupervisorID in your Employees table.
If you need to record Who supervised who over time then you will need a third table, with EmployeeID,SuperId(Which is actually the EmpID of the supervisor), StartEnd and EndDate

I'm also confused as to why you have an ID field and and EmpID field in your employees table. Are they not the same thing, or is it a Payroll number type thing?

Thanks for the suggestions. However, those fields are needed.

[Minty]

Thanks for the suggestions. However, those fields are needed.

Not if you want to normalise your data correctly. You are storing the employee name in two places, once in your main employee table, and again in the supervisor table.
This is not normalised. Bert Smith the employee, is still Bert Smith when he's a supervisor.
His employee information goes with him regardless of him being a supervisor or not.

You can easily produce a list of all supervisors by simply querying the supervisor ID stored in the employees table, and linking it back to the employee table to get their names.

[June7]

EmpID would be info about the employee, if you want to generate and assign an identifier that would have meaning to employee and db users and don't want to use SSN as this identifier. The ID field could be autonumber primary key used as link to related dependent tables, such as EmpPayroll or EmpTravelExp, etc.

[BRASILO]

Not if you want to normalise your data correctly. You are storing the employee name in two places, once in your main employee table, and again in the supervisor table.
This is not normalised. Bert Smith the employee, is still Bert Smith when he's a supervisor.
His employee information goes with him regardless of him being a supervisor or not.

You can easily produce a list of all supervisors by simply querying the supervisor ID stored in the employees table, and linking it back to the employee table to get their names.

Could you provide a sample DB explaining this?