How do I get a combobox to pull from multi tables?

[Dechienne]

Building a form to track issues inside a callcenter. I'd like to get the
form as efficient as possible so I'd like to be able to enter in a problem
into one field, then on the following field have only the corresponding
sub-problems listed.

I've attempted to use macro's to run several different actions on a field.
I can get those to work, but I keep getting a null error, but still get the
correct data.

I was advised to make each situation/reason combination into it's own
table - which I did, and now I have 6 tables I need 1 field to be able to
look at.

I need to know if/how I can get this field to look up information from
multiple different tables based off the information inputed by the user, and
the input data from that table into another combo box on the same form. The
tables are all labled the same as what will be inputed into the field.

Little more details: Some of the information that'll be inputed into the
Situation field is like: Idle: , Sign-off:, Inbound Call: The data that
is in the Reason field (The one I'm attempting to auto populate based off
the situation field) will have data like: Personal, lead-line, meeting,
research, equipment...etc The tables are named the same thing as the
situations themselves.

Hope that made sense...been driving me nuts trying to finish this thing

 

[Steve Schapel]

Dechienne,

In my opinion, you only need one table, with 2 fields, Situation and
Reason, and enter data into this table to provide every possible
combination of Situation/Reason. Set the Row Source of the first
combobox to a query, the SQL view of which will look something like this...
SELECT DISTINCT Situation FROM YourTable
Set the Row Source of the second combobox to a query, the SQL view of
which will look something like this...
SELECT Reason FROM YourTable WHERE Situation =
[Forms]![YourForm]![Situation]
On the After Update event of the Situation combobox, put code like this...
Me.Reason.Requery

 

[Wolfgang Kais]

Hello Dechienne.

Building a form to track issues inside a callcenter. I'd like to get
the form as efficient as possible so I'd like to be able to enter in a
problem into one field, then on the following field have only the
corresponding sub-problems listed.

You could use the enter event of the second ConboBox fill it's list.

I've attempted to use macro's to run several different actions on a
field. I can get those to work, but I keep getting a null error, but
still get the correct data.

You could have told us mor about the "null error". What happens when?

I was advised to make each situation/reason combination into it's
own table - which I did, and now I have 6 tables I need 1 field to be
able to look at.

So what kind of advisor was that?

I need to know if/how I can get this field to look up information
from multiple different tables based off the information inputed by
the user, and the input data from that table into another combo box
on the same form. The tables are all labled the same as what will
be inputed into the field.
[...]

Here's my advice: You should have two tables: Situations and Resaons.
Situations: SituationID (AutoNumber, PK), Situation (Text)
Reasons: ReasonID (AutoNumber, PK), SituationID, Reason (Text).
Create a relationsship between the two using their SituationID fields.
Build the first ComboBox (cboSituation) from the Situations table and
let it display the Situation field and store the SituationID.
The second combo's (cboReason, 2 columns, bound column 1,column
widths: 0) rowsource could be specified in the enter event:

Private Sub cboReason_Enter()
cboReason.RowSource = _
"Select ReasonID, Reason From Reasons Where " & _
BuildCriteria("SituationID", dbLong, Nz(cboSituation, "Null"))
End Sub

In the AfterUpdate event of cboSituation, you could delete the value
of the second combo:
Private Sub cboSituation_AfterUpdate()
cboReason = Null
End Sub

 

[Steve Schapel]

Dechienne,

I should have specified, that you would need to replace the YourTable
and YourFOrm in my example with the actual names of the table and form
concerned, and also I have assumed the comboboxes are simply named
Situation and Reason, so adjust as necessary.

 

[Dechienne]

Thanks Steve - works like a charm now!

One more thing, is there a way to have the form prompt you when more then on
option is located? For example - if we input something for situation and
there are multiple Reasons, is there a way to be prompted of that so no
errors occur?

Dechienne,

I should have specified, that you would need to replace the YourTable
and YourFOrm in my example with the actual names of the table and form
concerned, and also I have assumed the comboboxes are simply named
Situation and Reason, so adjust as necessary.

--
Steve Schapel, Microsoft Access MVP

Dechienne,

In my opinion, you only need one table, with 2 fields, Situation and
Reason, and enter data into this table to provide every possible
combination of Situation/Reason. Set the Row Source of the first
combobox to a query, the SQL view of which will look something like this...
SELECT DISTINCT Situation FROM YourTable
Set the Row Source of the second combobox to a query, the SQL view of
which will look something like this...
SELECT Reason FROM YourTable WHERE Situation =
[Forms]![YourForm]![Situation]
On the After Update event of the Situation combobox, put code like this...
Me.Reason.Requery

 

[Steve Schapel]

Dechienne,

