ConfusedA's Question of the day...updating 2 fields on 2 tables from 1 text box

[ConfusedA]

Using: Access 2k3

My problem:
I have a table 'tblemployeeinfo' which contains, among other things a field for employee name and a field for wage.
I had initially used this table to populate many list boxes for inputting employee information. When I decided to tackle database security I noticed that the list boxes became blank. This is problematic because the basic users need access to the employee name section, but I want to keep the wage information confidential and only available at the Admin level.

My solution:
Making the initial text on the employee input form populate a new table (tblemployeename) that can then be used to populate all of the list boxes without affecting the table with wage information.

My question: Is it possible to give an object 2 control sources to do this? If yes, how. If not, what would a better solution to this problem be?

Thanks in advance.
ConfusedA

 

[ConfusedA]

I've considered using an append query that is activated through a command button for updating the current record, my only concern would be that if the user does not click the button the information still enters into the main table but not the second table. And I'm also unsure if the append would work because i'm unsure if the record is updated on a change from the record or just on the entering of the data. If the record is updated on the entering of data then my solution may work. Thoughts?

 

[Kryst51]

I understand your need to keep the wages secure. I have a couple of suggestions/questions.

1. Is the wage showing up in your list box. Is that what is not acceptable to you? If this is the case, the you should be able to stop that list box from showing the wage.

2. Can you have a table that has three fields. WageID, Wage, EmployeeID? Employee ID, would be populated either through a table lookup (Which I know is not a good idea) or through a form that only the person who has the right to see that sensitive information can view/use. On this particular form is where you can have the lookup instead of the at the table level.

3. If the question is prompted by a need to keep users from getting to the table to see the information, I suggest reading up on Access Security. Which there is an overabundance of information on, not only on this website, but could be found easily on the web. (I myself know nothing about it.) Ultimately you want your users to only see forms and reports. Nothing else.

Please feel free to ask more questions.

 

[ConfusedA]

Thanks again for your comments Kryst51.
I'll attempt to address your points in order.
1. Only the employee name should show up in the listbox, but because I have set a security level on the table that the employee name (and wage) are located on the list box appears to be empty when a user without admin abilities accesses the form.
2. There is a form in which admin places the employee data (name, wage, avg. weekly hours, etc.) in to (that's what populates the table) it's only taking name out of the table while the table is secured that I am having a problem with.
3. I do agree about the forms and reports thing, just for some reason by making my tables unavailable I'm making my report boxes unable to populate. I'm only at the beginning stages of trying to figure out this security thing, so I have many questions regarding that (it's pretty much caused a small storm throughout my program)

Thanks for your thoughts and time.
A