Ability to Add Records

[jeffcampbell]

I'm an Access newbie using Access 2003, and am a bit confused about how to create a form/subform (when I have two tables linked by a junction table) which allows the creation of new records in either of the two main tables. Here's the scenario: First table, tblEmployees, with three fields, EmployeeID (primary key, number, indexed, No Duplicates), Fname, LName. Second table, tblAwards, with three fields, AwardID (primary key, number, indexed, No Duplicates), Description, Source. Junction table, tblJunction, with two fields, EmployeeID and AwardID (with both fields jointly acting as the primary key,number, indexed, No Duplicates). Obviously, I've simplified the database for purposes of illustration, and for experimentation. The relationship between Employee and Awards is that all Employees can be a recipient of any award. I'd like to have my end user be able to have a form that allows them to add/create/delete any employee, and also be able to add/create/delete any awards and, be able to associate an existing award to an employee. I've tried a combobox with awards, and that does give ability to select an existing award, but not the ability to create a new award from the form.
Thanks for your help!!!
Jeff

 

[rainman89]

Do you want them to be able to create a new award/employee directly in the main form?

Another option would be to use the combo box for both employee and award, but have a button or something to create a new award/employee right next to the combo. Be sure to requery your form after you create a new employee or award though.

 

[jeffcampbell]

Hi Rainman,
Yes, that's my hope, the ability to have a single form where the end user can move through records in the employee table, see the associated awards for the employee, add employees as necessary, and add new awards which can then be associated with any of the employees.
Thanks!!

 

[rainman89]

Alright then I think you will want to look into the "not in list" function of a combo box

Here is a quick link
http://www.databasedev.co.uk/not_in_list.html

 

[jeffcampbell]

Hi Rainman,
I've been experimenting with the NotinList and the VBA, and I think I'm getting closer. The last issue I'm having is that although I've been able to get the NotinList & VBA to create a new record in tblAwards, the record doesn't seem to be linked to tblJunction and since there isn't that link, the new award isn't being linked to tblEmployees. I'm thinking I need to somehow somehow get the EmployeeID into the mix. For example, when I'm in tblAwards, I can create a new AwardID, and with the "plus" enter an EmployeeID associated with the AwardID. Should the VBA be opening both the tblEmployees and tblAwards, and entering the respective information?
Thanks for all the help, I really, really appreciate it!
Jeff

 

[rainman89]

Jeff,

Apologizes for the untimely response. Holiday weekend and all....

Take a look at the attached sample...

I think what you were missing was the fact that you needed to either set the control source of the form to a query based off of your junction table, or you needed some vba to add the records manually.

This sample should show you what I am talking about.

Hope it helps

 

[jeffcampbell]

Hi Rainman,
Thank you again for all your help, and for actually making something I could look at and try!! I never expected that someone go to that extent to help, you went above and beyond, and I really appreciate it. After experimenting, and looking at your sample, I figured out that I could set the two foreign keys in the junction table to be comboboxes with lookup to the respective tables. Then using the junctiontable combobox (AwardID) on the form rather than the tblAward (AwardID) I could drop down and select any award for any employee. I then added a combobox to the tblEmployees which referenced the tblAwards.AwardID, and used the NotInList functionality that you explained earlier to allow the end user to use the drop down, see that last award that was given out, and be able to enter a new number for an award and subsequently add it to both comboboxes. (did have to do a Me.Refresh on the tblJunction AwardID to get the added awards to show up consistantly). Anyway, thank you again!!!
Jeffrey

 

[HiTechCoach]

I'm an Access newbie using Access 2003, and am a bit confused about how to create a form/subform (when I have two tables linked by a junction table) which allows the creation of new records in either of the two main tables. Here's the scenario: First table, tblEmployees, with three fields, EmployeeID (primary key, number, indexed, No Duplicates), Fname, LName. Second table, tblAwards, with three fields, AwardID (primary key, number, indexed, No Duplicates), Description, Source. Junction table, tblJunction, with two fields, EmployeeID and AwardID (with both fields jointly acting as the primary key,number, indexed, No Duplicates). Obviously, I've simplified the database for purposes of illustration, and for experimentation. The relationship between Employee and Awards is that all Employees can be a recipient of any award. I'd like to have my end user be able to have a form that allows them to add/create/delete any employee, and also be able to add/create/delete any awards and, be able to associate an existing award to an employee. I've tried a combobox with awards, and that does give ability to select an existing award, but not the ability to create a new award from the form.
Thanks for your help!!!
Jeff

That would be correct. Access is working as designed.

If you want o add an Award, you have to add some way to do it.

Some Options:

1) add an command button for Add Awards.

2) use the Not In List event of the combo box to add an award.

 

[ChrisLayfield]

Rainmain...I tried your db. I typed in a person who was not on the employee list and the box appeared asking to add the employee so I selected yes. The problem was the employee was added to the combobox, but does not appear on the tblEmployee. Any idea why?

 

[rainman89]

Hi Chris,

The combo box is based directly off of the table so if it was in the combo it must be in the table.

Did you happen to have the table open when you created the person in the comobo box?

 

[ChrisLayfield]

must have had it open...once again I have shown what a noob am I. Thanks. Everytime I get some sample db to look at I can learn how to make on. Thanks for the sample.