I am sorry, I am not sure what you mean. I would assume there would be
the choice between a number of possible Reasons for any given Situation.
After you select the Situation, the Reason combobox should only list
those Reasons associated with the selected Situation, and you would be
required to select the applicable Reason from the Reason combobox. Is
that right? So, what do you want to happen? Do you want the Reasons
list for the selected Situation to be displayed as soon as the Situation
is selected? If so, your code on the After Update of the Situation
combobox would be like this...
With Me.Reason
.Requery
.SetFocus
.DropDown
End With

 

[Dechienne]

Thanks Steve - That works perfectly

One more thing if you could please.

When we pull information, there are times that there are more then one
possible pieces of data that could be popluated.

Is there a way to get the system to notify you when there are more then one
possible piece of data?

Sorry, trying to think of how to explain this. What I'm looking to fix is a
problem where there is mis information being entered. We have agents that
use the same extension in multi call centers, so we're getting times where
the program is pulling the agent information that's listed first (setvalue
to 0 to auto fill), but it isn't always correct. Is there a way to get the
system to notify us when one of these situation occur?

Table information:

Combo boxes:
Agent
Name
Manager
Department

Have it set to where when you put in the agent number - it runs a query off
a main table with all the employee information - then auto fills the Name,
Manager, and Department fields.

Well, when we have the double agent problems, it'll give us the first
listing, which isn't always correct - and it's causing errors, and I get to
go through and delete a few thousand enteries a week because of this. Any
thoughts?

Dechienne,

I should have specified, that you would need to replace the YourTable
and YourFOrm in my example with the actual names of the table and form
concerned, and also I have assumed the comboboxes are simply named
Situation and Reason, so adjust as necessary.

--
Steve Schapel, Microsoft Access MVP

Dechienne,

In my opinion, you only need one table, with 2 fields, Situation and
Reason, and enter data into this table to provide every possible
combination of Situation/Reason. Set the Row Source of the first
combobox to a query, the SQL view of which will look something like this...
SELECT DISTINCT Situation FROM YourTable
Set the Row Source of the second combobox to a query, the SQL view of
which will look something like this...
SELECT Reason FROM YourTable WHERE Situation =
[Forms]![YourForm]![Situation]
On the After Update event of the Situation combobox, put code like this...
Me.Reason.Requery

 

[Steve Schapel]

Dechienne,

Do I understand correctly here... the basic problem is that sometimes an
item in a combobox's list can refer to more than one individual? If so,
you need to make the Row Source of the combobox to be a query which is
multi-column so that the fields that distinguish one from another are
shown, which allows the user to select the right one.

Maybe it would help if you could describe more details about the data
entry that is happening, and give some specific examples to illustrate
where the problem arises.

 

[Dechienne]

Yeah, no problems

Currently the problem is multiple agents have the same agent number. The
query I have setup pulls off a master employee table that has every
agent/name/manager/department combination that is in the system, between
both call centers. The problem came from the Aspect system being seperate,
so it generates numbers for each call center without checking with the other
one first to ensure that number is not used. So we get people in one
building that have the same number as someone in another building across the
state

The main problem is if we put in one agent number, and there is more then
one person with that number, the form will auto pull the first record (macro
to setvalue itemdata(0))

Here's some example data, then more explaination

Example data:

Agent number: 1111
Agent: John Smith
Manager: Bob Smith
Department: Technical Support

Agent number: 1111
Agent: Jane Doe
Manager: Bob Doe
Department: Sales

The problem comes when we're entering the data. We put in agent number of
1111, apon tabbing out of that field the query is ran which goes to the
table, pulls the records that have the agent number of 1111, and populates
the other data into the system - being the Name/Manager/Department fields on
the form will auto fill and then skip to the Situation field so we can
proceed with our entry.

We are able to simply click the drop down and select the correct
information. So if we were trying to input Jane's information, but got
John's we can manually change it. The issue is we have about 15-25 of these
duplicate agent numbers between the two centers, and short of writting them
all down, we cannot remember which ones are the duplicate ones. Because as
people are hired/fired they change which numbers are duplicated..etc. What
I'm looking for is: When the form runs, and the results for the
Agent/Name/Manager/Department combination is more then one occurence for the
form to flag us, either by pop-up, combo warning, etc. This way we'll be
able to ensure we select the correct agent for the problem at hand.

Hope that helps a bit - get distracted so my flow leaves much to be desired

 

[Dechienne]

Yeah, no problems

Currently the problem is multiple agents have the same agent number. The
query I have setup pulls off a master employee table that has every
agent/name/manager/department combination that is in the system, between
both call centers. The problem came from the Aspect system being seperate,
so it generates numbers for each call center without checking with the other
one first to ensure that number is not used. So we get people in one
building that have the same number as someone in another building across the
state

The main problem is if we put in one agent number, and there is more then
one person with that number, the form will auto pull the first record (macro
to setvalue itemdata(0))

Here's some example data, then more explaination

Example data:

