List Box SOS, Urgent, Please Help

[Julius]

I have 3 list boxes I am using on a form, I need list box 3 to reference List
box 2, and List box 2 to reference List Box 1. So in other words when I hope
the form List box 2 and three are blank, when I selet a call center in List
box 1, it populates my Team Leader List in list box 2, then when i select a
Team leader in list box 2, it displays the agents for that Team leader in
list box 3. No so far I have already built the form I put the list boxes in
their respective places this is where I get lost

Select Field1, Field2 From TableName Where FieldName =
Forms![FormName]![ListBoxAName]

I got that from another question somebody else asked. Can someone explain
this to me as if I was a four year old. If I was too confusing then just
explain how do I referenc box 3 to 2 and 2 to 1.

 

[fredg]

I have 3 list boxes I am using on a form, I need list box 3 to reference List
box 2, and List box 2 to reference List Box 1. So in other words when I hope
the form List box 2 and three are blank, when I selet a call center in List
box 1, it populates my Team Leader List in list box 2, then when i select a
Team leader in list box 2, it displays the agents for that Team leader in
list box 3. No so far I have already built the form I put the list boxes in
their respective places this is where I get lost

Select Field1, Field2 From TableName Where FieldName =
Forms![FormName]![ListBoxAName]

I got that from another question somebody else asked. Can someone explain
this to me as if I was a four year old. If I was too confusing then just
explain how do I referenc box 3 to 2 and 2 to 1.

OK but remember, if you were 4 years old you wouldn't be able to read
this reply!

Leave the ListBox2 and ListBox3 rowsource properties blank.

Code the ListBox1 AfterUpdate event:
Me.ListBox2.Rowsource = "Select MyTable.TeamLeader from MyTable Where
MyTable.CallCenter = '" & Me.ListBox1"'"

Code ListBox 2 AfterUpdate event:
Me.ListBox3.Rowsource = "Select MyTable.Agents from MyTable Where
MyTable.TeamLeader = '" & Me.ListBox2"'"

The above code assumes that all 3 fields are text datatype.
If in fact one or more of the fields involved are Number datatype,
for example TeamLeader is actually a Number datatype, then use this
syntax:

Me.ListBox3.Rowsource = "Select MyTable.Agents from MyTable Where
MyTable.TeamLeader = " & Me.ListBox2

Selecting the Call Center in ListBox1 will populate ListBox2 with only
those team leaders associated with that call center, etc.

Change the table and field names to the actual names you are using.

 

[Jeanette Cunningham]

Hi Julius,
something like this air code will set up listbox 2 after user makes a
selection in listbox 1.
-->
Dim strSQL As String
strSQL = "Select TeamLeadID, TeamLeadName " _
& "From TableWithTeamLeadInfo " _
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox " _
& "OrderBy TableWithTeamLeadInfo.TeamLeadName "
Debug.Print strSQL
Me.TeamLeadListBox.RowSource = strSQL
-------------

Note: I am guessing the names of your table, fields and listboxes.
Replace TeamLeadID,TeamLeadName, TableWithTeamLeadInfo, CallCenterID,
CallCenterListBox, TeamLeadListBox with the correct names for your database.

Each listbox will need to have its first column as the bound column.
The first column of each listbox will need to have the width of its first
column set to 0.

If you get duplicate team leader names in your listbox you may need to
change the first line of strSQL to this-->
strSQL = "Select Distinct TeamLeadID, TeamLeadName " _


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Issachar5]

Ok maybe I think I am not doing this right at all, my code keeps turning red
and it keeps jumping to the where and saying this is the end of the
statement. I am not good with codes, so have patience with me. In your
statement shouldn't I brackets somewhere, I did exactly how you have it and
it doesn't work.

Hi Julius,
something like this air code will set up listbox 2 after user makes a
selection in listbox 1.
-->
Dim strSQL As String
strSQL = "Select TeamLeadID, TeamLeadName " _
& "From TableWithTeamLeadInfo " _
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox " _
& "OrderBy TableWithTeamLeadInfo.TeamLeadName "
Debug.Print strSQL
Me.TeamLeadListBox.RowSource = strSQL
-------------

Note: I am guessing the names of your table, fields and listboxes.
Replace TeamLeadID,TeamLeadName, TableWithTeamLeadInfo, CallCenterID,
CallCenterListBox, TeamLeadListBox with the correct names for your database.

