MS Access Form Questions

[BSkeens]

Hi,

I have a few questions regarding the use of forms in Access DBs. I'm currently trying to build a DB for work and had some questions on whether or not some desired functions can be used within forms.

• My database currently has five Tables (one table for each employee on my team) that manage my employee's work (volume, status on different tasks, etc...)
• I need to create a form for them to use to input new line-items into their personal tables.

So here are my questions:

• How can I set up a combo-box on a form to select employee names and route the new line item on the form to that employee's table? Is this sort of thing even possible? For example, if John Doe is selected on the form, how can I ensure the rest of the forms data inputs are routed to John Doe's Table?
• I have a table referencing our clients' account information (Client Name, Address, etc...). Is there a way I can set up a search or "Lookup" field on the form so that when you enter a client's ID# it will automatically populate the rest of that client's info on the form?
• Would there be a way for me to put a field (such as quantity) on the form and have it create multiple line-items (matching the number given)? For example, if some wanted to create three identical line items, is there a way to have a drop-down box or combobox and have them select 3 and when they hit the "Add New Record" button it will populate three times?

Any help or other suggestions on approaching the above problem would be greatly appreciated.

[pbaldy]

For starters, you should not have a table for each employee, you should have a single table with a field to denote the employee.

This will help with the second:

http://www.baldyweb.com/Autofill.htm

For the third, yes. You can use a For/Next loop and the AddNew method of a recordset.

[BSkeens]

Thanks for the tips. The thought behind having a table for each employee was so they could go into their own tables and update status at any point in time. Should I build the form in a way that they can call on the specific line item for the task they which to update and use the form to update that line item from the master employee list on the DB?

And for the other two I think that will definitely work. Thank you for the tips.

[pbaldy]

The principle is normalization:

Fundamentals of Relational Database Design -- r937.com

You don't want to have to add a table, etc every time you add an employee (normal course-of-business changes shouldn't require design changes to the app). You can filter the form to a specific employee in any number of ways.