How to make data input to one table automatically appear in another ?

[SteveJM]

Hi there ,

I'm a newbie to Access database. I'm using Access 2010. I'm trying to build a staff database and I'm now on the locker section. I have a locker table and I have a staff table. I have a primary key called staff ID number which is an auto number. When I place a member of staff's ID number next to a locker number in the locker table, I would like the locker number to automatically show on the staff table, what I would also like to be able to do is when I remove the staff ID number from the locker table, I would like the locker number to disappear from the staff table.
Any help will be much appreciated.

Regards,
Steve

 

[Uncle Gizmo]

Don't put the locker ID in the staff table, have a separate table, tblAllocatedLocker with two fields, StaffID, LockerID.

 

[SteveJM]

Don't put the locker ID in the staff table, have a separate table, tblAllocatedLocker with two fields, StaffID, LockerID.

_______________________________________________________________

Thanks for your answer Uncle Gizmo. I have a staff record form that I have for individual staff members and i wanted the locker number to appear there so all information for each staff member is on one page. If I can have the staff table automatically add & remove the locker number to the staff table, then the staff record form will also update.

Any ideas ?

 

[Uncle Gizmo]

Having an extra table is the correct way. You need to choose how you want to display it on your staff members details form. I think I'd try a subform.

 

[SteveJM]

Having an extra table is the correct way. You need to choose how you want to display it on your staff members details form. I think I'd try a subform.

--------------------

Many thanks Uncle Gizmo. I have tried the subform, I have locked off the subform so that data can not be changed via subform and only in the LockerTable. Is it possible to remove the slider bars and record shifters from the subform so there is just a window for data and title ?

Many thanks, Steve

 

[Galaxiom]

Is it possible to remove the slider bars and record shifters from the subform so there is just a window for data and title ?

Take a close look at the properties of the subformcontrol and the form displayed in it. If you think about their names they will make sense.

By changing these settings it is even possible to make the subform look like all its controls are part of the main form.

 

[SteveJM]

Thanks for your input everyone. I've managed to obtain the correct function that i want but can't get rid of those pesky scroll bars. I've changed various things in properties but just can't seem to remove the scroll bars.
I've attached a screen shot of the field and properties list.

Any more ideas please ?

 

[Minty]

If you set them to neither they won't show on the form, but will in design view, otherwise you couldn't move around it to do anything.

 

[gemma-the-husky]

different approach

if a staff member can only have a single locker, then you can just put the locker number in the staff table

however, if a staff member can have multiple lockers, or you need to track locker history then you need the locker assignments in a separate table. The right approach in that case is to manage them as others have indicated - you shouldn't start deleting and moving lockers from table to table.

in terms of seeing the data, no-one should look at tables directly. you present the data via forms or queries, and you can bring data together from multiple tables - that's how you get the lockers to be displayed with the staff details.