?
Solved

Modifying a Record

Posted on 2011-10-27
14
Medium Priority
?
270 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Frank Freese
  • 8
  • 3
  • 3
14 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37040955
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.

;-)

JeffCoachman

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37041130
Finally:
<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 , ...one day, ...someone will ask :
"who was the manager of XXX between May and Sept"
Or
"How many Managers has store XXX  gone through in the last 5 years...

;-)

JeffCoachman
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 37041173
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.
StoreBonusRevX.mdb
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:Frank Freese
ID: 37041177
thank you
0
 

Author Comment

by:Frank Freese
ID: 37041285
jeff:
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?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37041335
I capricorn1 has already created a sample, then he is ahead of me.

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

;-)

Jeff
0
 

Author Comment

by:Frank Freese
ID: 37041362
understood - thank you
cap: what do you think?
0
 

Author Comment

by:Frank Freese
ID: 37041559
capricorn:
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?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37041569
did the sample db worked?
0
 

Author Comment

by:Frank Freese
ID: 37041645
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37041719
clean first your table tblDesignatedEmployee, get rid of duplicates values.
0
 

Author Comment

by:Frank Freese
ID: 37044234
ok...
0
 

Author Comment

by:Frank Freese
ID: 37044286
it test just perfect - I truly appreciate you - thanks for going through as the "Expert" you are!
0
 

Author Closing Comment

by:Frank Freese
ID: 37044287
just perfect....
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question