Combobox defaulting to first item after selection

[Markb384]

Hi guys, noob to this forum and I have limited Access experience, although this is changing rapidly

In brief, I have a form which appears on loadup which holds a combobox and some textboxes. The combobox is populated with 6 fields from a table, with 5 of the fields set to 0cm in width in the properties. Once an item is selected, the other columns are used to populate textboxes in the form using vba. However, when I select a value from the combobox, the textboxes are populated correctly but the combobox resets back to item one.

In more detail...
I have a form with a combobox called combo_equipment_ID which is populated with the following SQL code to retrieve values from 6 fields within a table called Equipment Information.

SELECT Equipment_Information.Equipment_Code, Equipment_Information.Equipment_ID, Equipment_Information.Location, Equipment_Information.Hospital, Equipment_Information.Manufacturer, Equipment_Information.ModelFROM Equipment_Information
ORDER BY Equipment_Information.Equipment_ID;

The combobox has the columns sized such that 5 of the fields are 0cm in width, with only the 2nd column (the equipment ID) visible at 5cm in width.

I have some VBA code to run after a selection is made on the combobox to set the values in the other columns as the values of some textboxes on the form, as shown below. For information, the Form_Load is just some commands to reset the form, but shouldn't actually run when a selection is made.

Private Sub combo_equipment_ID_afterupdate()'sub to run after the equipment ID combobox has been updated and an item selected


    'if the combobox is empty, reset the buttons, otherwise set the details from the database
    If IsNull(combo_Equipment_ID) Then
        'all the code needed to reset the form is in the load code
        Call Form_Load
    Else
        'after selection from the dropdown menu, set the captions so that the user knows which equipment they have selected
        Me.textbox_equipment_man = CStr(Nz([Forms]![Welcome]![combo_Equipment_ID].Column(4)))
        Me.textbox_equipment_model = CStr(Nz([Forms]![Welcome]![combo_Equipment_ID].Column(5)))
        Me.textbox_department = CStr(Nz([Forms]![Welcome]![combo_Equipment_ID].Column(2)))
        Me.textbox_hospital = CStr(Nz([Forms]![Welcome]![combo_Equipment_ID].Column(3)))
    
        'reenable buttons
        Me.button_Add_Survey.Enabled = True
        Me.button_Edit_Equipment.Enabled = True
        Me.button_View_Results.Enabled = True
        Me.button_Add_Equipment.Enabled = False
    End If
    
End Sub

Everything works fine, with the correct fields appearing in the combobox and the textboxes populated correctly. However, once a selection is made, the combobox resets and shows the first value in the list. This isn't only confusing for the person using the form, it is also a problem when that combobox value is referenced by another.

Does anyone have any ideas on what might be happening or where to start looking for a solution? I have been through the properties of the combobox and I can't find anything which suggests a possible solution.

Thanks in advance

[rpeare]

First you don't need to mess with the width, you can design the form so that everything is the size it would be under normal circumstances and just use the .visible property to show it or not show it

is your combo_Equipment_ID a bound or unbound control?

what's happening in your call Form_Load function? I suspect in there your fields are being 'reset'

Have you put in debug.print statements to find out where your code is going?

I'm guessing you're getting caught in the afterupdate trying to process when you don't want it to (in other words it might be processing after every character you type which you don't want)

Try using the same code in the ON EXIT event rather than the AFTER UPDATE event and see if it changes anything.

[Markb384]

Hi rpeare

Thank you for your reply. I seem to have managed to solve it by changing the "bound column" within the properties of the combobox to any number other than 1. I don't actually understand why this has fixed it though to be honest.

In regards to your questions, I'm not sure where the .visible property is within the columns of a combobox. The width method was used in a guide I saw somewhere on the Internet, which is how I figured out how to retrieve the information in one swoop rather than retrieving one value using the SQL and then using DLookup within VBA.

I believe the combobox itself is unbound, as this is what is says when I am in design mode. The fact that there is a bound column in the properties makes me dubious of this belief though.

The form_load code just resets the values of the textboxes to "" as well as enabling/disabling some buttons that I don't want the user pressing without an entry within the combobox. The code itself is...

