Updating with a subform

[lipin]

We needed a way for supervisors to enter attendance for their departments. So a I have a main form with a drop down list of sups. They pick and hit the command button to view a list of the employees they have. This command button on main form runs query of Employees table. Query has 3 fields Emp Name, Emp #, and Supervisor. The supervisor criteria comes from the drop down. That part works. The Employee name and # are the first two fields in datahseet view of subform. So I added 3 more fields on subform: Code, Hrs, and Comment. They will need to enter the data daily.

The problem I am having is saving the data. The control source for the Empl name and # is the Empl name and number field from the query. The 3 fields I added are unbound and i go to control source and the only two choices are name and number. I have a table set up to save the fields to. But that is not the source.

Basically, I think I need to change control source for the subform from the query that lists the employees to the table. But then how do I get the employee list to populate if those two fields' control source is the table? Sorry so long winded.

 

[PookaTech]

Close your database. Open it and open the query the form is based on, add the three new fields from the table to the query (this should be a simple matter of grabbing them from the table list above and dragging them into the query) save the query and close it. Now open your form that is based on the query. You should be able to set the control source to the new fields which you just added to your query.

Pookatech

 

[lipin]

Well the three fields that are unbound are not in the query that provides the employee name and number. That is all that query is for. It lists all the employees in the department.

But then I want to enter data in the other 3 textboxes and save all five as a record in another table.

But the control source for two records is already the query that populates them so how can I save all five records in a new table?

 

[PookaTech]

I think I understand the problem now. There are two ways you might tackle it. One, use an append query with fields set to the form controls. Then have an after update event, or a command button that shoots off the query.
The other is to have a main form with the query with all the employees in the department as the source. Then have a subform, absed on the the table with 5 fields, which shows the hours, code and comment field for each employee.
I like the second method because the supervisor can be sure they have entered the record and see previous records they have added. If you want the supervisor to be able to see their employees more easily, you could have the employees in a list box on the main form and have the double click event for the list box move the form to the selected employee.

Best of Luck,
Pookatech