473,322 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Help with combo box determining selections of second combo box

Hello all,

I have a subform within a form and I have two combo boxes. the first combo is Facility and the second is Unit. I want the selections for Unit to be determined by the selection in Facility. I have it setup with the folowing Row Source info:

SELECT [tblUnitFloor].Unit FROM tblUnitFloor WHERE ((([tblUnitFloor].Facility)=Forms!frmMainForm!sfrMainSub!Facility)) ;

tblUnitFloor is a table with all the Units and the Facilities listed.

My problem is this, when I select a Facility for the first record I see all the proper Units for the Facility. But in the next record, if I select a different Facility, I still see all the Units for the first Facility. Anyone have any thoughts?
Sep 28 '10 #1
16 1510
MMcCarthy
14,534 Expert Mod 8TB
Check out this insight on Cascading Combo/List Boxes. I think it should have all the information you need.
Sep 29 '10 #2
Thanks! I will give it a try!!!
Sep 29 '10 #3
Well, it almost works...

I make a selection in the first combo box, but when I clink on the drop down for the second box, a window pops up and I have to manually type the option I want from the first box. After that, I do see the correct options for the second box.

Here is the code I used:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Facility_AfterUpdate()
  2.  
  3.     With Me![UnitFloor]
  4.         If IsNull(Me!Facility) Then
  5.             .RowSource = " "
  6.         Else
  7.             .RowSource = "SELECT [Unit] " & _
  8.                          "FROM tblUnitFloor " & _
  9.                          "WHERE [Fac] = " & Me!Facility
  10.         End If
  11.         Call .Requery
  12.     End With
  13.  
  14. End Sub
  15.  
Am I doing something wrong here?
Oct 4 '10 #4
MMcCarthy
14,534 Expert Mod 8TB
Assuming Fac is a text field then you need the following:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Facility_AfterUpdate()
  3.  
  4.     With Me![UnitFloor]
  5.         If IsNull(Me!Facility) Then
  6.             .RowSource = " "
  7.         Else
  8.             .RowSource = "SELECT [Unit] " & _
  9.                          "FROM tblUnitFloor " & _
  10.                          "WHERE [Fac]='" & Me!Facility & "'"
  11.         End If
  12.         Call .Requery
  13.     End With
  14.  
  15. End Sub
  16.  
Oct 4 '10 #5
That took care of the popup window, but I do not have any options to select from in the second combo box now.
Oct 4 '10 #6
MMcCarthy
14,534 Expert Mod 8TB
what is the rowsource of Facility combobox?

Also check column count and column widths properties of the unitfloor combobox.
Oct 4 '10 #7
It is a Value List with two facilites.
Oct 4 '10 #8
Does it matter if both combo boxes are on a subform?
Oct 4 '10 #9
MMcCarthy
14,534 Expert Mod 8TB
And long as it's the same values in Fac as you typed in for the two facilities, check the spelling.

The fact that they are on a subform will work as long as both boxes are on the same subform.
Oct 4 '10 #10
Spelling is correct. (I've checked that 3 times now and I even copy/pasted them from the table to the form to be sure.)
Oct 4 '10 #11
MMcCarthy
14,534 Expert Mod 8TB
OK take a copy of the form and tblUnitFloor and put it in a database file then zip it up and attach it here. I'll have to see it to see what is going on.
Oct 4 '10 #12
Here are teh table an form. I included the main form and subform just in case...

Thank you very much for helping me with this!!
Attached Files
File Type: zip Help.zip (18.9 KB, 62 views)
Oct 4 '10 #13
MMcCarthy
14,534 Expert Mod 8TB
OK there were two issues. Firstly you spelt Memorial with 2 l's in the value list.

Secondly you have added a space after the opening single quote before Me!Facility.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Facility_AfterUpdate()
  2.  
  3.     With Me![UnitFloor]
  4.         If IsNull(Me!Facility) Then
  5.             .RowSource = " "
  6.         Else
  7.             .RowSource = "SELECT [Unit] " & _
  8.                          "FROM tblUnitFloor " & _
  9.                          "WHERE [Fac]='" & Me!Facility & "'"
  10.         End If
  11.         Call .Requery
  12.     End With
  13.  
  14. End Sub
  15.  
Oct 4 '10 #14
It works!!! Thanks for checking that for me! I couldn't tell that the single quote did not have a space after it in your earlier example... As for the spelling error, I think I did that just before I sent you the example. One last try to copy/past the values and I missed highlighting the L.

Anyway, thanks again for your help!!!
Oct 4 '10 #15
The solution above does work, but I have noticed if I leave the first combo box blank on a new record then the second combo box displays the options with the selections from the last time combo box 1 was used. Shouldn't the rowsource be blank if no selection is made? (line 3 in the code below)

With Me![UnitFloor]
If IsNull(Me!Facility) Then
.RowSource = " "
Else
.RowSource = "SELECT [Unit] " & _
"FROM tblUnitFloor " & _
"WHERE [Fac] ='" & Me!Facility & "'"
End If
End With
Oct 7 '10 #16
MMcCarthy
14,534 Expert Mod 8TB
you're missing the requery. Either .requery if within the With statement or if after it then

Expand|Select|Wrap|Line Numbers
  1. Me!UnitFloor.Requery
Oct 7 '10 #17

Sign in to post your reply or Sign up for a free account.

Similar topics

15
by: sara | last post by:
Hi I'm pretty new to Access here (using Access 2000), and appreciate the help and instruction. I gave myself 2.5 hours to research online and help and try to get this one, and I am not getting...
0
by: Budinski | last post by:
I have a subform that has a datasheet in it. In the datasheet I have multiple records. Each record has many textboxes and combo boxes. I have a particular combobox with 2 columns, Column 1 has the...
2
by: SM | last post by:
Hello, I have an HTML file that contains a form with 2 selection list (combo box). The first combobox 'onchange' function, addColor(),adds a new color to the second combobox. I have half of the...
2
by: Robertjb1 | last post by:
First Post - New member - I've searched the discussions available to no avail. I have, however, learned a few things in doing so. (Great Site -- Thanks) My issue is as follows: Access 2003/WinXP...
2
by: SHAWTY721 | last post by:
I have a form that contains two combo boxes that are related to each other. I need to find a way to populate my text box based on the criteria of the two combo boxes so the appropriate number...
3
by: lrw0831 | last post by:
I have a table named AssignedTo. In this table are the fields: AssignedTo_ID, Department, Employee. On my form, I need to create 2 combo boxes. The first one will select the department. Based...
30
by: vanlanjl | last post by:
Question: How do I create a Report based off the values/selections of mutliple combo boxes in a form? I have tried this several times with several failures and have used multiple codes to try...
3
by: kurai hikari | last post by:
itis like menus and sub menus . for example i have acombo box with 5 selections in it. and each selection has it's own 3 sub selections . now i want the sub selection appear when i click on the...
1
UTuser
by: UTuser | last post by:
I'm not having much luck looking for answers in the archives for what it is I want to set up in my Access DB. If I could get some direction as to the function I should look at, this will be a big...
9
by: bdominiq | last post by:
I have three diferent combo boxs I want to sum the second column of each combo box after update. I tried to sum the selection on a different text box, but it row them up on the text box. How can you...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.