Modifying a Record

I have attached a db that opens to a form named frmModifiedAssignedEmployees. This form is based upon the table tblDesignatedEmployee and is structured as follows:
Designated EmployeeID (PK)
StoreID (FK)
EmployeeID (FK)
EmployeeTypeID (FK)

The purpose for this form is to allow employees to change assigned store employees. For example, Store X has been assigned a new District Manager and that record needs to be modified.

The first thing the user does is to select a store from a combo box called StoreID. Below this combo box are four unbounded and not enabled text boxes that receive information from the combo box that is informational only.

To the right of those text boxes are 4 toggles, each associated with an employee type. Currently I am only working on the first toggle District Manager. When the District Manager is toggled then the associated combo box is enabled and the user can then select from a list of employees to replace the current District Manager for that store. Thus the record is MODIFIED. I do not want to createa new record.

The attached db opens to the form I am referring to. StoreBonusRev.mdb
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
I know you don't want to hear this, but your form design here is what make this confusing.

Your first Manager combobox is pulling the info from the Store Combo.
This means that "Changing" the Manager (from the second Manager combo) will have to involve updating the values in the Store combobox recordsource.
Unfortunately, your source for the store combo contains calculated fields based on subqueries...?

Finally you would have to:
1. Update this value
2. Save the Value
3. Re-query the combobox (or possibly the entire form.)
4. Return to the saver record.

So while this is certainly possible it may take an expert a while to decipher...

I'll post the way I would approach this in a while...

In the mean time perhaps another Expert will be along, perhaps I am not understanding something.



Jeffrey CoachmanMIS LiasonCommented:
<I do not want to createa new record.>

If I am understanding correctly, this would mean that you will not have any "History" of who managed what store previously...
I know a lot of people will say:
 "I don't need History, I just typed the wrong value, and I simply want to change it"

Great, if you could set a "Time Limit" for the edits, or use some other system to differentiate between "Mistakes" and entries that are "etched in stone"

If a person is assigned for more that a day or so, I would want to have a history, ...if only for legal reasons...
(You are missing money/inventory, ...etc, from a store, and the lawyer asks "Who was the manager for those two days?")

You may not need it now, but , day, ...someone will ask :
"who was the manager of XXX between May and Sept"
"How many Managers has store XXX  gone through in the last 5 years...


Rey Obrero (Capricorn1)Commented:
test this revision

just modify the district manager,
you can modify the other just follow the codes in the afterupdate event of cboDistrictmanager

the combo boxes must be unbound.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Frank FreeseAuthor Commented:
thank you
Frank FreeseAuthor Commented:
you bring up a good point. maybe I should simply write the old record into a history file, delete the current record and create a new one for a store?
cap and jeff:
can we make something happen like appending to a history file, write the new record then delete the old?
Jeffrey CoachmanMIS LiasonCommented:
I capricorn1 has already created a sample, then he is ahead of me.

I'll let you continue on with him to avoid confusion...


Frank FreeseAuthor Commented:
understood - thank you
cap: what do you think?
Frank FreeseAuthor Commented:
my thoughts would be to
append my old record to a history table
write new record
delete old record.
I'd want to open follow up questions for each specific instance that needs help.
fair enough?
Rey Obrero (Capricorn1)Commented:
did the sample db worked?
Frank FreeseAuthor Commented:
no...had a some of problems with it. I do recall that when I tried to save a record I got an error that claimed duplicate record exists? then my store combo box went blank. i have a tremendous amount of confiedence in you and thanks always for your help.
Rey Obrero (Capricorn1)Commented:
clean first your table tblDesignatedEmployee, get rid of duplicates values.
Frank FreeseAuthor Commented:
Frank FreeseAuthor Commented:
it test just perfect - I truly appreciate you - thanks for going through as the "Expert" you are!
Frank FreeseAuthor Commented:
just perfect....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.