Each listbox will need to have its first column as the bound column.
The first column of each listbox will need to have the width of its first
column set to 0.

If you get duplicate team leader names in your listbox you may need to
change the first line of strSQL to this-->
strSQL = "Select Distinct TeamLeadID, TeamLeadName " _


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have 3 list boxes I am using on a form, I need list box 3 to reference
List
box 2, and List box 2 to reference List Box 1. So in other words when I
hope
the form List box 2 and three are blank, when I selet a call center in
List
box 1, it populates my Team Leader List in list box 2, then when i select
a
Team leader in list box 2, it displays the agents for that Team leader in
list box 3. No so far I have already built the form I put the list boxes
in
their respective places this is where I get lost

Select Field1, Field2 From TableName Where FieldName =
Forms![FormName]![ListBoxAName]

I got that from another question somebody else asked. Can someone explain
this to me as if I was a four year old. If I was too confusing then just
explain how do I referenc box 3 to 2 and 2 to 1.

 

[Issachar5]

I think I did what you expressed however it is not working I am getting a end
statement error on the Where. Shouldn't I have brackets somewhere, this is
how I did it based on what you instructed me

Me.ListBox20.Rowsource = "EmployeeID from Employees Where
New Monitor Table - Test 2.Team = '" & Me.List22"'"

Whats wrong?

I have 3 list boxes I am using on a form, I need list box 3 to reference List
box 2, and List box 2 to reference List Box 1. So in other words when I hope
the form List box 2 and three are blank, when I selet a call center in List
box 1, it populates my Team Leader List in list box 2, then when i select a
Team leader in list box 2, it displays the agents for that Team leader in
list box 3. No so far I have already built the form I put the list boxes in
their respective places this is where I get lost

Select Field1, Field2 From TableName Where FieldName =
Forms![FormName]![ListBoxAName]

I got that from another question somebody else asked. Can someone explain
this to me as if I was a four year old. If I was too confusing then just
explain how do I referenc box 3 to 2 and 2 to 1.

OK but remember, if you were 4 years old you wouldn't be able to read
this reply!

Leave the ListBox2 and ListBox3 rowsource properties blank.

Code the ListBox1 AfterUpdate event:
Me.ListBox2.Rowsource = "Select MyTable.TeamLeader from MyTable Where
MyTable.CallCenter = '" & Me.ListBox1"'"

Code ListBox 2 AfterUpdate event:
Me.ListBox3.Rowsource = "Select MyTable.Agents from MyTable Where
MyTable.TeamLeader = '" & Me.ListBox2"'"

The above code assumes that all 3 fields are text datatype.
If in fact one or more of the fields involved are Number datatype,
for example TeamLeader is actually a Number datatype, then use this
syntax:

Me.ListBox3.Rowsource = "Select MyTable.Agents from MyTable Where
MyTable.TeamLeader = " & Me.ListBox2

Selecting the Call Center in ListBox1 will populate ListBox2 with only
those team leaders associated with that call center, etc.

Change the table and field names to the actual names you are using.

 

[Jeanette Cunningham]

Yes, I left out something on that line.
I left out -->
<space> & " <space>

Replace this line-->
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox " _

with this-->
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox & " " _

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Ok maybe I think I am not doing this right at all, my code keeps turning
red
and it keeps jumping to the where and saying this is the end of the
statement. I am not good with codes, so have patience with me. In your
statement shouldn't I brackets somewhere, I did exactly how you have it
and
it doesn't work.

Hi Julius,
something like this air code will set up listbox 2 after user makes a
selection in listbox 1.
-->
Dim strSQL As String
strSQL = "Select TeamLeadID, TeamLeadName " _
& "From TableWithTeamLeadInfo " _
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox " _
& "OrderBy TableWithTeamLeadInfo.TeamLeadName "
Debug.Print strSQL
Me.TeamLeadListBox.RowSource = strSQL
-------------

Note: I am guessing the names of your table, fields and listboxes.
Replace TeamLeadID,TeamLeadName, TableWithTeamLeadInfo, CallCenterID,
CallCenterListBox, TeamLeadListBox with the correct names for your
database.

Each listbox will need to have its first column as the bound column.
The first column of each listbox will need to have the width of its first
column set to 0.

