Static Autofill using forms (1 Viewer)

[NickS]

Hello there!

I am reletively new to database design and I am hoping to make robust data tracking systems which will serve my workgroup for a long time to come.

I have two tables relevant to this question: Employees and Evaluations.

Employees has the following fields: EE ID, EE Name, Job Title, Supervisor, Workgroup

Evaluations has the following fields: Eval ID, EE ID, Eval Date, Supervisor, Date Approved

I would like to have "Supervisor" stored in both tables because the supervisor may change but the supervisor who completed a particular evaluation would not have changed. Also, if the employee had a new supervisor a person other than his or her supervisor might write his or her evaluation.

So, I would like to have the form autopopulate the Evaluations.Supervisor with the information from the Employee table which corresponds to the EE ID I select on the form. I would like to be able to overide that entry, and I would like the entry to be static (I don't want it to change when I change the supervisor on the "Employees" table).

Can anyone help?

Thanks,

NickS

 

[pbaldy]

See if this helps:

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

 

[Pat Hartman]

serve my workgroup for a long time to come

If you start with a sound schema and professional naming convention you'll have a good base for growth.

There are lots of recommendations but they boil down to
No spaces or special characters.
Use only a-z, A-Z, 0-9, and the underscore "_"
Common styles are CamelCase (MyPreference) and The_Underscore (I_Dont_Like_It)
You also need to avoid reserved words especially the names of properties and functions. Name, Date, Month, and Year are the most common problems. Create your names using an adjective and a noun and you'll avoid most conflicts without having to worry about memorizing a list. CustName, StreetName, TranDate.
And finally, if you use abbreviations, be consistant. CustomerName and CustAddress will just strain your brain for no good reason.

 

[NickS]

Pat,

Can you help me understand why it is helpful to avoid spaces and special characters in nameing columns?

I am working now with some databases that were poorly constructed and that is part of why I have to rework a lot of them (several databases with overlapping information, tables that are too large and should be spilt into multiple tables, etc.)

I want to be sure that I do not create new problems that someone will have to come behind me and fix, so, thanks for the input.

NickS

 

[Pat Hartman]

The improper names will cause problems in VBA and occassionally in queries. For example, if you just referred to "Date" in a query of a table that conatined a field named "Date", you'd get the current date, NOT the date in your table field so you have to becareful to always enclose all your improper names in square barckets. [Date] will reference your table field in the query but Date references the current date function. An easy mistake to make if you're not paying close attention.

Splitting a table because it is "too large" isn't a good reason. Tables that are too "wide" are typically spreadsheets mascarading as tables. They need to be normalized to remove the repeating groups and duplicate columns.