Clearing displayed value in a combo box

[jjmclell]

Hi there,

I have 3 combo boxes. The values you can select in cmb_2 are based on a query of what you select in cmb_1 and the values you can select in cmb_3 are based on a query of what you select in cmb_2. Now, what is supposed to happen on the form is the user first selects a value from cmb_1, then cmb_2, and then cmb_3 and it helps refine their search.

My problem occurs when a user selects a new value in cmb_1. Ideally I would like cmb_2 and cmb_3 to show nothing until they make a selection with those boxes, however they keep displaying the values the user had selected in the last record entry. I put a requery on cmb_2 to take effect AfterUpdate of cmb_1, but cmb_3 won't requery b/c cmb_2 is simply showing a null value and there's nothing for it to base it's query on (I think). Anyways, if you can follow all of this up until now, I'm looking for a way to requery cmb_3 so that it has a blank display when the user selects a new value in cmb_1.

Thanks,

jjmclell

 

[skyline666]

Hi jjmclell,

I had a similar problem to this and found a solution to it. My problem was I had a combo box, and when a user selected an item from the combo box, a list box was populated with values related to the combo box. Then when a user selected a value from the list box, a subform would appear with values then relating to that item in the list box. The problem I had was not that the subform wasnt refreshing, but that I had some text boxes in the main form that had values related to values in the subform, and when a user clicked on an item in the list box, if no values in the subform where there, it still showed values in the text boxes from the previous subform. I think this is similar to yours from what I read. Anyway enough of my old problem heres what I did to solve it:

First of all, I have the subform not visible until the user has selected a value from the combo box and list box. When the user hasnt selected a value from the combo box, a message is displayed (in a label) where the subform should be saying something like "Select a value from combo box". When the user has selected a value from the combo box, that message disappears and a new message appears saying something like "pick an item from list box".

The code I have in the combo box is:

Me.ProjectID.Requery
Me.Risk.Visible = False
Me.lblMasterProjectSelect.Visible = False
Me.lblSubProjectSelect.Visible = True

The first line refreshes the list box (called ProjectID) with the values for the selected value in the combo box (which it gets from a query like yours). The second line hides the subform (called Risk, this line is here incase a user has already looked at values from a different item in the list box, and so the subform will have already appeared). Third line hides the combo box select message. Fourth line shows the list box message.

The code for the list box is:

Me.lblSubProjectSelect.Visible = False
Me.Risk.Visible = True
Me.Controls("Risk").Form.Requery

First line hides the list box message. Second line displays the subform. Third line refreshes the subform (which also uses a query like yours).

On form open event i have (i guess you can work out what they do from stuff above):

Me.Risk.Visible = False
Me.lblMasterProjectSelect.Visible = True

Well I hope this long essay helps!! If you need any further help, ask and i'll try to help you.

Andrew

 

[jjmclell]

Hey thanks a lot. It seems in the end I am going to have to make the 3rd combo box invisible if I want to ensure there is no confusion and mismatched records. I was hoping to avoid a bunch of event coding but that's the way it goes I guess. Thanks once again.

jjmclell

 

[Remou]

It appears that these are unbound combos, so why not just set the third combo to null in the afterupdate event of the first combo?

Me.Combo3=Null

 

[skyline666]

Well a good thing about making it invisible is that the user can't actually choose anything at all (even if something was to appear for some reason), and that the messages also tell the user that he/she must choose the first combo box before the second.

If you want to not make them invisible, you could use the locked option, so Me.cmb2.locked = true or something (where cmb2 is your second combo box), and when they have clicked the first combo box, the second is unlocked. So in the form open event, have cmb1 locked = false and cmb2 and cmb3 locked = true. Then on the cmb1_click event, set cmb2 locked = false and cmb3 locked = true.

Then on cmb2, check to see if it is locked (e.g. if cmb2.locked = true then exit sub), and if it isnt then unlock cmb3. The check to see if it is locked or not is to make sure that the user doesnt click cmb2 first and have access to it.

On cmb3, you will need checks again, so if cmb3.locked = true then exit sub.

Hope this helps.

Andrew

 

[jjmclell]

The three combo boxes are bound to a table. Your idea does work though...but would it be a better idea to do this instead ?:

cmb_3.Value = ""

Or do I want it nulled? In my mind, if I null the field it'll be as though there's no entry there whatsoever. Maybe it doesn't matter either way? Any input? Thanks,

jjmclell

 

[jjmclell]

Thanks Andrew. I suppose I will likely have to lock and unlock boxes if I want the user to select them in the right order. Once again, many thanks.

jjmclell

 

[skyline666]

Sorry, i was moving desks at the office so havent had access to my pc, im not very happy either cos the setup i have now is way worse then what i have, gonna have to complain me thinks.

Anyway, to be honest your idea might work although I don't really know as im pretty new to vba but am picking up things as i go lol. The locking and unlocking I feel is better as it doesn't allow the user to select the combo box at all, regardless if it has no data in, as if you think about it, especially on the database im working on at the moment, if you have say a combo box that returns values into a different combo box, one of the options in the first may not necessarily return values in the second purely becuase theres nothing there to return. So personally having them locked/unlocked or visible/shown tells the user you can pick an option in there, especially if you add labels as I mentioned before telling the user what he/she needs to do in terms of which combo box to pick.

In fact, just reading your second to last post again, you have the same thoughts about null values actually suggesting theres nothing there at all to pick, whereas in fact it could be because the user hasn't picked the combo box before.

Happy to of helped.

Andrew