If you get duplicate team leader names in your listbox you may need to
change the first line of strSQL to this-->
strSQL = "Select Distinct TeamLeadID, TeamLeadName " _


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have 3 list boxes I am using on a form, I need list box 3 to reference
List
box 2, and List box 2 to reference List Box 1. So in other words when
I
hope
the form List box 2 and three are blank, when I selet a call center in
List
box 1, it populates my Team Leader List in list box 2, then when i
select
a
Team leader in list box 2, it displays the agents for that Team leader
in
list box 3. No so far I have already built the form I put the list
boxes
in
their respective places this is where I get lost

Select Field1, Field2 From TableName Where FieldName =
Forms![FormName]![ListBoxAName]

I got that from another question somebody else asked. Can someone
explain
this to me as if I was a four year old. If I was too confusing then
just
explain how do I referenc box 3 to 2 and 2 to 1.

 

[Issachar5]

Thank you so much for your assistance can you take a look at this code and
tell me what I am doing wrong. I keep getting Syntax Error or high light
yellow around '" & Me.List48 "'"

Me.List28.RowSource = "Select [Team Leaders].[Team ID], [Team
Leaders].[Team] FROM [Team Leaders]; Where "
tblCallCenters.CallCenterID = '" & Me.List48 "'"

Yes, I left out something on that line.
I left out -->
<space> & " <space>

Replace this line-->
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox " _

with this-->
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox & " " _

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Ok maybe I think I am not doing this right at all, my code keeps turning
red
and it keeps jumping to the where and saying this is the end of the
statement. I am not good with codes, so have patience with me. In your
statement shouldn't I brackets somewhere, I did exactly how you have it
and
it doesn't work.

Hi Julius,
something like this air code will set up listbox 2 after user makes a
selection in listbox 1.
-->
Dim strSQL As String
strSQL = "Select TeamLeadID, TeamLeadName " _
& "From TableWithTeamLeadInfo " _
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox " _
& "OrderBy TableWithTeamLeadInfo.TeamLeadName "
Debug.Print strSQL
Me.TeamLeadListBox.RowSource = strSQL
-------------

Note: I am guessing the names of your table, fields and listboxes.
Replace TeamLeadID,TeamLeadName, TableWithTeamLeadInfo, CallCenterID,
CallCenterListBox, TeamLeadListBox with the correct names for your
database.

Each listbox will need to have its first column as the bound column.
The first column of each listbox will need to have the width of its first
column set to 0.

If you get duplicate team leader names in your listbox you may need to
change the first line of strSQL to this-->
strSQL = "Select Distinct TeamLeadID, TeamLeadName " _


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


I have 3 list boxes I am using on a form, I need list box 3 to reference
List
box 2, and List box 2 to reference List Box 1. So in other words when
I
hope
the form List box 2 and three are blank, when I selet a call center in
List
box 1, it populates my Team Leader List in list box 2, then when i
select
a
Team leader in list box 2, it displays the agents for that Team leader
in
list box 3. No so far I have already built the form I put the list
boxes
in
their respective places this is where I get lost

Select Field1, Field2 From TableName Where FieldName =
Forms![FormName]![ListBoxAName]

I got that from another question somebody else asked. Can someone
explain
this to me as if I was a four year old. If I was too confusing then
just
explain how do I referenc box 3 to 2 and 2 to 1.

 

[Jeanette Cunningham]

For a text field it is often easier to use 3 double quotes instead of a
single quote followed by a double quote.

replace this-->
'" & Me.List48 "'"

with this-->
""" & Me.List48 & """ "

Expanded to be clearer-->
3 double quotes before the &Me.
and 3 double quotes after the 8 &
followed by one more double quote at the end of the line.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thank you so much for your assistance can you take a look at this code and
tell me what I am doing wrong. I keep getting Syntax Error or high light
yellow around '" & Me.List48 "'"

Me.List28.RowSource = "Select [Team Leaders].[Team ID], [Team
Leaders].[Team] FROM [Team Leaders]; Where "
tblCallCenters.CallCenterID = '" & Me.List48 "'"

Yes, I left out something on that line.
I left out -->
<space> & " <space>

Replace this line-->
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox " _

with this-->
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox & " "
_

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Ok maybe I think I am not doing this right at all, my code keeps
turning
red
and it keeps jumping to the where and saying this is the end of the
statement. I am not good with codes, so have patience with me. In
your
statement shouldn't I brackets somewhere, I did exactly how you have it
and
it doesn't work.

:

