Full Version: Select 1 list item following AfterUpdate event
UtterAccess Forums > Microsoft® Access > Access Forms
mjennings
I have three controls on a form. The first control, cboClient, affects the RowSource for the 2nd control, cboDistrict, which in turn affects a RowSource for a 3rd control, lstFields. I have no problem with defining the RowSources for the controls, but after making a selection in the first control, cboClient, I want the rowsource for the second control, cboDistrict, to not only change (this part is working), but also to select the first item in the control so that the RowSource in the third control, lstFields, is updated and showing rows of data.
thought I could use Me.cboDistrict.Selected(0) = True, but this method is not recognized.
Without some kind of code to automatically update all rowsources, the user would have to actually select an item in the 2nd control, leaving the 3rd control blank until a valid choice is made in the 2nd control.
Confused yet? frown.gif
fkegley
This seemed to work for me.
Me.cboDistrict.SetFocus
Me.cboDistrict.ListIndex = 0
mjennings
Thanks - this seems to make sense, but I get an error message as follows:
un-time error '7777'
You've used the ListIndex property improperly.
fkegley
I got that error too, it went away when I put the SetFocus instruction ahead of it.
mjennings
Hmmm, I already had the SetFocus in my code. Did you make any other changes to avoid the error message?
fkegley
No, that is all that I did.
Here is the row source property:
SELECT tblCounties.CountyID, tblCounties.CountyName FROM tblCounties ORDER BY [CountyID];
Two columns, with the first hidden, the second showing, the first is bound column.
mjennings
My rowsource is very similar and the bound column is the first column. But I just can't get around the error message.
fkegley
That is strange, could it be a version thing? I am using 2003.
don't think sending you my example would help. I have basically told you what I did in gory detail. I can send it tho.
mjennings
Could be but I think its something else. I was able to get the ListIndex property to work as it did for you with another simpler example.
hould be simple, but apparently it doesn't like my code.
fkegley
Well, maybe it's some corruption. Copy the inside lines to the clipboard. Then delete all of the event procedure including the top and bottom lines.
lose the code window, delete the troublesome combo box as well. Put a new combo box on the form. Cancel the wizard. Get the properties of the combo box, event page, AfterUpdate row, dropdown, event procedure, ... button.
Paste in the code you earlier copied. You may need to change some of the control names.
mjennings
OK, so here is the funny twist:
can apply the code as you have suggested. When I select from my first combo, the AfterUpdate event kicks in and modifies the Select statement for the second combo. No problem. Then the SetFocus occurs and finally the ListIndex occurs resulting in the error message.
Typically, I debug to go view the code. But if I just end, the focus is in the right place and the selected index is correct! Any way to suppress this error message?
In essence, the code seems to work, but somewhere the debugger believes it is an improper use of the ListIndex property.
I'm very certain this is not related to a corrupted form. I am attempting this with many other forms and am receiving the identical message.
mjennings
I have found that the ListIndex has no problems when moving the focus to a list box. The problem seems to be with combo boxes.
Can you confirm in your testing that you used combo boxes and not list boxes?
It seems that I have success with list boxes using either the ListIndex property or the Selected property. Unfortunately, I want to use combo boxes. Selected is not an available method for combo boxes and as this thread implies, I have had little success using the ListIndex method.
mjennings
Still unable to figure out how to make this work. To reiterate, I have 2 combo boxes and a list box (multi extended). By selecting a value in cbo1, the AfterUpdate event updates a list of values that appears under cbo2. The intent is along with this AfterUpdate event associated to cbo1, I want the first item in cbo2 to be selected (in essence becoming the value in this combo box), which in turn will then update the list of values appearing in the list box.
I have tested under Access 2K and 2003 and believe ListIndex is not appropriate for a combo box; although it seems to work perfectly with a list box. (I think ListIndex is more appropriately used to determine the value stored in a list at a specified position, not necessarily to move the focus to that position).
How else might this be accomplished?
fkegley
I repeat, mine works just fine, I am using a text box in the exit event of the text box I am setting a combo box to the first value in its list. Here is the code:
Me.Combo0.SetFocus
Me.Combo0.ListIndex = 0
However, when I switch to doing the same code in the After Update event of a combo box it throws an error. It also puts the value in the combo box. Why would that make a difference, I wonder?
Never mind, I figured it out, I think, instead of using List Index property, use Default Value property, set it to the lowest value the bound column of the combo box can have.
THere is the code:
Me.Combo0.SetFocus
Me.Combo0.DefaultValue = 1
mjennings
This puts the value of 1 (which is not a value included in the combo list) in my combo field. Its not putting the first combo list value in the field, its literally putting the value 1 in the field. I had considered this avenue, but since the list of values appearing in my combo box will be different based on what is selected in cbo1, there is no practical way to assign a value for the combo box to default to.
How do you store a value returned from an sql statement? I could use the same select statement, only returning the first value from a single field, store this as a string, then plug this in to the default value property (or just value). This approach might work.
fkegley
It seems that you should be able to do that. You would need DMin to find the lowest value in the proposed row source for combo box 2. Then you would put that value in the default property of the combo box.

Wouldn't that work?
I just tried it:

Me.Combo0.DefaultValue = DMin("[CountyID]", "[tblCounties]")

and it worked just fine.

