Creating User forms - beginner needs help

[twarden]

I'm trying to create a user form (I know this is basic - but i'm a beginner!) and I'm having problems. I've created 3 entities, we'll call them employee, accident & Claim. Each table has some sub-tables feeding in to them - for instance

In the Employee table there is a field that asks for the employee's department. However, in this entity it is stored as a department number. There is then a sub-table that correlates the department number to a department name.

What I want to do is create a user form that will allow me to input information that feeds both into the main entity (so for Instance I would enter employee number - because that is a field on the main entity), but I would also enter the actual department name (as opposed to the department number) which is stored in a sub table that feeds into the main entity. Every time I try this I get an error - I'm sure there's a simple fix. I've also tried a simple combo box to solve this - but that's not working either.

Any suggestions?

 

[Andrew Fergus]

Hi

When using combo boxes like this you need to set them up a certain way.

Create a new combo box using the wizard, and pick the following options :

1) look up the values in the department table
2) make sure both the department number and name are 'selected fields'
3) sort it by the department name
4) Don't tick 'hide the key column' but instead set the column width to zero (by clicking and dragging the column indicator in the wizard)
5) When the wizard asks which field you want to store, choose the department number
6) When the wizard asks which field you want to store it in, choose the department number fields from the employee table (assuming the form is using the employee table as its source)

Now when you open the combo box it will show the department names, but behind the scenes it will be working with the department number.

HTH, Andrew

 

[Tanis]

You don't need sub tables as you call them . All tables are just tables. Create a department table. Your department table should consist of a primary key, call it DepartmentID and your Department names. If the department name is unique, you could use that as your primary key. Create a one to many relationship between the primary key in the department table and the department field in the main table. Create your combo box using the department table, tblDepartment, use the wizard for this, it will walk you through the stages.

 

[VicRauch]

If the department name is unique, you could use that as your primary key.

HIGHLY reccommend you NOT use department name as you primary key! DepartmentID and DepartmentName would be the two fields within the tblDepartment table in a relational database. The reason? Department names change! As long as the Employee Relations department stays Employee Relations, no problem. But as soon as it changes to Human Resouces, BIG problem if you have used the "unique" department name as the key.
Normalization Rules

 

[Andrew Fergus]

Hi Vic

I don't have an issue with using things like department names as the primary key for two reasons : you can force changes in the primary key into the table on the many side of the relationship by selecting the 'cascade update related fields' option in the table relationship screen and secondly, it makes it easier to push items from the combo box that are not in the list into the underlying table, if necessary. I accept there may be normalisation issues but sometimes its just easier when there is only one meaningful field in a table.

Andrew

 

[VicRauch]

Andrew,

I have just found it soooooo much easier throughout the development and then maintenance of a project to have that extreamly handy "ID" field for all these names. A long, long time ago (in a galaxy far, far away) I had a couple of fights with the data because I did not use the ID field, and ever since I've been very happy with using the ID and name fields (and they lived happily ever after).

 

[Andrew Fergus]

No worries. Have you tried using 'cascade update'? It is a pretty neat feature for these sorts of situations.

Regards
Andrew

 

[VicRauch]

Cascade Update? That was part of the stuff I did in a galazy far, far away. Part of what I really was not comfortable with. I just prefer the ID and name field. For me it takes out any additional steps that I hope I remembered to ask for cascading update/delete in the relationships, etc. With the ID and Name in the table, I know that when a name is changed, the "update" has already be cascaded throughout the database, without me having to remember "Did I make sure I selected cascading update/delete on that relationship?"