Hi Julius,
something like this air code will set up listbox 2 after user makes a
selection in listbox 1.
-->
Dim strSQL As String
strSQL = "Select TeamLeadID, TeamLeadName " _
& "From TableWithTeamLeadInfo " _
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox "
_
& "OrderBy TableWithTeamLeadInfo.TeamLeadName "
Debug.Print strSQL
Me.TeamLeadListBox.RowSource = strSQL
-------------

Note: I am guessing the names of your table, fields and listboxes.
Replace TeamLeadID,TeamLeadName, TableWithTeamLeadInfo, CallCenterID,
CallCenterListBox, TeamLeadListBox with the correct names for your
database.

Each listbox will need to have its first column as the bound column.
The first column of each listbox will need to have the width of its
first
column set to 0.

If you get duplicate team leader names in your listbox you may need to
change the first line of strSQL to this-->
strSQL = "Select Distinct TeamLeadID, TeamLeadName " _


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


I have 3 list boxes I am using on a form, I need list box 3 to
reference
List
box 2, and List box 2 to reference List Box 1. So in other words
when
I
hope
the form List box 2 and three are blank, when I selet a call center
in
List
box 1, it populates my Team Leader List in list box 2, then when i
select
a
Team leader in list box 2, it displays the agents for that Team
leader
in
list box 3. No so far I have already built the form I put the list
boxes
in
their respective places this is where I get lost

Select Field1, Field2 From TableName Where FieldName =
Forms![FormName]![ListBoxAName]

I got that from another question somebody else asked. Can someone
explain
this to me as if I was a four year old. If I was too confusing then
just
explain how do I referenc box 3 to 2 and 2 to 1.

 

[Issachar5]

Yes I fixed that now I get a message that highlights the FROM saying it is
the expected end of statement

Private Sub List48_AfterUpdate()
Me.List28.RowSource = "Select [Team Leaders].[Team ID], [Team
Leaders].[Team] FROM [Team Leaders]; Where"
[tblCallCenters].[CallCenterID], [tblCallCenters].[CallCenterName] FROM
[tblCallCenters]; = """ & Me.List48 """ "
End Sub

For a text field it is often easier to use 3 double quotes instead of a
single quote followed by a double quote.

replace this-->
'" & Me.List48 "'"

with this-->
""" & Me.List48 & """ "

Expanded to be clearer-->
3 double quotes before the &Me.
and 3 double quotes after the 8 &
followed by one more double quote at the end of the line.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thank you so much for your assistance can you take a look at this code and
tell me what I am doing wrong. I keep getting Syntax Error or high light
yellow around '" & Me.List48 "'"

Me.List28.RowSource = "Select [Team Leaders].[Team ID], [Team
Leaders].[Team] FROM [Team Leaders]; Where "
tblCallCenters.CallCenterID = '" & Me.List48 "'"

Yes, I left out something on that line.
I left out -->
<space> & " <space>

Replace this line-->
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox " _

with this-->
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox & " "
_

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Ok maybe I think I am not doing this right at all, my code keeps
turning
red
and it keeps jumping to the where and saying this is the end of the
statement. I am not good with codes, so have patience with me. In
your
statement shouldn't I brackets somewhere, I did exactly how you have it
and
it doesn't work.

:

Hi Julius,
something like this air code will set up listbox 2 after user makes a
selection in listbox 1.
-->
Dim strSQL As String
strSQL = "Select TeamLeadID, TeamLeadName " _
& "From TableWithTeamLeadInfo " _
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox "
_
& "OrderBy TableWithTeamLeadInfo.TeamLeadName "
Debug.Print strSQL
Me.TeamLeadListBox.RowSource = strSQL
-------------

Note: I am guessing the names of your table, fields and listboxes.
Replace TeamLeadID,TeamLeadName, TableWithTeamLeadInfo, CallCenterID,
CallCenterListBox, TeamLeadListBox with the correct names for your
database.

Each listbox will need to have its first column as the bound column.
The first column of each listbox will need to have the width of its
first
column set to 0.

If you get duplicate team leader names in your listbox you may need to
change the first line of strSQL to this-->
strSQL = "Select Distinct TeamLeadID, TeamLeadName " _


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


I have 3 list boxes I am using on a form, I need list box 3 to
reference
List
box 2, and List box 2 to reference List Box 1. So in other words
when
I
hope
the form List box 2 and three are blank, when I selet a call center
in
List
box 1, it populates my Team Leader List in list box 2, then when i
select
a
Team leader in list box 2, it displays the agents for that Team
leader
in
list box 3. No so far I have already built the form I put the list
boxes
in
their respective places this is where I get lost

