Filter List Box with Combo Box

C

channell

Ok, I have read and I am still a bit confused... I think I am close, but I
need just a bit of assistance. Thank you very much in advance!

I have a list box poulated with employees. It only shows their names, but
there are really three columns: [Employee ID], [POSITION ID], [EMPLOYEE NAME]

Now, in the cbo box above it, I have it populated with work positions
[POSITION ID].

Here is the code I have right now, maybe I am doing something wrong, please
help. Thank you!!

Private Sub Combo55_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!Combo55
strSQL = strSQL & "POSITION ID"
Me!ListEmployees1.RowSourceType = "Table/Query"
Me!ListEmployees1.RowSource = strSQL
End Sub

I select a position in my cbo box, and the list box goes blank. Thanks!

-Scott Channell
 
F

fredg

Ok, I have read and I am still a bit confused... I think I am close, but I
need just a bit of assistance. Thank you very much in advance!

I have a list box poulated with employees. It only shows their names, but
there are really three columns: [Employee ID], [POSITION ID], [EMPLOYEE NAME]

Now, in the cbo box above it, I have it populated with work positions
[POSITION ID].

Here is the code I have right now, maybe I am doing something wrong, please
help. Thank you!!

Private Sub Combo55_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!Combo55
strSQL = strSQL & "POSITION ID"
Me!ListEmployees1.RowSourceType = "Table/Query"
Me!ListEmployees1.RowSource = strSQL
End Sub

I select a position in my cbo box, and the list box goes blank. Thanks!

-Scott Channell
Click to expand...

I assume Combo55 is the name of the combo box from which you first
select the PositionID. I'll also assume [PositionID] is a Number
datatype.

First set the ListEmployees1 RowsourceType property to Table/Query.

Then code the Combo55 AfterUpdate event (watch out for word wrap on
the longer lines):

Private Sub Combo55_AfterUpdate()
Dim strSQL As String

STRsql = "Select TableName.[Employee ID], TableName.[POSITION ID],
TableName.[EMPLOYEE NAME] "

strSQL = strSQL & "from TableName Where TableName.[Position ID] = " &
Me!Combo55

strSQL = strSQL & " Order by TableName.[Employee Name];"

Me!ListEmployees1.RowSource = strSQL

End Sub

Change TableName to whatever the actual name of the table is.
Note the space after V
TableName.[Employee Name] "
and also
before the O V
= strSQL & " Order by

Why are you including the [Position ID] column in the ListEmployees1
list box? You already know what the position is because you have first
selected it in the Combo55. It will be the same [Position ID] for each
row.
 
C

channell

Fred:

Thank you for your help. I think I am still missing something, so I will
give you the sql of where my info is coming.

The Combo Box is as follows:

SELECT tPOSITION.[BELT ID], tPOSITION.BELT FROM tPOSITION;

The List Box is as follows: (and has to be that way for other things on my
form to work)

SELECT [qEMPLOYEES UNION].[Employee ID], [qEMPLOYEES UNION].[POSITION ID],
[qEMPLOYEES UNION].LastNameFirstName FROM [qEMPLOYEES UNION] ORDER BY
[qEMPLOYEES UNION].LastNameFirstName;

Here is the thing: [BELT ID] and [POSITION ID] are the same thing. I just
named it differently when I first created the Database.

Hope that makes sense. Thank you very much Fred.

-Scott Channell

fredg said:
Ok, I have read and I am still a bit confused... I think I am close, but I
need just a bit of assistance. Thank you very much in advance!

I have a list box poulated with employees. It only shows their names, but
there are really three columns: [Employee ID], [POSITION ID], [EMPLOYEE NAME]

Now, in the cbo box above it, I have it populated with work positions
[POSITION ID].

Here is the code I have right now, maybe I am doing something wrong, please
help. Thank you!!

Private Sub Combo55_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!Combo55
strSQL = strSQL & "POSITION ID"
Me!ListEmployees1.RowSourceType = "Table/Query"
Me!ListEmployees1.RowSource = strSQL
End Sub

