Populating Part of a Form

[Sander]

I am trying to put together a database for tuition refund. My current database has 2 tables. The first table called Employee contains employee info such as name, location, department, and badge. The badge field is the primary key. The second table called 2001 is linked to the Employee table by the badge field. This table (2001) contains info such as course, institution, degree type, grade, amount of course, etc. (I set the database up this way since each employee can take multiple courses in a year.) Every time I go to design a form I've been basing it off a query including all the information from both tables. When a user wants to look at an employee's information they select the badge from a combo box on a form I've created and when they hit a command button a query finds all the records that match the badge.

Now my question has to do with adding a new record. Is there a way that a user could select the badge field from a combo box and when they hit a command button a new record would appear where all the fields from the Employee table would automatically be filled in but the fields from the 2001 table would be left blank for them to enter?

Thank You in Advance for any help. I've got myself stumped!

 

[charityg]

What it sounds like you need is a form, subform situation. On the main form, you would display the employee information. The subform would display all class information (listing multiple records). It would also take the guesswork out of viewing the employee information for new records in the subform. The information would always be visible at the top of the form until you picked a different badge, in which case it would change.

 

[Pat Hartman]

I suspect there is another problem with the table structure that you should correct sooner rather than later. If it is your intention to create a new table each year, you are making much more work for yourself than you need to. It would be better to just include the year as a column in the table. That way you will not need to create a new table next year which would require changes to all your forms and queries. Having data from multiple years in the same table will allow you to easily do multi-year analysis reports.