Select Field1, Field2 From TableName Where FieldName =
Forms![FormName]![ListBoxAName]

I got that from another question somebody else asked. Can someone
explain
this to me as if I was a four year old. If I was too confusing then
just
explain how do I referenc box 3 to 2 and 2 to 1.

 

[fredg]

I think I did what you expressed however it is not working I am getting a end
statement error on the Where. Shouldn't I have brackets somewhere, this is
how I did it based on what you instructed me

Me.ListBox20.Rowsource = "EmployeeID from Employees Where
New Monitor Table - Test 2.Team = '" & Me.List22"'"

Whats wrong?

I have 3 list boxes I am using on a form, I need list box 3 to reference List
box 2, and List box 2 to reference List Box 1. So in other words when I hope
the form List box 2 and three are blank, when I selet a call center in List
box 1, it populates my Team Leader List in list box 2, then when i select a
Team leader in list box 2, it displays the agents for that Team leader in
list box 3. No so far I have already built the form I put the list boxes in
their respective places this is where I get lost

Select Field1, Field2 From TableName Where FieldName =
Forms![FormName]![ListBoxAName]

I got that from another question somebody else asked. Can someone explain
this to me as if I was a four year old. If I was too confusing then just
explain how do I referenc box 3 to 2 and 2 to 1.

OK but remember, if you were 4 years old you wouldn't be able to read
this reply!

Leave the ListBox2 and ListBox3 rowsource properties blank.

Code the ListBox1 AfterUpdate event:
Me.ListBox2.Rowsource = "Select MyTable.TeamLeader from MyTable Where
MyTable.CallCenter = '" & Me.ListBox1"'"

Code ListBox 2 AfterUpdate event:
Me.ListBox3.Rowsource = "Select MyTable.Agents from MyTable Where
MyTable.TeamLeader = '" & Me.ListBox2"'"

The above code assumes that all 3 fields are text datatype.
If in fact one or more of the fields involved are Number datatype,
for example TeamLeader is actually a Number datatype, then use this
syntax:

Me.ListBox3.Rowsource = "Select MyTable.Agents from MyTable Where
MyTable.TeamLeader = " & Me.ListBox2

Selecting the Call Center in ListBox1 will populate ListBox2 with only
those team leaders associated with that call center, etc.

Change the table and field names to the actual names you are using.

A couple of mistakes here. One by me and several by you.

My error was I left out an ampersand (&).
Was
Where MyTable.CallCenter = '" & Me.ListBox1"'"

Should have been
Where MyTable.CallCenter = '" & Me.ListBox1 & "'"

I made the same mistake on both AfterUpdate events (because I copied
and pasted).

Your errors..
Me.ListBox20.Rowsource = "EmployeeID from Employees Where
New Monitor Table - Test 2.Team = '" & Me.List22"'"

1) You left off the word Select and you should have included the table
name after the Select word.
MeListBox20.Rowsource = "Select MyTable.EmployeeID ... etc"

2) Is the table name "New Monitor Table"?
Including spaces in Table and/or Field names can be done, but then you
MUST always bracket the name whenever you use it.

Me.ListBox20.Rowsource = "Select MyTable.EmployeeID from Employees
Where [New Monitor Table] ......etc.

3) The next part I don't understand. What are you attempting with
"- Test 2.Team = "?
What is the purpose of the "-" sign?
What is the actual table name and/or Field names here?

4) It appears you wish to show the EmployeeID field in the combo box.
Is that a Number datatype?
Are you not wishing to show the EmployeeName field?

If EmployeeID is a Text Datatype, then the correct syntax would be:

Me.ListBox20.Rowsource = "Select [Employees].EmployeeID from
[Employees] Where [Employees].EmployeeID = '" & Me.List22 & "'"


If The EmployeeId field is a Number datatype, then you must use
different Syntax.

Me.ListBox20.Rowsource = "Select [Employees].EmployeeID from
[Employees] Where [Employees].EmployeeID = " & Me.List22

Try this again.

 

[Jeanette Cunningham]

