Cascading List Problem

[LosLobo]

Greetings all. I know that cascading lists are a common problem and in truth I my initial post here was to request help with my own, but then I figured out the right code. That being said, I have a new challenge which I'm not sure how to go about. Here is my situation:

I presently have 2 combo boxes (cboBuilding, cboRoom) and which are properly cascading. That's great, but now I want to be able to pull the RoomDescription for the room that was selected (basically autopopulate a field on the form and have it be uneditable if possible). I am uncertain how to go about doing this.

Here are the basic layouts of my two tables and what I have done:

tblBuilding
[BuildingID] - Primary Key (Autonumber) for Building Table
[BuildingName] - Name of Building

tblRoom
[RoomPK] - Primary Key (Autonumber) for Room Table
[RoomNumber] - Room Number for specific room
[RoomDescription] - Description of the room
[BuildingID] - FK to Building Table; identifies which building specific room is in

Private Sub Building_AfterU pdate()

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

1. With Me![Room]
2. If IsNull(Me!Build ing) Then
3. .RowSource = ""
4. Else
5. .RowSource = "SELECT [RoomNumber] " & _
6. "FROM tblRoom " & _
7. "WHERE [BuildingID]=" & Me!Building
8.
9. End If
10. Call .Requery
11. End With
12. End Sub

** My apologies for how the code looks. I write it spaced out nice and neat but when I post it doesn't include my spaces, any help with how to fix that is appreciated for future posts **

So the query above works fine to autopopulate my Room list, however I am not certain how to go about getting the Room Description onto the form. For one, the source for the form is a single table which has nothing to do (specifically) for the Room, which I used code to work around previously (above). I've managed to get a "Room Description" field onto the form, though it is complaining about the control source (rightfully so) as the primary data source for this form is exclusive of the Room Table data.

I am certain all I need is to create a query which will pull in the room description for the field based on the previous two choices by the user (i.e. the Building and specific Room selected). I know what I want to accomplish, I am just not certain as to the how to make it happen. Do I build another event? If so, which one? After Update again?

Any help is greatly appreciated! If you need more information, please let me know!

[ADezii]

Assuming you wish to display the Room Description in a Text Box named txtRoomDescript ion:

Me![txtRoomDescription] = DLookup("[RoomDescription]", "tblRoom", "[RoomNumber] = " & Me![cboRoom])

[LosLobo]

Assuming you wish to display the Room Description in a Text Box named txtRoomDescript ion:

Me![txtRoomDescription] = DLookup("[RoomDescription]", "tblRoom", "[RoomNumber] = " & Me![cboRoom])

ADezii:

Thanks for this code. Here is the scenario (basically):

User selects Building from drop down list.
User then selects room number from drop down list which is auto-populated based on the Building selected (this coded as an "AfterEvent " in Building Properties).

The goal is for the associated room description to automatically populate once the room number has been selected (user does nothing). I am following your code, but am uncertain where to insert it. I would think I have to do another "After Event" sub-routine in the "Room" properties to have it auto-pop the Room Description (just as I used Building to pop Room using the same logic). Is that so?

Also, my other "issue" (perhaps not) is that the form is based on our primary table, and therefore "RoomDescriptio n" isn't one of the properties of said table, nor would it be directly as that information is pulled from another table (Room). Though I have managed to put it on the form, I know it is complaining about my Control Source. Ideas on that?

Thanks in advance!

[LosLobo]

Okay, I killed the control source so that the field is considered UNBOUND now (duh, not sure why that didn't occur to me previously ... it's early and a Monday :-) but my code isn't working. I had a similar variant of what you had suggested but the only place I can think of putting it is in the "AFTER EVENT" for ROOM in order to create the row source for RoomDescription . Either this is wrong (as it isn't working) or I'm doing something wrong. Here is my "After Event" procedure at present:

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

When I open the form and choose my building, I can choose the room of the building (as it should work), however as soon as I do the "After Event" above kicks in and I get the following Error:

Run-time error '438':

Object doesn't support this property or method

I go to the debugger and it has lines 6-8 (above) highlighted. So there is something about my query it hates. Maybe it is too early in the morning and I am still brushing the cobwebs ... or that I haven't coded in a LONG time, but clearly I am missing something ... and probably something simple too. :-)

