Help passing right information to Table

[jserrone]

Hello Everyone,

I have created an access database with a simple login screen. Once the user is authenticated the database brings the user to the Advice Form. I have a code On Load built for the Advice Form to have the Employee name populated automatically. However, when I complete this form and save it, the database saves the record in the advice table with the Employee ID (Autonumber) instead of the Employee Name. Can someone look at the code and tell me what I'm doing wrong. I would appreciate any help!
For testing I created a user: joe password 123

Attached is a sample of my database

 

[boblarson]

Actually, if it saves it with the ID and not the name then it is doing its job correctly. If you do it the other way you are violating normalization rules as the employee name (text) should ONLY be in ONE place. The ID number is what ties everything together in a normalized database.

 

[jserrone]

Hi Bob,

Thanks! but I would like to save the employee name under the Advice Table as the Name of the employee. I have a problem getting qurries and reports to be generated with the Autonumber created by Access.

 

[boblarson]

Actually, you probably just need a little tutorial on how to use queries as those are the key to recombining the data in the way you want to see it.

To view the data in reports you would just have a query that has both the Advice table and the Employees table selected and then a link set between both Employee ID fields (just drag and drop from one to the other) and then you are set. You can then select all of the fields you want from your Advice table and only the Employee name from the Employee table and then the query will have the same info as if you stored the employee name in the Advice table, but while keeping it normalized.

 

[boblarson]

Just another FYI for you as well:

ONE of the reasons why you want to do it this way is that, let's say that an employee's name was spelled wrong. To fix it you could have to touch many records. But, if the ID is stored, it has to ONLY be fixed in the Employee table and then automatically all of your forms and reports will then have the correct value showing.

 

[jserrone]

Makes sense! I actually figured out in the query. I had the Name stored as a Text and connected to the ID as a number and was giving me a Mismatch message. I changed the name to number and is working great now!

Thanks