Here is the complete sub for List48. I removed some semi colons and re-wrote
the where clause.
You should be able to copy and paste this without worrying about line
breaks.
----------------------
Private Sub List48_AfterUpdate()
Me.List28.RowSource = "Select [Team Leaders].[Team ID], " _
& "[TeamLeaders].[Team] " _
& "FROM [Team Leaders] " _
& "Where [tblCallCenters].[CallCenterID] = """ & Me.List48 """ " _
& "Order By "[TeamLeaders].[Team]"
End Sub
-----------------------

I strongly suggest that you rewrite the code in a way that will help you to
debug it-->
-----------------------
Private Sub List48_AfterUpdate()
Dim strSQL As String
strSQL = "Select [Team Leaders].[Team ID], " _
& "[TeamLeaders].[Team] " _
& "FROM [Team Leaders] " _
& "Where [tblCallCenters].[CallCenterID] = """ & Me.List48 """ " _
& "Order By "[TeamLeaders].[Team]"
Debug.Print strSQL
Me.List28.RowSource = strSQL
End Sub
-----------------------------

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Yes I fixed that now I get a message that highlights the FROM saying it is
the expected end of statement

Private Sub List48_AfterUpdate()
Me.List28.RowSource = "Select [Team Leaders].[Team ID], [Team
Leaders].[Team] FROM [Team Leaders]; Where"
[tblCallCenters].[CallCenterID], [tblCallCenters].[CallCenterName] FROM
[tblCallCenters]; = """ & Me.List48 """ "
End Sub

For a text field it is often easier to use 3 double quotes instead of a
single quote followed by a double quote.

replace this-->
'" & Me.List48 "'"

with this-->
""" & Me.List48 & """ "

Expanded to be clearer-->
3 double quotes before the &Me.
and 3 double quotes after the 8 &
followed by one more double quote at the end of the line.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thank you so much for your assistance can you take a look at this code
and
tell me what I am doing wrong. I keep getting Syntax Error or high
light
yellow around '" & Me.List48 "'"

Me.List28.RowSource = "Select [Team Leaders].[Team ID], [Team
Leaders].[Team] FROM [Team Leaders]; Where "
tblCallCenters.CallCenterID = '" & Me.List48 "'"

:

Yes, I left out something on that line.
I left out -->
<space> & " <space>

Replace this line-->
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox "
_

with this-->
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox &
" "
_

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Ok maybe I think I am not doing this right at all, my code keeps
turning
red
and it keeps jumping to the where and saying this is the end of the
statement. I am not good with codes, so have patience with me. In
your
statement shouldn't I brackets somewhere, I did exactly how you have
it
and
it doesn't work.

:

Hi Julius,
something like this air code will set up listbox 2 after user makes
a
selection in listbox 1.
-->
Dim strSQL As String
strSQL = "Select TeamLeadID, TeamLeadName " _
& "From TableWithTeamLeadInfo " _
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox
"
_
& "OrderBy TableWithTeamLeadInfo.TeamLeadName "
Debug.Print strSQL
Me.TeamLeadListBox.RowSource = strSQL
-------------

Note: I am guessing the names of your table, fields and listboxes.
Replace TeamLeadID,TeamLeadName, TableWithTeamLeadInfo,
CallCenterID,
CallCenterListBox, TeamLeadListBox with the correct names for your
database.

Each listbox will need to have its first column as the bound
column.
The first column of each listbox will need to have the width of its
first
column set to 0.

If you get duplicate team leader names in your listbox you may need
to
change the first line of strSQL to this-->
strSQL = "Select Distinct TeamLeadID, TeamLeadName " _


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


I have 3 list boxes I am using on a form, I need list box 3 to
reference
List
box 2, and List box 2 to reference List Box 1. So in other words
when
I
hope
the form List box 2 and three are blank, when I selet a call
center
in
List
box 1, it populates my Team Leader List in list box 2, then when
i
select
a
Team leader in list box 2, it displays the agents for that Team
leader
in
list box 3. No so far I have already built the form I put the
list
boxes
in
their respective places this is where I get lost

Select Field1, Field2 From TableName Where FieldName =
Forms![FormName]![ListBoxAName]

I got that from another question somebody else asked. Can
someone
explain
this to me as if I was a four year old. If I was too confusing
then
just
explain how do I referenc box 3 to 2 and 2 to 1.

 

[Jeanette Cunningham]

Oops
there is one double quote too many in the Order By line.
Replace -->
& "Order By "[TeamLeaders].[Team]"


with this-->
& "Order By [TeamLeaders].[Team]"

For both examples of the code in my previous post.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Here is the complete sub for List48. I removed some semi colons and
re-wrote the where clause.
You should be able to copy and paste this without worrying about line
breaks.
----------------------
Private Sub List48_AfterUpdate()
Me.List28.RowSource = "Select [Team Leaders].[Team ID], " _
& "[TeamLeaders].[Team] " _
& "FROM [Team Leaders] " _
& "Where [tblCallCenters].[CallCenterID] = """ & Me.List48 """ " _
& "Order By "[TeamLeaders].[Team]"
End Sub
-----------------------

I strongly suggest that you rewrite the code in a way that will help you
to debug it-->
-----------------------
Private Sub List48_AfterUpdate()
Dim strSQL As String
strSQL = "Select [Team Leaders].[Team ID], " _
& "[TeamLeaders].[Team] " _
& "FROM [Team Leaders] " _
& "Where [tblCallCenters].[CallCenterID] = """ & Me.List48 """ " _
& "Order By "[TeamLeaders].[Team]"
Debug.Print strSQL
Me.List28.RowSource = strSQL
End Sub
-----------------------------

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Yes I fixed that now I get a message that highlights the FROM saying it
is
the expected end of statement

Private Sub List48_AfterUpdate()
Me.List28.RowSource = "Select [Team Leaders].[Team ID], [Team
Leaders].[Team] FROM [Team Leaders]; Where"
[tblCallCenters].[CallCenterID], [tblCallCenters].[CallCenterName] FROM
[tblCallCenters]; = """ & Me.List48 """ "
End Sub

For a text field it is often easier to use 3 double quotes instead of a
single quote followed by a double quote.

replace this-->
'" & Me.List48 "'"

with this-->
""" & Me.List48 & """ "

Expanded to be clearer-->
3 double quotes before the &Me.
and 3 double quotes after the 8 &
followed by one more double quote at the end of the line.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Thank you so much for your assistance can you take a look at this code
and
tell me what I am doing wrong. I keep getting Syntax Error or high
light
yellow around '" & Me.List48 "'"

Me.List28.RowSource = "Select [Team Leaders].[Team ID], [Team
Leaders].[Team] FROM [Team Leaders]; Where "
tblCallCenters.CallCenterID = '" & Me.List48 "'"

:

Yes, I left out something on that line.
I left out -->
<space> & " <space>

Replace this line-->
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox "
_

with this-->
& "Where TableWithTeamLeadInfo.CallCenterID = " & CallCenterListBox &
" "
_

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Ok maybe I think I am not doing this right at all, my code keeps
turning
red
and it keeps jumping to the where and saying this is the end of the
statement. I am not good with codes, so have patience with me. In
your
statement shouldn't I brackets somewhere, I did exactly how you
have it
and
it doesn't work.

:

Hi Julius,
something like this air code will set up listbox 2 after user
makes a
selection in listbox 1.
-->
Dim strSQL As String
strSQL = "Select TeamLeadID, TeamLeadName " _
& "From TableWithTeamLeadInfo " _
& "Where TableWithTeamLeadInfo.CallCenterID = " &
CallCenterListBox "
_
& "OrderBy TableWithTeamLeadInfo.TeamLeadName "
Debug.Print strSQL
Me.TeamLeadListBox.RowSource = strSQL
-------------

Note: I am guessing the names of your table, fields and listboxes.
Replace TeamLeadID,TeamLeadName, TableWithTeamLeadInfo,
CallCenterID,
CallCenterListBox, TeamLeadListBox with the correct names for your
database.

Each listbox will need to have its first column as the bound
column.
The first column of each listbox will need to have the width of
its
first
column set to 0.

If you get duplicate team leader names in your listbox you may
need to
change the first line of strSQL to this-->
strSQL = "Select Distinct TeamLeadID, TeamLeadName " _


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


I have 3 list boxes I am using on a form, I need list box 3 to
reference
List
box 2, and List box 2 to reference List Box 1. So in other
words
when
I
hope
the form List box 2 and three are blank, when I selet a call
center
in
List
box 1, it populates my Team Leader List in list box 2, then when
i
select
a
Team leader in list box 2, it displays the agents for that Team
leader
in
list box 3. No so far I have already built the form I put the
list
boxes
in
their respective places this is where I get lost

Select Field1, Field2 From TableName Where FieldName =
Forms![FormName]![ListBoxAName]

I got that from another question somebody else asked. Can
someone
explain
this to me as if I was a four year old. If I was too confusing
then
just
explain how do I referenc box 3 to 2 and 2 to 1.