Agent number: 1111
Agent: John Smith
Manager: Bob Smith
Department: Technical Support

Agent number: 1111
Agent: Jane Doe
Manager: Bob Doe
Department: Sales

The problem comes when we're entering the data. We put in agent number of
1111, apon tabbing out of that field the query is ran which goes to the
table, pulls the records that have the agent number of 1111, and populates
the other data into the system - being the Name/Manager/Department fields on
the form will auto fill and then skip to the Situation field so we can
proceed with our entry.

We are able to simply click the drop down and select the correct
information. So if we were trying to input Jane's information, but got
John's we can manually change it. The issue is we have about 15-25 of these
duplicate agent numbers between the two centers, and short of writting them
all down, we cannot remember which ones are the duplicate ones. Because as
people are hired/fired they change which numbers are duplicated..etc. What
I'm looking for is: When the form runs, and the results for the
Agent/Name/Manager/Department combination is more then one occurence for the
form to flag us, either by pop-up, combo warning, etc. This way we'll be
able to ensure we select the correct agent for the problem at hand.

Hope that helps a bit - get distracted so my flow leaves much to be desired

 

[Steve Schapel]

Dechienne,

Ok, thanks for the further explanation, I think I understand now what
you are asking.

Well, ideally you should do something about re-allocating the Agent
number so it is unique. In fact, I hesitate to suggest a solution that
just caters to the database design flaw. But in the meantime, you could
put code something like this in the After Update event of the Agent
Number control...
If DCount("*","Employees","[Agent Number]=" & Me.Agent_Number)>1 Then
MsgBox "Alert! More than one with this number"
End If

 

[Dechienne]

Thanks Steve

Sadly getting the number Unique isn't going to happen, the guys in control
of the aspect system have already said as much, so I gotta work with the
broken junk I've been given

Dechienne,

Ok, thanks for the further explanation, I think I understand now what
you are asking.

Well, ideally you should do something about re-allocating the Agent
number so it is unique. In fact, I hesitate to suggest a solution that
just caters to the database design flaw. But in the meantime, you could
put code something like this in the After Update event of the Agent
Number control...
If DCount("*","Employees","[Agent Number]=" & Me.Agent_Number)>1 Then
MsgBox "Alert! More than one with this number"
End If

--
Steve Schapel, Microsoft Access MVP

Yeah, no problems

Currently the problem is multiple agents have the same agent number. The
query I have setup pulls off a master employee table that has every
agent/name/manager/department combination that is in the system, between
both call centers. The problem came from the Aspect system being seperate,
so it generates numbers for each call center without checking with the other
one first to ensure that number is not used. So we get people in one
building that have the same number as someone in another building across the
state

The main problem is if we put in one agent number, and there is more then
one person with that number, the form will auto pull the first record (macro
to setvalue itemdata(0))

Here's some example data, then more explaination

Example data:

Agent number: 1111
Agent: John Smith
Manager: Bob Smith
Department: Technical Support

Agent number: 1111
Agent: Jane Doe
Manager: Bob Doe
Department: Sales

The problem comes when we're entering the data. We put in agent number of
1111, apon tabbing out of that field the query is ran which goes to the
table, pulls the records that have the agent number of 1111, and populates
the other data into the system - being the Name/Manager/Department fields on
the form will auto fill and then skip to the Situation field so we can
proceed with our entry.

We are able to simply click the drop down and select the correct
information. So if we were trying to input Jane's information, but got
John's we can manually change it. The issue is we have about 15-25 of these
duplicate agent numbers between the two centers, and short of writting them
all down, we cannot remember which ones are the duplicate ones. Because as
people are hired/fired they change which numbers are duplicated..etc. What
I'm looking for is: When the form runs, and the results for the
Agent/Name/Manager/Department combination is more then one occurence for the
form to flag us, either by pop-up, combo warning, etc. This way we'll be
able to ensure we select the correct agent for the problem at hand.

 

[Steve Schapel]

Dechienne,

If the value of the Agent Number can't be altered, maybe there could be
an additional field to identify the Call Cantre. Then, the combination
of this field with the Agent Number would uniquely identify every
Employee, and you could use this in your combobox instead.

 

[Dechienne]

We thought about using the employee number, which is unique across the
company. However the system we use to monitor the agents doesn't have the
ability to display the employee number, we've got a seperate system that
houses that information and I'm looking to see if there is a way I can merge
the two, or link from one to the other. It's a big mess right now, and
trying to fix it will take a large amount of time sadly.

The only other thing we could do is input like the department name in
additon to the agent number, however since 95% of our employees have unique
numbers, it's kind of a step backwards in
efficiency.



I've got a few of my guys looking at this with me to see if we can figure
other stuff out - all the input you've given us had made a big impact
already, and made our lives much easier. Leaving us time to make a dent in
the stack of other projects we've got going. So thanks again from all 5 of
us