Changing Multiple Control Sources in a Form (1 Viewer)

[sphynx]

I have a form in my current project that I need to copy and use to control data input into 10 different tables, i.e 1 form for each table.

Each form has 168 text boxes which I need to change the Control Source and name of.

I know how to change a forms control source and an individual controls sources and names, what I would like advice on is if it is possible to change all form references in one go, I'm thinking like find & replace in Excel or VBA

Any advice would be greatly received as i don't relish the idea of manually changing over 3000 control sources and names!!!!!!!!!!!!!!

 

[DCrake]

So for every form there is a table, each with 168 fields, all said fields relate to a specific control on the form?

You want to use one form to populate a specific table dynamically?

Are the fields in each table share the same names?

If so why is the data in so many tables when you could have 1 extra field denoting the source data id.

A bit more info would be ideal.

 

[sphynx]

The database in question is for recording staff training

There are 10 departments for staff to work in
There are up to 28 individual tasks for each person to train in and a date that the person has been trained stored within the tables for each and every employee.

Therefore I have 10 tables, 1 for each departments training records. I have done it this way due to people moving departments and a need to keep a historic reference of the skills they have been trained in within each area.

The form in question pulls the data from the area the member of staff is currently assigned to and displays all the skills they are trained in, the date they were trained. I also via VBA calculate whether they are due retraining and the % they are trained within there area. this is also displayed on the form.

I have done it this way as it needs to be very visual and easy to interpret as it will be used on the shop floor by our trainers,

If I could create a dynamic form to pull the correct records for the correct person in relation to there department that would be great.

the problem is I have no idea how to do that what so ever!!

Does the above make more sense now?

 

[DCrake]

You need to go down the normalisation route.

Table1

Departments:
Department Id - PK
Department Name


Table2
Employees
EmployeeID - PK
Employee Name


Table3
Training
ExerciseId = PK
ExerciseNum
Exercise Name


Main Data:
DeptID FK
StaffID FK
TrainId FK
TrainNo

All the fields in the main data would make up the PK

Then if you employ grouping in your form you could bring up all staff who work in which depertment

If an employee moves from one department to another you can still retain the information in the main table simply add them again to the maindata under a different department id and make that the current department by having a boolean flag to denote that this is the current department.

This would also mean that you would only need one table for current and historic data. Don't forget if you make a change to one table in your current setup you will have to replicate it for all your tables.

CodeMaster:


All the

 

[sphynx]

Thanks

This is only my second project and I am self taugh so all help is greatly received

 

[sphynx]

I have done some research on normalization and have changed my database accordingly.

It is now far easier to work with, thanks for the advice