I select a position in my cbo box, and the list box goes blank. Thanks!

-Scott Channell
Click to expand...

I assume Combo55 is the name of the combo box from which you first
select the PositionID. I'll also assume [PositionID] is a Number
datatype.

First set the ListEmployees1 RowsourceType property to Table/Query.

Then code the Combo55 AfterUpdate event (watch out for word wrap on
the longer lines):

Private Sub Combo55_AfterUpdate()
Dim strSQL As String

STRsql = "Select TableName.[Employee ID], TableName.[POSITION ID],
TableName.[EMPLOYEE NAME] "

strSQL = strSQL & "from TableName Where TableName.[Position ID] = " &
Me!Combo55

strSQL = strSQL & " Order by TableName.[Employee Name];"

Me!ListEmployees1.RowSource = strSQL

End Sub

Change TableName to whatever the actual name of the table is.
Note the space after V
TableName.[Employee Name] "
and also
before the O V
= strSQL & " Order by

Why are you including the [Position ID] column in the ListEmployees1
list box? You already know what the position is because you have first
selected it in the Combo55. It will be the same [Position ID] for each
row.
Click to expand...
 
F

fredg

Fred:

Thank you for your help. I think I am still missing something, so I will
give you the sql of where my info is coming.

The Combo Box is as follows:

SELECT tPOSITION.[BELT ID], tPOSITION.BELT FROM tPOSITION;

The List Box is as follows: (and has to be that way for other things on my
form to work)

SELECT [qEMPLOYEES UNION].[Employee ID], [qEMPLOYEES UNION].[POSITION ID],
[qEMPLOYEES UNION].LastNameFirstName FROM [qEMPLOYEES UNION] ORDER BY
[qEMPLOYEES UNION].LastNameFirstName;

Here is the thing: [BELT ID] and [POSITION ID] are the same thing. I just
named it differently when I first created the Database.

Hope that makes sense. Thank you very much Fred.

-Scott Channell

fredg said:
Ok, I have read and I am still a bit confused... I think I am close, but I
need just a bit of assistance. Thank you very much in advance!

I have a list box poulated with employees. It only shows their names, but
there are really three columns: [Employee ID], [POSITION ID], [EMPLOYEE NAME]

Now, in the cbo box above it, I have it populated with work positions
[POSITION ID].

Here is the code I have right now, maybe I am doing something wrong, please
help. Thank you!!

Private Sub Combo55_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!Combo55
strSQL = strSQL & "POSITION ID"
Me!ListEmployees1.RowSourceType = "Table/Query"
Me!ListEmployees1.RowSource = strSQL
End Sub

I select a position in my cbo box, and the list box goes blank. Thanks!

-Scott Channell
Click to expand...

I assume Combo55 is the name of the combo box from which you first
select the PositionID. I'll also assume [PositionID] is a Number
datatype.

First set the ListEmployees1 RowsourceType property to Table/Query.

Then code the Combo55 AfterUpdate event (watch out for word wrap on
the longer lines):

Private Sub Combo55_AfterUpdate()
Dim strSQL As String

STRsql = "Select TableName.[Employee ID], TableName.[POSITION ID],
TableName.[EMPLOYEE NAME] "

strSQL = strSQL & "from TableName Where TableName.[Position ID] = " &
Me!Combo55

strSQL = strSQL & " Order by TableName.[Employee Name];"

Me!ListEmployees1.RowSource = strSQL

End Sub

Change TableName to whatever the actual name of the table is.
Note the space after V
TableName.[Employee Name] "
and also
before the O V
= strSQL & " Order by

Why are you including the [Position ID] column in the ListEmployees1
list box? You already know what the position is because you have first
selected it in the Combo55. It will be the same [Position ID] for each
row.
Click to expand...
Click to expand...

So where have you placed the Where Clause that I gave you in my
previous reply? ....
strSQL = strSQL & "from TableName Where TableName.[Position ID] = " &
Me!Combo55)

