Populating more than one table from a combo box selection

Topic

New Reply

One of my co-workers asked me how to do this and I don’t know.

She has a database to track employee security clearances. From our HR dept, she imports a temporary table of employee data (first and last name, department, location, hire date, employee number) to the db once a month or so.

She has a data entry form and created a combo box to pick an employee’s name from the temp table. When she picks the name, she wants the other fields for that record in the temp table to write to her master employee data table. She doesn’t need all the employees in the temp table added, which is why she wants to use the form to pick the ones she wants.

Does this explanation make sense to you?

Reply | Quote

Replies

Adding a record to the master table automatically when the user selects a name in the combo box is dangerous – it leaves no room for correcting mistakes. I would place a command button next to the combo box. The user selects a name, then clicks the command button to add the record to the master table.

The code behind the command button could execute an SQL statement that begins with INSERT INTO. The details depend on the table and field names, of course.

Reply | Quote

One of my co-workers asked me how to do this and I don’t know.

She has a database to track employee security clearances. From our HR dept, she imports a temporary table of employee data (first and last name, department, location, hire date, employee number) to the db once a month or so.

She has a data entry form and created a combo box to pick an employee’s name from the temp table. When she picks the name, she wants the other fields for that record in the temp table to write to her master employee data table. She doesn’t need all the employees in the temp table added, which is why she wants to use the form to pick the ones she wants.

Does this explanation make sense to you?

I would tend to create another column in the temp table called Xfer which is a yes/no field.

I would base a form on the temp table and click the yes/no fields for the records you want to xfer to the main table.

This gives you a visual of all records before you xfer them. If you have made any mistakes you just clear or set the apropriate yes/o field accordingly.

I would put a button on the form that has a SQL script to append to the main table if the xfer field is True.

Reply | Quote