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_AfterUpdate()
'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!Building) 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!
18  2087 
Assuming you wish to display the Room Description in a Text Box named txtRoomDescription: - Me![txtRoomDescription] = DLookup("[RoomDescription]", "tblRoom", "[RoomNumber] = " & Me![cboRoom])
Assuming you wish to display the Room Description in a Text Box named txtRoomDescription:
- 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 "RoomDescription" 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!
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!
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. :-)
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:- Your Combo Box names are cboBuilding and cboRoom.
- The Bound Column for cboBuilding is BuildingID.
- cboRoom is a Single Column Combo Box with simply the Room Number listed.
- You have a Text Box on the Form named txtRoomDescription.
- 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
- 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).
- Get back to me one way or another and we'll get this issue resolved.
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!
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!Building" 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!
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.
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!Building" 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] & "'"))
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.
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.
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.BuildingID, tblBuildings.Building
FROM tblBuildings
ORDER BY tblBuildings.Building;
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!
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
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.
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
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!
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_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], [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.
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.
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.Column(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?
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.Column(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!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: JMosey |
last post by:
Not sure if this has been covered ( a google search came up pretty
bare).
I have a site that:
- has multi-level cascading menus
- floats center of the browser window
- Will have fairly heavy...
|
by: Rabbit |
last post by:
Cascading Combo/List Boxes
This tutorial is to guide you in the creation of Cascading combo/list boxes. That is when you have multiple combo/list boxes where the selection of an option in one...
|
by: KMEscherich |
last post by:
Hi there, am wondering if there is a way to have a primary key control on a field and have this drive the cascading list boxes.
I would like to have someone enter an ID(Primary Key) in a textbox...
|
by: kcdoell |
last post by:
Hello:
I have been struggling with building a cascading list on a form that I created. My problem is that I am getting a "Datatype Mismatch in criteria expression" error that I can not seem to...
|
by: kcdoell |
last post by:
I have three tables:
One for the Division location:
tblDivision
DivisionID = Autonumber
DivisionName = Text
One for the Working Region:
tblWrkRegion
|
by: klarae99 |
last post by:
Hello,
I am working on an Access 2003 Database. The tables that pertain to this issue are tblOrg, tblState, tblCity, and tblZip. I have posted the table structure with only the pertinant fields...
|
by: kcdoell |
last post by:
Good Morning:
I have a form where I am trying to create cascading combo boxes. I have done this before but I am getting the following error that is throwing me off:
Procedure declaration...
|
by: kcdoell |
last post by:
I have 5 cascading combo boxes on a form. Below is a sample of my vb in the first combo box:
Private Sub CboDivision_AfterUpdate()
'When the Division is selected, the appropriate Segment...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