strSQL = "SELECT [qEMPLOYEES UNION].[Employee ID], [qEMPLOYEES
UNION].[POSITION ID], [qEMPLOYEES UNION].LastNameFirstName FROM
[qEMPLOYEES UNION] Where [qEmployees Union].[Position ID]] = " &
Me![Combo55] & " ORDER BY [qEMPLOYEES UNION].LastNameFirstName;"
 
C

channell

After Messing around with it, it works great! I appreciate your help!!
Thanks Fred.

-Scott Channell

fredg said:
Fred:

Thank you for your help. I think I am still missing something, so I will
give you the sql of where my info is coming.

The Combo Box is as follows:

SELECT tPOSITION.[BELT ID], tPOSITION.BELT FROM tPOSITION;

The List Box is as follows: (and has to be that way for other things on my
form to work)

SELECT [qEMPLOYEES UNION].[Employee ID], [qEMPLOYEES UNION].[POSITION ID],
[qEMPLOYEES UNION].LastNameFirstName FROM [qEMPLOYEES UNION] ORDER BY
[qEMPLOYEES UNION].LastNameFirstName;

Here is the thing: [BELT ID] and [POSITION ID] are the same thing. I just
named it differently when I first created the Database.

Hope that makes sense. Thank you very much Fred.

-Scott Channell

fredg said:
On Mon, 12 Jan 2009 22:51:01 -0800, channell wrote:

Ok, I have read and I am still a bit confused... I think I am close, but I
need just a bit of assistance. Thank you very much in advance!

I have a list box poulated with employees. It only shows their names, but
there are really three columns: [Employee ID], [POSITION ID], [EMPLOYEE NAME]

Now, in the cbo box above it, I have it populated with work positions
[POSITION ID].

Here is the code I have right now, maybe I am doing something wrong, please
help. Thank you!!

Private Sub Combo55_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!Combo55
strSQL = strSQL & "POSITION ID"
Me!ListEmployees1.RowSourceType = "Table/Query"
Me!ListEmployees1.RowSource = strSQL
End Sub

I select a position in my cbo box, and the list box goes blank. Thanks!

-Scott Channell

I assume Combo55 is the name of the combo box from which you first
select the PositionID. I'll also assume [PositionID] is a Number
datatype.

First set the ListEmployees1 RowsourceType property to Table/Query.

Then code the Combo55 AfterUpdate event (watch out for word wrap on
the longer lines):

Private Sub Combo55_AfterUpdate()
Dim strSQL As String

STRsql = "Select TableName.[Employee ID], TableName.[POSITION ID],
TableName.[EMPLOYEE NAME] "

strSQL = strSQL & "from TableName Where TableName.[Position ID] = " &
Me!Combo55

strSQL = strSQL & " Order by TableName.[Employee Name];"

Me!ListEmployees1.RowSource = strSQL

End Sub

Change TableName to whatever the actual name of the table is.
Note the space after V
TableName.[Employee Name] "
and also
before the O V
= strSQL & " Order by

Why are you including the [Position ID] column in the ListEmployees1
list box? You already know what the position is because you have first
selected it in the Combo55. It will be the same [Position ID] for each
row.
Click to expand...
Click to expand...

So where have you placed the Where Clause that I gave you in my
previous reply? ....
strSQL = strSQL & "from TableName Where TableName.[Position ID] = " &
Me!Combo55)

strSQL = "SELECT [qEMPLOYEES UNION].[Employee ID], [qEMPLOYEES
UNION].[POSITION ID], [qEMPLOYEES UNION].LastNameFirstName FROM
[qEMPLOYEES UNION] Where [qEmployees Union].[Position ID]] = " &
Me![Combo55] & " ORDER BY [qEMPLOYEES UNION].LastNameFirstName;"
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combo Box Default Value 2
Multi Select List creating multiple records 13
multi select list box in a form as a query parameter 3
limit the contents of one combo/list box based on what's selected inanother combo/list box 11
Not In List Cbo Help 5
Using values from combo box in subform 2
Referencing a dropdown list box on a form 4
Cascading List Box 4

Top