Need help with creating query & form

[mcomp72]

Hi everyone. I’ve run into a situation with my database that I’m not sure how to solve.

First, a little background on what I’m doing: I’ve created a database to electronically handle some employee paperwork, so that my employees don’t have to fill it all out each time they do a job with me. (I work on Film/TV productions, and I do many different jobs each year. The crew members have to fill out the same paperwork every time, and it’s a bit of a pain.) That part of the database is done and works great. I now want to add their timecards to the database, both to help me with the calculations of them and to be able to do them paperlessly. I’ve created the Table for the Timecards, but am now trying to create the Form, and I’ve reached a point where I’m not sure how to do it. I guess I don’t understand how database tables relate to one another well enough, because I can’t seem to figure out how to properly create the combo boxes that I want.

I know it would probably be helpful for you to be able to see my table relationships, so I’ve created a PDF laying it all out. You can find it here:
https://www.dropbox.com/s/7iptj64eoc...ships.pdf?dl=0



Here’s what I’m trying to do on my Timecard form:
- First, I want a combo box that allows the user to specify which Job the timecard is for. (This should be a combination of the job name followed by the company name who is producing the job. I’ve done a combo box that combines the two on one of my other forms, and it works, but here it’s a bit trickier, as I’ll explain below.)
- Next, I want a combo box where I can select which employee the timecard is for, but I only want the combo box to list the employees that are currently working on the particular job that has been selected in the combo box above. Employees are “assigned” to a job on the EmployeesOnJobs table.
- Then, I want certain information about that employee to automatically fill in on certain areas of the form. That information would be pulled from the EmployeesOnJobs Table and the various tables that have relationships with that table.

I have created a query to make this Timecard form with, and I have included the various items from the different Tables that I would need. However, I can’t figure out how to create those first two combo boxes that I mentioned. (I know how to create combo boxes in general, but I don’t know what my Control Source and Row Source should be for these.)

I hope this is clear. It’s definitely getting complicated for me. I'm sure this is due to my limited knowledge of how databases/Access works. I’d really love to try to understand it so I can add this functionality to the database. Anyone able to explain how I would do what I’m wanting to do?

[June7]

Sounds like you need cascading (dependent) comboboxes. This is any extremely common topic. Search forum and web.

[mcomp72]

Thanks. I'll search around and see what I can find.

[ArviLaanemets]

Here is one possible way to do this

[mcomp72]

Here is one possible way to do this

Wow... I can't thank you enough for spending the time to make this. It's VERY helpful. I will probably have questions about it, and if so, I'll pop them here. Thanks again!!

[mcomp72]

I'm trying to create my own SQL statement for the filtering of a third combo box to choose the correct Position Title of the Employee (in case the person works two positions on the same job, which occasionally happens), and I am getting a syntax error. I can't figure out what is wrong with the statement. Any ideas?

Me.PositionComboBox.RowSource = "SELECT PositionID, PositionTitle " & _
                                "FROM "[Q_Employees on Jobs] " & _
                                "WHERE JobID = " & Me.JobNameComboBox & " AND EmployeeID = " & Me.EmployeeNameComboBox & " " & _
                                "ORDER BY PositionTitle;"

[June7]

Remove the " after FROM

[mcomp72]

Wow. I can't believe I didn't see that. Thank you. I guess after staring at something for so long, sometimes you just can't see what should be so obvious!!

[ArviLaanemets]

I haven't access at home available, so I can't check it currently.

In my example db I assumed en employee can have only one title on job. When you look at indexes, there is defined an unique index based on JobID and EmplID. When employee can wear several hats at same job, the whole setup must be corrected - starting with adding TitleID to unique id. But probably you don't need a 3rd combo - you simply have to edit the second one so that it displays title for employee too - i.e. whe an employee has 2 titles, the user can select the same employee with either of them. The second combo returns not EmplID, it returns ?RowID, and in this case the employee has 2 different rows in table (don't remember what names I used for tables and fields, and can't look up them!).

[June7]

Happens to the best of us

Something to think about is a common practice for identifying control or data types or objects is to use a 3-letter prefix, such as cbxEmpID, tblEmployees, frmEmployees, etc.

[mcomp72]

I haven't access at home available, so I can't check it currently.

In my example db I assumed en employee can have only one title on job. When you look at indexes, there is defined an unique index based on JobID and EmplID. When employee can wear several hats at same job, the whole setup must be corrected - starting with adding TitleID to unique id. But probably you don't need a 3rd combo - you simply have to edit the second one so that it displays title for employee too - i.e. whe an employee has 2 titles, the user can select the same employee with either of them. The second combo returns not EmplID, it returns ?RowID, and in this case the employee has 2 different rows in table (don't remember what names I used for tables and fields, and can't look up them!).

Good idea about the 2nd combo box displaying both the Employee name AND their Position title! I think I can figure out how to do that. If not, I'll post back here.

[mcomp72]

Something to think about is a common practice for identifying control or data types or objects is to use a 3-letter prefix, such as cbxEmpID, tblEmployees, frmEmployees, etc.

Ah -- good to know, thanks.