I have a subform that must default to a datasheet view so that the end-user may hide and un-hide columns at will. The structure of this subform should be that when the form opens, there is no actual data showing, yet, there is a text field for employee #, a combo box for employee last name, and another text field showing hours worked. I need the form to have this functionality: once the end-user selects an item from the employee last name combo box, the form is populated with an entire record (including the corresponding employee #, hours, etc.) from a specific table using the selected employee last name as a filter. Then, new records can populate the form, including employee #, hours worked, etc. every time the end-user clicks on a command button from the PARENT FORM (since the subform is in datasheet view and can't show command buttons), triggering a new row, and once again allowing the end-user to select a different employee last name. I have significant experience with MS Access and know some SQL yet am a novice with VBA so, if you can help, could you please be explicit with the code you provide? Thank you so much!

Hi,

   What is the table structure behind the form/subform?

Let me see if I understand this:

You have a form in normal form view, with a subform in datasheet view.

1. The parent form should open with no records to display (Why?)
2. A combo box on the parent form should allow you to select an employee by last name.
3. Once the employee is selected, you want the parent form to display the employee name, employee #, and a total of hours worked. (Where od the hours worked come from?)
4. You want the subform to display entries for hours worked? For one employee at a time, not allowing the user to enter data for many employees on the subform? Or are the employees on the subform working for the employee on the parent form? Still, who's hours are totalled?
5. Datasheet subforms always allow creation of new records without using a button on the parent form.

Many questions...

mmcdonal

 What is the table structure behind the form/subform?

There is a Parent Form, a child subform, and a second child subform. The parent form is only there to tie together the two children and contains a hidden id to do so. Trust me, it is essential to do this for another functionality reason. The one relevant child subform (the other is only there to show a single record from the other subform) has a table for a recordsource with the structure:

employee # - text field (char)- reference field that ids the record
employee name - text field (char)-reference field
hours - number (integer)- end user enters hours here to populate table
amounts - number (currency or double)- end user enters dollars to populate table.


Let me see if I understand this:

You have a form in normal form view, with a subform in datasheet view.

1. The parent form should open with no records to display (Why?)It's not the parent form that should open; it's the subform. No records should be there but the employee name filed should have a combo box so that the user can choose an employee name, and then I need to program it to bring up the employee number.

2. A combo box on the parent form should allow you to select an employee by last name. A combo box on the subform should allow you to select employee by last name.

3. Once the employee is selected, you want the parent form to display the employee name, employee #, and a total of hours worked. (Where od the hours worked come from?)I want the subform to display the employee name, employee #. The hours works have to be entered by the end-user

4. You want the subform to display entries for hours worked? For one employee at a time, not allowing the user to enter data for many employees on the subform? Or are the employees on the subform working for the employee on the parent form? Still, who's hours are totalled?

The user should be able to see a continuous listing of employee records they have selected on the subform. Hours are entered by the end-user per employee #
5. Datasheet subforms always allow creation of new records without using a button on the parent form.

Remember that this is in form view with the properties tab format set to datasheet under default view.

I still don't understand the table structure underneath this. You only listed one table.

I still don't understand what you are trying to do. Why are you using controls on a subform to select a new employee for the main form? The main form should reference one employee at a time.

Are you recording hours for a higher organization, like a project, or a department? If so, you still wouldn't need this structure.


mmcdonal