Cascading List Problem

[hjozinovic]

hjozinovic,

Thank you for this creative idea. I like it conceptually, but admittedly VBA is not a language that I know very well at all so I am uncertain how to go about doing what you are suggesting.

Presently the RowSource for ROOM is determined by the code that I have already listed previously; what do I need to change to make it work the way you suggest? I am not sure also what you mean by changing the column widths, do you mean within Access, or VBA? My apologies ... I feel like a total newb (well I suppose I am at this) right now which is something I haven't been in a long time. :-)

If you can explain further here or in a PM I am happy to give it a try!

Private Sub Building_AfterU pdate()

'When the Building is selected, the appropriate Room list will
'display in the drop down list of CboRoom

  With Me![Room]
     If IsNull(Me!Building) Then
        .RowSource = ""
      Else
        .RowSource = "SELECT [RoomNumber], [RoomDescription] " & _
         "FROM tblRoom " & _
         "WHERE [BuildingID]=" & Me!Building
      
      End If
     Call .Requery
   End With
 
End Sub

You can use this code as you have it now, with slight change at line 5 where [RoomDescription] is added.
Now go to control cboRoom and set it's Column widths property to 3cm;0cm (instead of Auto).

This column width of 3cm reffers to first column in the drop-down list (RoomNumber), and 0cm reffers to RoomDescription which is present, but is not visible in the list.
Now set RoomDescription 's control Row source to: =cboRoom.Column (1)
Note: First column has index 0, second colum has index 1 etc.

[LosLobo]

hjozinovic,

Getting closer! Now I don't get an error (I commented out my old code to try this solution) that calls up the debugger, however I am getting an unknown type error in the RoomDescription box:

#Name?

So it is trying to pull it thru but something is still off since it isn't associating what we're trying to do.

[LosLobo]

Fixed the #Name? problem (symantec issue) but it still isn't pulling the RoomDescription as it should. Here is how things look:

RoomDescription (a textbox) has a Control Source of "=cboRoom.Colum n(1)" per your instructions.

Now I followed what you were doing, in that this should pull the value of the "hidden column" from cboRoom. If I am following you right, Column(0) would be the Room #, Column(1) should be the RoomDescription ... correct?

Now I tried the following (as a test) for the RoomDescription Control Source:

=cboRoom.Column (0)

Sure enough, it duplicates the room number on the form ... so that works fine but it also tells me that the previous query isn't working right insofar as pulling the RoomDescription . I'm puzzled as to why.

I changed the column values per your instructions (Access converted it from cm to inches), so that is fine, and the query you suggested has no errors (nor should it) so I'm a bit puzzled why Column(1) is empty. Ideas?

[hjozinovic]

Fixed the #Name? problem (symantec issue) but it still isn't pulling the RoomDescription as it should. Here is how things look:

RoomDescription (a textbox) has a Control Source of "=cboRoom.Colum n(1)" per your instructions.

Now I followed what you were doing, in that this should pull the value of the "hidden column" from cboRoom. If I am following you right, Column(0) would be the Room #, Column(1) should be the RoomDescription ... correct?

Now I tried the following (as a test) for the RoomDescription Control Source:

=cboRoom.Column (0)

Sure enough, it duplicates the room number on the form ... so that works fine but it also tells me that the previous query isn't working right insofar as pulling the RoomDescription . I'm puzzled as to why.

I changed the column values per your instructions (Access converted it from cm to inches), so that is fine, and the query you suggested has no errors (nor should it) so I'm a bit puzzled why Column(1) is empty. Ideas?

That's good! Maybe you could change zero width to something other(5cm maybe) and than try it in order to see are there any values in the second row.

ayyyyyy!
Probably the problem is that you have ONLY ONE colum in cboRoom.
Try setting Column count property of the cboRoom control to 2 !!!!!!
This way it will allow you to have TWO columns, even second is hidden!