I'll keep at it, and thanks in advance for your help!

[LosLobo]

I've looked up the error code with Microsoft Support and it seems to have to do with Windows 95 ... umm ... alright. That made no sense, so I went back to Google and found another gent who has had the same error with one of his bits of code. From this it sounded like the error is occurring because it doesn't like my object, in this case the Me!Room in line 8.

Now what I don't understand with that is if it didn't like it there, why did it accept it when I first started the coded IF THEN loop? The implication with the error is that it can't "find" the Me![Room] which makes no sense. I will continue to bang my head against my desk working on this. Any ideas are welcome. :-)

[ADezii]

I've looked up the error code with Microsoft Support and it seems to have to do with Windows 95 ... umm ... alright. That made no sense, so I went back to Google and found another gent who has had the same error with one of his bits of code. From this it sounded like the error is occurring because it doesn't like my object, in this case the Me!Room in line 8.

Now what I don't understand with that is if it didn't like it there, why did it accept it when I first started the coded IF THEN loop? The implication with the error is that it can't "find" the Me![Room] which makes no sense. I will continue to bang my head against my desk working on this. Any ideas are welcome. :-)

ASSUMPTIONS:

1. Your Combo Box names are cboBuilding and cboRoom.
2. The Bound Column for cboBuilding is BuildingID.
3. cboRoom is a Single Column Combo Box with simply the Room Number listed.
4. You have a Text Box on the Form named txtRoomDescript ion.
5. In the AfterUpdate() Event of cboRoom, try the following code:
   

   Private Sub cboRoom_AfterUpdate()
   'Make sure both the Building and Room Combo Boxes have values in them
   If Not IsNull(Me![cboBuilding]) And Not IsNull(Me![cboRoom]) Then
     Me![txtRoomDescription] = DLookup("[RoomDescription]", "tblRoom", "[RoomNumber] = " & _
                               Me![cboRoom] & " And [BuildingID] = " & Me![BuildingID])
   End If
   End Sub

6. The simpler solution would be to incorporate the [RoomDescription] Field into the Row Source for cboRoom, where, after a Room selection is made, the Description for the Room would be ==> Me![cboRoom].Column(1).
7. Get back to me one way or another and we'll get this issue resolved.

[LosLobo]

Adezii,

Ok, first thank you for all of your help. I have tried your code, but I am getting some errors in it, which appear to be symmantic, but I am still trying to figure out where that particular problem is. Everything you have there appears as though it should work fine. I am still learning the code, and admittedly keep forgetting about DLookup, but your query should work just fine. Instead I get the following error:

Run-time error '2465':

Microsoft Office Access can't find the field 'BuildingID' referred to in your expression.'

This is odd to me because BuildingID is one of the fields in tblRoom (as a FK) so I do not see why it can't find it. I am presently trying to figure out the why, but if you read this and have an epiphany let me know. :-)

Thanks again for your help!

[LosLobo]

Ok, fixed that problem (needed to change the second BuildingID to Building) but now I have a mis-match in data type. Here is the code:

Private Sub Room_AfterUpdate()
'Make sure both the Building and Room Combo Boxes have values in them
If Not IsNull(Me![Building]) And Not IsNull(Me![Room]) Then
  ' Lookup and Insert RoomDescription into the RoomDescription box on form
  Me![RoomDescription] = DLookup("[RoomDescription]", "tblRoom", "[RoomNumber] = " & _
                            Me![Room] & " And [BuildingID] = " & Me![Building])
End If
End Sub

