 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / January 2008Self-Made Combo Boxes not working correctly...
Okay all.. Here's my deal... I have tried and tried and tried to get these to work... I'm going to post what I have done, and what I am trying to do, and see if someone can help me figure crap out... tblBuilding - PK is BuildingIDNum - Autonumber tblBuildingZones - FK is BuildingIDNum via SELECT DISTINCTROW Building.BuildingIDNum, Building.Name FROM Building ORDER BY Building.BuildingIDNum; cboBuilding - This has Building 1, Building 2, etc. The field displaying this info is "Name" in tblBuilding. cboBuildingZones - This has different "zones" for Building 1, Building 2, etc., that depending on which building is chose in cboBuilding, this will populate with the "zone" - First Floor, Second Floor, Mezzanine, etc. The field displaying this info is "InBuildingLocation" in tblBuildingZones. However, the field I need to write in the underlying form table is "DN.911Zone". I wrote the following code in AfterUpdate of the Building ID, and I'm getting a highlighted yellow on "End Sub" as soon as I try and populate my cboBuildingZones box. Private Sub cboBuilding_AfterUpdate() With Me![cboZoneID] If IsNull(Me!cboBuilding) Then .RowSource = "" Else .RowSource = "SELECT [InBuildingLocation] " & _ "FROM tblBuildingZones " & _ "WHERE [BuildingIDNum]=" & Me!cboBuilding End If Call .Requery End With End Sub I hope someone can help me out either by fixing my code, or taking the 2 minutes to write what probably should be a really stupid quick entry that I just can't get my hands around! Thanx in advance! Goobz, I'll use cboB and cboBZ as the two combos. Rather than code to change the Rowsource of cboBZ, why not just filter for cboB in the query behind cboBZ. Include the BuildingIDNo in the cboBZ query, and give it a criteria of... =Forms!frmYourFormName!cboB On the AfterUpdate of cboB, Requery cboBZ to always keep both cbos in synch.  Signaturehth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." > Okay all.. Here's my deal... I have tried and tried and tried to get > these to work... I'm going to post what I have done, and what I am [quoted text clipped - 37 lines] > > Thanx in advance! > Goobz, > I'll use cboB and cboBZ as the two combos. [quoted text clipped - 55 lines] > > - Show quoted text - So then you are saying just basically nuke the whole routine that I posted above, and do "=Forms!DN Update!cboBuildings" as criteria for the Combo box query? >So then you are saying just basically nuke the whole routine that I >posted above, and do "=Forms!DN Update!cboBuildings" as criteria for >the Combo box query? Yes... That's how "cascading", or "drill down" combo boxes are set up. The next cbo in the sequence uses the previous cbo's value as a criteria for it's rowsource query. I have a sample A97 and A2003 file on my web site (below) that demostrates "Synched Combos" Give it a try, and get back as to how you made out... Signaturehth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." On Jan 21, 6:03 am, "Al Campagna" <newsgro...@comcast.net> wrote: > Goobz, > I'll use cboB and cboBZ as the two combos. [quoted text clipped - 59 lines] > > - Show quoted text - So then you are saying just basically nuke the whole routine that I posted above, and do "=Forms!DN Update!cboBuildings" as criteria for the Combo box query? > Yes... > That's how "cascading", or "drill down" combo boxes are set up. The [quoted text clipped - 8 lines] > Microsoft Access MVP > http://home.comcast.net/~cccsolutions/index.html Al... I am still having problems. I just cannot get the 2nd combo box to work to save my life! Now, I am getting an error that says something to the effect of "Records in table 'DN' would hav e no record on the 'one' side. When I click 'Debug', it highlights yellow on the "= Null" VB Code. If I comment out the "VB Code", then it doesn't give me the error anymore, but I also don't see the list. If you, or someone else has no problem with it, I wouldn't mind sending it to you to view and see what the heck is going on... Goobz, Please don't <snip> the previous posts from the thread. We need to see the sequence of events during the problem determination process, rather than try to piece it together over several individual posts. Can't help if I can't see the code. Whenever you have code problems, cut & paste "exactly" the procedure code you have, and describe the elements of that code (fields, field types, intended purpose, ..etc). But.... what code are you referring to? My last suggestion was to create a query that shows the fields/columns you need for cboB (including the BuilingID field), and to set the criteria for BuildingID to... =Forms!frmYourFormName!cboB How does "Null" (probably should be IsNull) come into play with that? Signaturehth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." On Jan 21, 4:40 pm, "Al Campagna" <newsgro...@comcast.net> wrote: > Yes... > That's how "cascading", or "drill down" combo boxes are set up. The [quoted text clipped - 8 lines] > Microsoft Access MVP > http://home.comcast.net/~cccsolutions/index.html Al... I am still having problems. I just cannot get the 2nd combo box to work to save my life! Now, I am getting an error that says something to the effect of "Records in table 'DN' would hav e no record on the 'one' side. When I click 'Debug', it highlights yellow on the "= Null" VB Code. If I comment out the "VB Code", then it doesn't give me the error anymore, but I also don't see the list. If you, or someone else has no problem with it, I wouldn't mind sending it to you to view and see what the heck is going on... > Goobz, > Please don't <snip> the previous posts from the thread. We need to see [quoted text clipped - 47 lines] > If you, or someone else has no problem with it, I wouldn't mind > sending it to you to view and see what the heck is going on... Okay... It is just still not working... Here's the info I have for both boxes... Combo Box One Name: cboBuilding Control Source: BuildingNumber Row Source Type: Query Row Source: SELECT [911Zones].BuildingNumber FROM 911Zones GROUP BY [911Zones].BuildingNumber ORDER BY [911Zones].BuildingNumber; Column Count: 1 Bound Column: 1 IME Sentence: Phrase Predict Combo Box Two Name: cboZoneID Control Source: 911Zone Row Source Type: Query Row Source: SELECT [911Zones].BuildingNumber, [911Zones].InBuildingLocation FROM 911Zones WHERE ((([911Zones].InBuildingLocation)=[Forms]![DN Update]![cboBuilding])) ORDER BY [911Zones].InBuildingLocation; Column Count: 1 Bound Column: 1 IME Sentence Mode: Phrase Predict. I am running out of hair to pull out! :) On Jan 22, 5:31 pm, "Al Campagna" <newsgro...@comcast.net> wrote: > Goobz, > Please don't <snip> the previous posts from the thread. We need to see [quoted text clipped - 51 lines] > If you, or someone else has no problem with it, I wouldn't mind > sending it to you to view and see what the heck is going on... Okay... It is just still not working... Here's the info I have for both boxes... Combo Box One Name: cboBuilding Control Source: BuildingNumber Row Source Type: Query Row Source: SELECT [911Zones].BuildingNumber FROM 911Zones GROUP BY [911Zones].BuildingNumber ORDER BY [911Zones].BuildingNumber; Column Count: 1 Bound Column: 1 IME Sentence: Phrase Predict Combo Box Two Name: cboZoneID Control Source: 911Zone Row Source Type: Query Row Source: SELECT [911Zones].BuildingNumber, [911Zones].InBuildingLocation FROM 911Zones WHERE ((([911Zones].BuildingNumber)=[Forms]![DN Update]![cboBuilding])) ORDER BY [911Zones].InBuildingLocation; Column Count: 1 Bound Column: 1 IME Sentence Mode: Phrase Predict. I am running out of hair to pull out! :) -------------------- Goobz, That's good info above... we should be able to work it out. If BuildingNumber is the value in cboBuilding, then the criteria... =Forms![DN Update]!cboBuilding needs to be applied to the BuildingNumber field in the cboZoneID query. Your query above applies the criteria to InBuildingLocation! >Row Source: SELECT [911Zones].BuildingNumber, >[911Zones].InBuildingLocation FROM 911Zones WHERE >((([911Zones].BuildingNumber)=[Forms]![DN Update]![cboBuilding])) >ORDER BY [911Zones].InBuildingLocation; AND... With InBuildingLocation and BuildingNumber as two columns in cboZoneID, the column count needs to be 2. Your ColumnWidths can be .5"; 0"...which would hide the 2nd column BuildingNumber, but still allow it to filter the results. or ColumnWidths .5"; .5" (size to suit your fields) This setting would show both colums on the dropdown. Signaturehth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." > On Jan 22, 5:31 pm, "Al Campagna" <newsgro...@comcast.net> wrote: > [quoted text clipped - 114 lines] > > - Show quoted text - Okay.. I have made the changes as you suggest, and still am getting nothing at all, whatsoever... In fact, I've made some other changes here, to hopefully make my life easier, but got no changes in results at all, whatsoever... Combo Box One Name: cboBuilding Control Source: None Row Source Type: Query Row Source: SELECT [911Zones].BuildingNumber FROM 911Zones GROUP BY [911Zones].BuildingNumber ORDER BY [911Zones].BuildingNumber; Column Count: 1 Bound Column: 1 IME Sentence Mode: Phrase Predict Combo Box Two Name: cboZoneID Control Source: 911Zone Row Source Type: Query Row Source: SELECT [911Zones].InBuildingLocation, [911Zones].ToneCommanderInfo, [911Zones].BuildingNumber FROM 911Zones WHERE ((([911Zones].InBuildingLocation)=[Forms]![DN Update]! [cboBuilding])) ORDER BY [911Zones].BuildingNumber; Column Count: 3 Column Widths: 5";5";0" (this will be changed once working) IME Sentence Mode: Phrase Predict I added the 3rd column under cboZoneID, because that extra column is the actual data that I need written to the record's field in the table. The other 2 fields are easy to read data, instead of the actually data passed on which no one would be able to interpret. I also removed the Control Source from the 1st combo box, and am just letting the query do the work. On Jan 23, 5:17 pm, "Al Campagna" <newsgro...@comcast.net> wrote: > "Goobz" <charlestwat...@gmail.com> wrote in message > [quoted text clipped - 122 lines] > > - Show quoted text - Okay.. I have made the changes as you suggest, and still am getting nothing at all, whatsoever... In fact, I've made some other changes here, to hopefully make my life easier, but got no changes in results at all, whatsoever... Combo Box One Name: cboBuilding Control Source: None Row Source Type: Query Row Source: SELECT [911Zones].BuildingNumber FROM 911Zones GROUP BY [911Zones].BuildingNumber ORDER BY [911Zones].BuildingNumber; Column Count: 1 Bound Column: 1 IME Sentence Mode: Phrase Predict Combo Box Two Name: cboZoneID Control Source: 911Zone Row Source Type: Query Row Source: SELECT [911Zones].InBuildingLocation, [911Zones].ToneCommanderInfo, [911Zones].BuildingNumber FROM 911Zones WHERE ((([911Zones].InBuildingLocation)=[Forms]![DN Update]! [cboBuilding])) ORDER BY [911Zones].BuildingNumber; Column Count: 3 Column Widths: 5";5";0" (this will be changed once working) IME Sentence Mode: Phrase Predict I added the 3rd column under cboZoneID, because that extra column is the actual data that I need written to the record's field in the table. The other 2 fields are easy to read data, instead of the actually data passed on which no one would be able to interpret. I also removed the Control Source from the 1st combo box, and am just letting the query do the work. -------------------------------- No, you didn't make the changes as I suggested. You made cboBuilding unbound, you added another field to cbo911Zone, and you STILL have the BuildingNumber value selected in cboBuilding as a criteria against InBuildingLocation in cbo911Zone! I'll say again... the BuildingNumber selected in cbo911Zone MUST be use as the criteria against the BuildingNumber column in cbo911Zone. Signaturehth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." > On Jan 23, 5:17 pm, "Al Campagna" <newsgro...@comcast.net> wrote: > [quoted text clipped - 174 lines] > > - Show quoted text - Okay... My SQL now for the 2nd box is: SELECT [911Zones].InBuildingLocation FROM 911Zones WHERE ((([911Zones].BuildingNumber)=[Forms]![DN Update]![cboBuilding])) ORDER BY [911Zones].BuildingNumber; This now populates the 2nd box with what building I chose in the 1st box. HOWEVER, It doesn't change, and it won't let me select it.. It just beeps... What I mean by it won't change, is the fact that If start out selecting Building 1, then I change my mind and select Building 21, it still shows Building 1 selections in the drop down. When I click on it, all it does is beep at me. It won't populate anything, and the selections don't disappear... I'm getting closer! :) Thanx for the help so far! On Jan 24, 9:31 am, "Al Campagna" <newsgro...@comcast.net> wrote: > "Goobz" <charlestwat...@gmail.com> wrote in message > [quoted text clipped - 179 lines] > > - Show quoted text - Okay... My SQL now for the 2nd box is: SELECT [911Zones].InBuildingLocation FROM 911Zones WHERE ((([911Zones].BuildingNumber)=[Forms]![DN Update]![cboBuilding])) ORDER BY [911Zones].BuildingNumber; This now populates the 2nd box with what building I chose in the 1st box. HOWEVER, It doesn't change, and it won't let me select it.. It just beeps... What I mean by it won't change, is the fact that If start out selecting Building 1, then I change my mind and select Building 21, it still shows Building 1 selections in the drop down. When I click on it, all it does is beep at me. It won't populate anything, and the selections don't disappear... I'm getting closer! :) Thanx for the help so far! Goobz, Something is not as we assume... What version are you running? I have 97 and 2003 installed at this time. Use my website below to send me the file/s, as long as it's not too large. Zip the file, and put "newsgroup" in the Subject line. What version are you running? In the body explain exactly what form has the problem. Signaturehth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." > On Jan 24, 9:31 am, "Al Campagna" <newsgro...@comcast.net> wrote: > [quoted text clipped - 215 lines] > > - Show quoted text - Al... I am out of the office until Monday morning, but will get them sent to you then. I am running Office 2003 on the PC at work, with Windows XP. > > "Goobz" <charlestwat...@gmail.com> wrote in message > [quoted text clipped - 227 lines] > > - Show quoted text - The boxes now work perfectly fine! Thank you very much, sir! > On Jan 24, 9:31 am, "Al Campagna" <newsgro...@comcast.net> wrote: > [quoted text clipped - 215 lines] > > - Show quoted text - You have mail, sir! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.