Form Help

[johnpaul]

Hi,



I am having problems with crating a form.

I have a DB with 5 tables - EmpInfo, Discipline, Other Achievements, Performance, Storeinfo

I have created a from to allow me to enter employees into the DB into table EmpInfo.

Now that I ahve all my employees in the DB with their details I want to crate a form so I can enter the discipline into the DB.

How do I create a form that will look up the employee name from Table EmpInfo and allow me to enter info in the dtails part of the discipline table.

I have used the wizzard to get the 3 parts of the disicpline sheet I wish to enter info in but I have to link it to a employee at the moment both table are using my employee numbers as PK's

please can anyone help

[RuralGuy]

...at the moment both table are using my employee numbers as PK's

This sounds like a design issue at the moment. PK's are used in other tables as ForeignKeys (FK's).

[johnpaul]

This sounds like a design issue at the moment. PK's are used in other tables as ForeignKeys (FK's).

Can you tell me how to set FK's?

[RuralGuy]

There is nothing very special about FK's. Assuming an AutoNumber is a PK in a table then that same value in another table field that is a LongInteger type will make it a ForeignKey. The secret is defining the Relationships of the two tables. Using the method I just described, the Relationship wizard can determine and set up the relationship for you.

[johnpaul]

There is nothing very special about FK's. Assuming an AutoNumber is a PK in a table then that same value in another table field that is a LongInteger type will make it a ForeignKey. The secret is defining the Relationships of the two tables. Using the method I just described, the Relationship wizard can determine and set up the relationship for you.

Unfortunatly my employee numbers are not Autonumber as these are pre-determined by someone else, so I have them in as Number (Long Integer) on my empinfo table.

In my discipline table I have Employee number (Number, Long Integer),
Date, Details, Issued By.

Would I need to create another input in dicsipline and have it has a Autonumber to work?

[RuralGuy]

Would I need to create another input in dicsipline and have it has a Autonumber to work?

The simple answer is no. Also a rule of thumb is: If you will not be performing math on the number than it should probably be a Text field. In this case I think I would leave it as a LongInteger because Keys work better when they are numbers since they need to be indexed.

[johnpaul]

The simple answer is no. Also a rule of thumb is: If you will not be performing math on the number than it should probably be a Text field. In this case I think I would leave it as a LongInteger because Keys work better when they are numbers since they need to be indexed.

I think I understand, I have have somehow managed to get a form working how I want.

What I have done is change the employee number in the discipline from PK and made the date field in the discipline table the PK.
now in relationships I have linked the emp no from empInfo table with the emp no in discipline table it now seams to work.

[RuralGuy]

A good style in design is to name fields with the same value the same name. It makes it easier to understand. It is also a good idea to start forcing yourself to stop using embedded spaces in a name. I use CamelFontNames myself but Under_Score_Names is also very acceptable. I see no reason to use your date field in the disipline table as a primary key. What is wrong with using an AutoNumber field for that? Are you trying to save disk space?

[johnpaul]

A good style in design is to name fields with the same value the same name. It makes it easier to understand. It is also a good idea to start forcing yourself to stop using embedded spaces in a name. I use CamelFontNames myself but Under_Score_Names is also very acceptable. I see no reason to use your date field in the disipline table as a primary key. What is wrong with using an AutoNumber field for that? Are you trying to save disk space?

I dont have any Autonumber fields in my discipline table, only EmpNumber - Date-Details-LogedBy

[RuralGuy]

I may have lost track of what is going on. Are you not allowed to modify the table structure in this system?

[johnpaul]

I may have lost track of what is going on. Are you not allowed to modify the table structure in this system?

Yes I can Modify

I am currently creating the DB it's the first I have ever done so Kinda going in blind to a degre

[RuralGuy]

As you have probably already noticed, Access wants to create an AutoNumber field as the PrimaryKey field of all of the tables it creates. This is not a bad thing. Often these PrimaryKeys come in handy later on in the design. It simply allows you to absolute3ly identify a particular record in the table. Using an existing field (such as your date field example) is called a "natural" key and can also work. A date field is a special "double floating point" field and gets handled differently than a simple LongInteger field like an AutoNumber. I'm really anal and dogmatic with this stuff, sorry.

[johnpaul]

As you have probably already noticed, Access wants to create an AutoNumber field as the PrimaryKey field of all of the tables it creates. This is not a bad thing. Often these PrimaryKeys come in handy later on in the design. It simply allows you to absolute3ly identify a particular record in the table. Using an existing field (such as your date field example) is called a "natural" key and can also work. A date field is a special "double floating point" field and gets handled differently than a simple LongInteger field like an AutoNumber. I'm really anal and dogmatic with this stuff, sorry.

OK, So would it be a good idea to create a field on every tabe called ref and have this as a AutoNumber?

or should I just leave my Date field as the PK?

[RuralGuy]

The AutoNumber field in each table should have a unique name appropriate for that table. I would create a new field in the Discipline table called maybe DisciplineID and make it an AutoNumber field. Of course that is just me.

[johnpaul]

The AutoNumber field in each table should have a unique name appropriate for that table. I would create a new field in the Discipline table called maybe DisciplineID and make it an AutoNumber field. Of course that is just me.

Thank you for the help