List Boxes -Help

[SG]

I am trying to create linked list boxes but I am having a little
difficulty..

I have the following piece of code on he load form with popluates list0 with
room conditions this is fine.

Private Sub Form_Load()
Me.List0.RowSource = _
"SELECT DISTINCT RoomCondition FROM tblRoomCondition"

End Sub

The next bit is where I am getting a little lost. What I want to happen is
when a user clicks on a room condition it displays a list of rooms by type
example Double. I have a table called tblrooms which stores the room name,
conditionid, room typeid, roomclassid and floors id. I need to know how to
get the room type name and not the ID number (primary key)


Private Sub List0_AfterUpdate()

With Me.List2
.RowSource = _
"SELECT DISTINCT Conditionid FROM tblRooms " & _
"Where Conditionid = " & Chr(34) & Me.List0 & Chr(34)
.Requery
End With
Me.Label3.Caption = Me.List2.ListCount & _
" Rooms " & _
Me.List0
End Sub


Any help would be much appreciated I've tried for hours but not getting
anywhere.

Thank you in advance.

S

 

[Marshall Barton]

I am trying to create linked list boxes but I am having a little
difficulty..

I have the following piece of code on he load form with popluates list0 with
room conditions this is fine.

Private Sub Form_Load()
Me.List0.RowSource = _
"SELECT DISTINCT RoomCondition FROM tblRoomCondition"

End Sub

The next bit is where I am getting a little lost. What I want to happen is
when a user clicks on a room condition it displays a list of rooms by type
example Double. I have a table called tblrooms which stores the room name,
conditionid, room typeid, roomclassid and floors id. I need to know how to
get the room type name and not the ID number (primary key)


Private Sub List0_AfterUpdate()

With Me.List2
.RowSource = _
"SELECT DISTINCT Conditionid FROM tblRooms " & _
"Where Conditionid = " & Chr(34) & Me.List0 & Chr(34)
.Requery
End With
Me.Label3.Caption = Me.List2.ListCount & _
" Rooms " & _
Me.List0
End Sub

With Me.List2
.RowSource = _
"SELECT DISTINCT T.RoomTypeID, T.TypeName " & _
"FROM tblRooms As R INNER JOIN tblTypes As T " & _
" ON R.RoomTypeID = T.RoomTypeID " & _
"Where R.Conditionid = " & Chr(34) & Me.List0 & Chr(34)
End With

Note that it is a waste of time/resources to requery the
list/combo box because setting the RowSource does that
automatically.

 

[SG]

I have done this ....

With Me.List2

..RowSource = _

"SELECT tblRoomCondition.RoomCondition, tblRoomType.RoomType" & _

"FROM tblRoomCondition INNER JOIN (tblRoomType INNER JOIN tblRooms ON
tblRoomType.RoomTypeID = tblRooms.RoomTypeID) ON
tblRoomCondition.ConditionID = tblRooms.ConditionID" & _

"GROUP BY tblRoomCondition.RoomCondition, tblRoomType.RoomType" & _

"Where tblRoomcondition.Condition = " & Chr(34) & Me.List0 & Chr(34)

..Requery

End With

Me.Label3.Caption = Me.List2.ListCount & _

" Rooms " & _

Me.List0

But my list2 is still not populated any further ideas?

 

[Marshall Barton]

It would be unproductive for me to make further guesses
until after you explain why you are using GROUP BY and why
you think it is necessary to include tblRoomCondition in
both the SELECT and FROM clauses.

The GROUP BY is just baffling, unless you felt some need to
avoid using DISTINCT.

From what I understand, tblRooms contains the ConditionID
which is also the BoundColumn in List0. This tells me that
is what should be in the WHERE clause. As far as I can see,
tblRoomcondition.Condition is totally irrelevant to this
query. If that's correct, then tblRoomcondition is also
irrelevant and should be removed.

 

[SG]

Marshall,

I have removed the Group By and used DISTINCT

I had originally used a query and copied the SQL in to the code. Currently
list 0 is displaying the roomcondition 'Excellent' but list2 is not
displaying anything at all. what should be happening is list2 should be
displayind room types with the condition of excellent from the tblrooms but
I think the issue is that rather than excellent showing in tblrooms primary
key conditionid is showing.

Hope this makes more sense...

S

It would be unproductive for me to make further guesses
until after you explain why you are using GROUP BY and why
you think it is necessary to include tblRoomCondition in
both the SELECT and FROM clauses.

The GROUP BY is just baffling, unless you felt some need to
avoid using DISTINCT.

From what I understand, tblRooms contains the ConditionID
which is also the BoundColumn in List0. This tells me that
is what should be in the WHERE clause. As far as I can see,
tblRoomcondition.Condition is totally irrelevant to this
query. If that's correct, then tblRoomcondition is also
irrelevant and should be removed.
--
Marsh
MVP [MS Access]

I have done this ....

With Me.List2

.RowSource = _

"SELECT tblRoomCondition.RoomCondition, tblRoomType.RoomType" & _

"FROM tblRoomCondition INNER JOIN (tblRoomType INNER JOIN tblRooms ON
tblRoomType.RoomTypeID = tblRooms.RoomTypeID) ON
tblRoomCondition.ConditionID = tblRooms.ConditionID" & _

"GROUP BY tblRoomCondition.RoomCondition, tblRoomType.RoomType" & _

"Where tblRoomcondition.Condition = " & Chr(34) & Me.List0 & Chr(34)

.Requery

End With

Me.Label3.Caption = Me.List2.ListCount & _

" Rooms " & _

Me.List0

But my list2 is still not populated any further ideas?


"Marshall Barton" wrote

 

[Marshall Barton]

It doesn't matter what list0 is displaying. What is
displayed is determined by the ColumnWidths property. You
hide a column in a list .combo box just by setting its
corresponding ColumnWidth to 0.

The Value of list0 is what is important here and the is
determined by the BoundColumn property. You want the value
to be the ConditionID field, so make sure that the
BoundColumn agrees with that.

Be sure to post a copy/paste of the query's SQL view
whenever you need help with a query so we can avoid a lot of
back and forth just to see what we're working with.