Solved Help with changing fields based on other fields (1 Viewer)

dawsonrhodes

Member
Local time
Yesterday, 22:25
Joined
Mar 8, 2020
Messages
82
Hey All,

I've created a new project for my work, it's essentially a computer assisted dispatch, storing call records for Security Operations. Essentially with each "call" (record) there is a total of 6 fields to assign "staff" to. What I'd like to do is make it so that only staff with the status of "10-8" (available) appear in that list. No problem, got that in the bag. Where things get a little over my head is that what I'd like to do is that when you select a "staff member" from the available (10-8 status) list in 1stAssigned field, it will change the EmployeeStatus field to "10-6" (busy).

This is all done on an Access Form, with Querries to populate the list, and record the records into. The records get recorded into qryDetailsExtended, the list of staff is pulled from qryEmployeesExtended, which is where the status of each employee is also stored. The name from the list populated by qryEmployeeExtended is the only thing that gets stored in qryDetailsExtended, under 1stAssigned, 2ndAssigned, etc.

Another field on this form is chkDetailCompleted, which signifies that the call is completed, which I use to filter other datasheets, etc. When this is checked and the record is closed, I'd like the names in 1stAssigned (populated from Employee Name in qryEmployeesExtended) to change EmployeeStatus to 10-8 as well, signifying they are cleared off the call and back available.

I've attached a screenshot with the basics of the form and query I am talking about.

Thanks in advance!
1620563328195.png

Form Name frmManageDetails
1620563372571.png

Query name qryEmployeesExtended
 

Gasman

Enthusiastic Amateur
Local time
Today, 03:25
Joined
Sep 21, 2011
Messages
8,493
You generally use the AfterUpdate event of a control to set other controls to whatever you want.?
 

dawsonrhodes

Member
Local time
Yesterday, 22:25
Joined
Mar 8, 2020
Messages
82
If it makes i
You generally use the AfterUpdate event of a control to set other controls to whatever you want.?
Hey Gasman,

I guess that would be what I'd need, however what would I write in vba if the record and field I'd like to update is in another table?

Also, how would I be sure that if I select John DOE under query field "Employee Name", that only that person's status goes to 10-7/10-8?
 

Gasman

Enthusiastic Amateur
Local time
Today, 03:25
Joined
Sep 21, 2011
Messages
8,493
You would use an Update query with the correct record using criteria if you had to update a table.
However I would expect you to do this via forms?, hence I mentioned controls.?
 

dawsonrhodes

Member
Local time
Yesterday, 22:25
Joined
Mar 8, 2020
Messages
82
He
You would use an Update query with the correct record using criteria if you had to update a table.
However I would expect you to do this via forms?, hence I mentioned controls.?
Hey Gasman, this would be where I am a touch out of my league, any chance if I attach a copy you could have a peak?
 

Gasman

Enthusiastic Amateur
Local time
Today, 03:25
Joined
Sep 21, 2011
Messages
8,493
I'll have a look, but I am just a reasonably experienced amateur.

However if you post it (with relevant details of what you are trying to achieve, as the initial post is just generic?), then I'll see what I can advise.?

It also allows better qualified members to take a look and perhaps offer better advice.?

I would not be writing the code however, that is for you to do, but will try and offer useful advice.

My first concern is you appear to want to update fields/controls, but a user would not get an indication as to it happening.?

EG, I cannot see Employee status on that form?

If I was using a system, I'd like to see the effect of what I am changing.?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:25
Joined
Feb 19, 2002
Messages
32,225
You might want to do some reading on normalization. The assignments need to be in a separate table, one per row rather than as multiple columns in a single row. This will also help with your other problems.
 
Last edited:

bastanu

AWF VIP
Local time
Yesterday, 19:25
Joined
Apr 13, 2010
Messages
820
In addition to Pat's suggestion (which would allow you to have 7 or 8 staff for a "special" call without having to change your structure), I would say that you don't need to store the employee status in a table as it can be easily calculated in a query as all employees without any assigned incomplete (chkDetailCompleted=False) calls are available, the rest are busy. That calculation will be instantaneous and would eliminate the need to "reset" the statuses.
Cheers,
 

Users who are viewing this thread

Top Bottom