Private Sub Form_Load()'sub to run when the form is loaded


    'disable buttons which can't be used until a number is selected
    Me.button_Add_Survey.Enabled = False
    Me.button_Edit_Equipment.Enabled = False
    Me.button_View_Results.Enabled = False
    
    'enable add equipment button
    Me.button_Add_Equipment.Enabled = True
    
    'ensure that the textboxes are blank, just in case
    Me.textbox_equipment_man = ""
    Me.textbox_equipment_model = ""
    Me.textbox_department = ""
    Me.textbox_hospital = ""
    
End Sub

The only updating that is done is the selection of an entry, as the user cannot enter their own numbers within the combobox. I tried "on exit" in a previous database, and I didn't quite get the result I wanted. If I remember correctly, the user needed to select an entry and then select away from the combobox to exit before the code would run whereas "on update" just runs when a selection is made. As this form is very much point-and-click, I think it works a little better.

I haven't used the debug.print statement before, I wasn't even aware it existed

Thank you

[rpeare]

the 'bound' column of a list box or combo box is just the 'default' reference when you refer to that object.

So for instance if your combo box has two columns and your 'bound' column is column 1 if you refer to

me.combobox1

it will retrieve the value in the first column of combobox 1 and the only way to change that is to use a column designation like:

me.combobox1.column(1)

just remember that combo boxes and list boxes start at column 0, so when you say your BOUND column is column 1 it would actually be like saying

me.combobox1.column(0)

If your textboxes are unbound controls you can reset them with

me.textbox_equipment_man = null

if they're bound you shouldn't be altering them with code at all you should be doing a

me.requery after a new item is selected from your combo box

[Missinglinq]

...First you don't need to mess with the width, you can design the form so that everything is the size it would be under normal circumstances and just use the .visible property to show it or not show it...

I think you misread the OP; he's setting the widths of Columns in the Combobox to 0cm:

...The combobox is populated with 6 fields from a table, with 5 of the fields set to 0cm in width in the properties...

Linq ;0)>

[Markb384]

I think you misread the OP; he's setting the widths of Columns in the Combobox to 0cm:
Linq ;0)>

Ah that makes sense as to why I couldn't find a visible property

the 'bound' column of a list box or combo box is just the 'default' reference when you refer to that object.

So for instance if your combo box has two columns and your 'bound' column is column 1 if you refer to

me.combobox1

it will retrieve the value in the first column of combobox 1 and the only way to change that is to use a column designation like:

me.combobox1.column(1)

just remember that combo boxes and list boxes start at column 0, so when you say your BOUND column is column 1 it would actually be like saying

me.combobox1.column(0)

That makes sense to me, particularly after I did a little more reading about it last night.

I'm still not clear how setting the bound column to 1 (which would actually be the 2nd column in the combobox as the first column has no width, if my interpretation of the Microsoft explanation is right) resets the data in the combobox back to the top value after the vba code is ran. It's no real biggy if I never get to the bottom of it, as it works now. It's more for interests sake that I am curious now

[rpeare]

no you're not getting it right. the numbering system of the list box columns and the numbering system for BOUND COLUMN are NOT the same.

the BOUND COLUMN is what you or I would view as the first column (column 1), so the BOUND COLUMN property does not start at 0

a list box or combo box, when you're referencing a specific column DOES start at 0

[Markb384]

no you're not getting it right. the numbering system of the list box columns and the numbering system for BOUND COLUMN are NOT the same.

the BOUND COLUMN is what you or I would view as the first column (column 1), so the BOUND COLUMN property does not start at 0

a list box or combo box, when you're referencing a specific column DOES start at 0

No, I do understand it. The bound column starts with a 1-based numbering system whereas the column() property starts with a 0-based numbering system. That is what I described happening in the previous post. Apart from this quote from Microsoft's BoundColumn webpage saying

The leftmost visible column in a combo box (the leftmost column whose setting in the combo box'sColumnWidths property is not 0) contains the data that appears in the text box part of the combo box in Form view or in a report. The BoundColumn property determines which column's value in the text box or combo box list will be stored when you make a selection. This allows you to display different data than you store as the value of the control.

which is why I said that, as the first column in my combobox has a width of 0cm, a boundcolumn of 1 is actually the second column in my combobox.

It is the resetting of this data to the top value when I select a bound column of 1 which was confusing me.



EDIT: Oops, my bad. I think I was thinking that the quote I have from Microsoft was referring to the boundcolumn, reading it again after posting made me realise it is just talking about what you actually see on the form.