You would find it, set the row source, set the default property, in that order.
Edited by: fkegley on Wed May 3 10:34:31 EDT 2006.
mjennings
OK, using DMin did the trick.
had a little difficulty building the DMin statement which required building a separate query, but I finally got it to work.
Then I used SetFocus to go the next combo and Value to display as follows:
Me.cboDistrict.SetFocus
Me.cboDistrict.Value = DMin("[District]", "qryLeaseDistricts", "[ClientName]='" & strClientName & "'")
Also, because the combo box that would serve as criteria for the DMin was bound by the numeric ID, I had to include the following to pick up the associated text description:
strClientName = Me.cboClient.Column(1)
Ocouldn't get the DMin to work, due to required quotations, etc. using the bound ID. I'm sure there is a way to do this, but it took less time to just point to the text value associated to the bound ID.
Mission Accomplished!
Humby
Try the ItemData method. For example, Me.cboDistrict.Value = Me.cboDistrict.ItemData(0) will select the first value in the cboDistrict combo box. I use this in a DB of my own, where the user chooses a month from one combo box, and the second combo box is then auto-populated with a list of Saturdays in that month, and the first Saturday is chosen for them by default.

EDIT: Made the example a little clearer.
Edited by: Humby on Wed May 3 12:12:00 EDT 2006.
datAdrenaline
To set a combo/listbox to the FIRST item in the list use this:

Me.cboDistrict = Me.cboDistrict.Column(0,0)

The expression above assumes that the BOUND column is the first column (index = 0). So, generically you could say this:

To set a combobox/listbox to a certain value in the list use this:

Me.cboDistrict = Me.cboDistrict.Column(<index of bound column>,<index of row you want>)

The method that fkegley proposed is actually in the MSAccess help, however, the Help file also indicates that .ListIndex is read only... so it is my suspicion that the help file is correct in saying that .ListIndex is READ-ONLY and incorrect in stating that you can set it!...>>>>> EDITS ADDED >>>> Using the .SetFocus before the setting of .ListIndex does work as Frank described! ... so I guess the .ListIndex property is read-only if the control does not have the focus, but can be changed if it does! ... to me ... that is kinda of odd! ... I would suggest sticking with .ItemData or .Column(0,0) <<<< END EDITS <<<<

The method I proposed is more efficient than using the DMin() method you are currently using ... Another reason I prefer the use of the .Column() method is that it always picks the FIRST item in the list, therefore it gives you the flexibility to modify the rowsource as you see fit, with out having to cascade the rowsource change through out your code.
------
HAs a side note... the ListIndex property of a combo/dropdownlist control on a command bar acts differently than a combo on a form.

HTH ...

EDITS ADDED >>>> The .ItemData method works well too! ... <posted while I was typing> .... Also, I wanted to note, that the .SetFocus is NOT neccessary to set the value of the control.

Edited by: datAdrenaline on Wed May 3 12:31:15 EDT 2006.
Edited by: datAdrenaline on Wed May 3 12:52:09 EDT 2006.
fkegley
I thought it was kind of funny that when I exited a TEXT BOX, it would allow me to set the ListIndex property of the combo box no problem, no error message, it just did as I asked it to.
When when I did the same code in the AfterUpdate event of a COMBO BOX, as OP wants to do, it put up error message.
mjennings
Perfect!
hey all work!!
Oliked using something other than DMin since this function is dependent on a query that is stored in the db Window.
Thanks for all of the useful suggestions. frown.gif
fkegley
You're welcome. The committee was glad to help.
mjennings
Now I'm attempting to cause the first record of a list control that exists on a subform to be selected with the Form_Current event. I can't seem to figure out the syntax needed to point to a control on a subform. For example:
orm_Current()
Forms!subform = Forms!subform!lstField.Column (0,0)
I've also tried variations using Me.subform, etc. or subform!Form!lstField with no luck.
Ousually get the message that it can't find the Form subform. So I'm certain the syntax is just wrong.
tinygiant
Maybe I missed it in all the posts, but you can also use the ItemData property of the ListBox. For example, Me.lstMyList = Me.lstMyList.ItemData(0). You don't have to set focus to the control to do this and it will update the listbox (but I don't think it fires the AfterUpdate Event).

Syntax to reference a subform's control should look like:
Forms!frmMyMainForm!ctlSubFormControlName.Form.lstMyList = Forms!frmMyMainForm!ctlSubFormControlName.Form.lstMyList.ItemData(0)

Alternately:
Me.ctlSubFormControlName.Form.lstMyList = Me.ctlSubFormControlName.Form.lstMyList.ItemData(0)

ctlSubFormControlName is the actual name of the subform control, not the name of the subform's Source Object.
mjennings
Well, I finally got this to work. Thanks to all for their input.
After struggling to get the first record in my list control to be selected, I finally resorted to building a very simplified version of my application. I got the selection to work in the simplified version, but not the actual version. Then I did some comparisons. When I compared the list control on my simplified version/form vs. the list control on my actual version/form, the difference was that in the actual list control I had ColumnHeads = True (this is rare for me to do, but needed in this case). As a result, I changed my ItemData(0) to ItemData(1) and finally had success.
It didn't occur to me that using the ColumnHeads would result in so much confusion in getting the desired results.
Live and learn . . . . . . .
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.