The problem remains in line 6 of the code. I even know what the problem is, which is that BuildingID is a numeric value while Me!Building is text. Basically the "Me!Buildin g" is from the combo box on that form which pulls the actual name of the building (since the user wouldn't know what building 3 would mean for example) while BuildingID is simply that ... the numeric PK for the building.

So I know where the data type mismatch error is coming from and why I am getting it. I am trying to puzzle out how to get around it. The only thing that is coming to mind is to create another nested loop at the end of the statement to align the Me!Building with it's numeric equivalent, but that seems to make things more convoluted than needed. If you have a better idea (likely) I am all ears. :-)

Thanks again!

[LosLobo]

Another question I have ...

I understand the data type issue outlined above, however when I am in the VBA debugging mode and move my cursor over line 6 (above), specifically over "Me![Building]" it gives me the following subtext (value) for that code:

Me![Building] = 1


Ok, so the good news is it is reading the value as the numeric equivalent (instead of the building name itself) which is good, but still doesn't mesh in the code because of the data type issue.

I can't change the BuildingID (FK) in tblRoom to TEXT (from NUMBER) as a data type as that causes another error and prevents my previous cascading drop box. I can't change the data type in tblBuildings as that is the primary key and an AUTONUMBER so that's a done deal.

I am again forced to think I would have to somehow create a nested loop to pull a numeric value back for the query to work but again this seems far too convoluted and there must be an easier way. I've identified the problem well enough (I think) but I am banging my head against the proverbial cyber-wall trying to make it work.

[ADezii]

Ok, fixed that problem (needed to change the second BuildingID to Building) but now I have a mis-match in data type. Here is the code:

Private Sub Room_AfterUpdate()
'Make sure both the Building and Room Combo Boxes have values in them
If Not IsNull(Me![Building]) And Not IsNull(Me![Room]) Then
  ' Lookup and Insert RoomDescription into the RoomDescription box on form
  Me![RoomDescription] = DLookup("[RoomDescription]", "tblRoom", "[RoomNumber] = " & _
                            Me![Room] & " And [BuildingID] = " & Me![Building])
End If
End Sub

The problem remains in line 6 of the code. I even know what the problem is, which is that BuildingID is a numeric value while Me!Building is text. Basically the "Me!Buildin g" is from the combo box on that form which pulls the actual name of the building (since the user wouldn't know what building 3 would mean for example) while BuildingID is simply that ... the numeric PK for the building.

So I know where the data type mismatch error is coming from and why I am getting it. I am trying to puzzle out how to get around it. The only thing that is coming to mind is to create another nested loop at the end of the statement to align the Me!Building with it's numeric equivalent, but that seems to make things more convoluted than needed. If you have a better idea (likely) I am all ears. :-)

Thanks again!

Sounds like you may have to rethink your logic on this one, bit if Me![Building] is referring to the actual Building Name (TEXT) from the Combo Box, then you need to extract the Building ID in the following manner, referring to Code Line #6:

Me![Room] & " And [BuildingID] = " & _
DLookup("[BuildingID]", "tblBuilding", "[BuildingName] = '" & Me![Building] & "'"))

[LosLobo]

Well I would be the first to agree that (in hindsight) this is getting a lot more convoluted than I had originally intended it to become. I think my logic/thought process on what I wanted to see happen was sound, it just became difficult to make it happen based on the field types (it didn't occur to me at the time that I created the database).

I have inserted your code, however I get the "dreaded" error code 2001 "You cancelled the previous operation" ... here is the code in whole as it stands:

Private Sub Room_AfterUpdate()
'Make sure both the Building and Room Combo Boxes have values in them
If Not IsNull(Me![Building]) And Not IsNull(Me![Room]) Then
  ' Lookup and Insert RoomDescription into the RoomDescription box on form
  Me![RoomDescription] = DLookup("[RoomDescription]", "tblRoom", "[RoomNumber] = " & _
                            Me![Room] & " And [BuildingID] = " & _
                            DLookup("[BuildingID]", "tblBuildings", "[BuildingName] = '" & Me![Building] & "'"))
End If
End Sub

The error occurs on line 7 which leads me to believe that we missed something in the syntax of the DLOOKUP statement. Do I need single quotes around BuildingName since it is a string and not numeric? I'm trying to think of what would cause the problem. As I have said before my knowledge/experience of VBA is very small (beginner at best) though I have done code in the past and usually it is just learning the different syntax and/or code style. Even so, I am admittedly new to this and I do appreciate your assistance.

[ADezii]

Well I would be the first to agree that (in hindsight) this is getting a lot more convoluted than I had originally intended it to become. I think my logic/thought process on what I wanted to see happen was sound, it just became difficult to make it happen based on the field types (it didn't occur to me at the time that I created the database).

I have inserted your code, however I get the "dreaded" error code 2001 "You cancelled the previous operation" ... here is the code in whole as it stands:

Private Sub Room_AfterUpdate()
'Make sure both the Building and Room Combo Boxes have values in them
If Not IsNull(Me![Building]) And Not IsNull(Me![Room]) Then
  ' Lookup and Insert RoomDescription into the RoomDescription box on form
  Me![RoomDescription] = DLookup("[RoomDescription]", "tblRoom", "[RoomNumber] = " & _
                            Me![Room] & " And [BuildingID] = " & _
                            DLookup("[BuildingID]", "tblBuildings", "[BuildingName] = '" & Me![Building] & "'"))
End If
End Sub

The error occurs on line 7 which leads me to believe that we missed something in the syntax of the DLOOKUP statement. Do I need single quotes around BuildingName since it is a string and not numeric? I'm trying to think of what would cause the problem. As I have said before my knowledge/experience of VBA is very small (beginner at best) though I have done code in the past and usually it is just learning the different syntax and/or code style. Even so, I am admittedly new to this and I do appreciate your assistance.

If Me![Building] is a String, then you will need Single Quotes around it.

[LosLobo]

If Me![Building] is a String, then you will need Single Quotes around it.

I did as you suggested and got the following error:

Run-time error '2465': Microsoft Office Access can't find the field "Building" referred to in your expression.

Here's the thing ... in the code itself, on line 3 (IF IsNot NULL ...) the value of Building is 1 (at present) because I am using the first building. Perhaps I need to pull back and give a broader view of this issue. I created a dropbox on the form for the user to choose which building, which room, etc they need. So the BUILDINGS table looks like this:

BuildingID - Autonumber
Building - Text

Data for BUILDINGS
1 Building1
2 Building2
3 Building3
(etc)

Now it would make no sense for the user to use a drop down box on a form if it gave them the PK as it would not mean anything to them, whereas the building name does. So there is my logic to that.

The ROWSOURCE for the Combo Box BUILDING is a simple SQL lookup:

SELECT tblBuildings.Bu ildingID, tblBuildings.Bu ilding
FROM tblBuildings
ORDER BY tblBuildings.Bu ilding;

Once the user has chosen the building, the following AfterUpdate sub-routine kicks in:

Private Sub Building_AfterUpdate()
 
'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] " & _
         "FROM tblRoom " & _
         "WHERE [BuildingID]=" & Me!Building
      
      End If
     Call .Requery
   End With
 
End Sub

That code as you can see is to generate the ROWSOURCE for the ROOM dropdown combo box, so that it is only pulling rooms from the Building selected.

Now all of the above works fine ... and this brings us back to where we are which is trying to get the RoomDescription to populate once the Room number is selected. I had thought, originally, that this would not be so difficult, and in truth most debugging issues come down to something simple while we (coders) make things over-complicated. Clearly I am not immune to this because this code has become more convoluted than I had thought it would be, but here we are.

I will be on this for the next few hours, so if you see my post and have an epiphany or need more information let me know. I thought pulling back might offer some clue(s) as to where I am going wrong. Thanks as always for your help!

[hjozinovic]

Hi LosLobo!

Why not trying this:
A) Create query that would be source for cboRoom. This query should include [RoomDescription] as one of the fields.
Note: you can set column widths property of cboRoom so that fields you don't need have width 0 cm (zero)
B) For control RoomDescription on the form set Row source property to:
=cboRoom.Column (3)
That will work if [RoomDescription] is fourth row in the query created above
C) In the after update event of cboBuilding control put a code like

Me!cboRoom.Requery

This way cboRoom would have room description in the list, but it would not be visible.
After updating building number, cboRoom would be requeried showing only rooms from that building.
When you select one room number, control Room description will automatically show the description for that room.

[LosLobo]

Hi LosLobo!

Why not trying this:
A) Create query that would be source for cboRoom. This query should include [RoomDescription] as one of the fields.
Note: you can set column widths property of cboRoom so that fields you don't need have width 0 cm (zero)
B) For control RoomDescription on the form set Row source property to:
=cboRoom.Column (3)
That will work if [RoomDescription] is fourth row in the query created above
C) In the after update event of cboBuilding control put a code like

Me!cboRoom.Requery

This way cboRoom would have room description in the list, but it would not be visible.
After updating building number, cboRoom would be requeried showing only rooms from that building.
When you select one room number, control Room description will automatically show the description for that room.

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!