Me.cboProjService.Column(3) Not working correctly

[OzNet]

I have a combobox on a subform with this code attached.

Private Sub cboProjService_Change()
If Not IsNull(Me.cboProjService) Then
    Me.SortOrder.Value = Me.cboProjService.Column(3)
    Me.Refresh
    Me.Requery
End If
End Sub

The subform is a continuous form. The Combobox updates the SortOrder field of the first record but subsequent records do not update when changed.

What am I doing wrong?

[NeoPa]

Nothing. As far as I can see. You are updating the current (selected) record.

This depends of course, on what you are trying to do. If this is not it then the code is wrong. What are your intentions?

PS. Line #4 is entirely redundant. Line #5 is a superset.

[topher23]

As NeoPa pointed out, this code should update the current record. You said that the combo box will update the first record in the set, but not subsequent records. Did you mean that the combo box updates the first record every time the value is changed, no matter which record is selected, or that it only works when the first record is selected?

[OzNet]

Thanks for your replies, I'll try and give you the essential information.

The combobox is bound (control source) to the ProjectServiceL S field of the tblProjectEsitm ates table via a query. This table and query also has a field called SortOrder which I want automatically filled when making a selection in the combobox.

The combobox looks up a table called tblProjectServi ces to get the PCode (Project Code) field value. The tblProjectServi ces table also has a filed called SortOrder which allocates the logical sequence of the Project Services listed. I want this value (column 3 in the combobox) to go into the SortOrder field of the tblProjectEstim ates table so I can sort the list by this field.

When I change the selection in the combobox of the first record, the Project code changes and the correct sortorder appears in my field.

When I change any of the records below the first, the Project code changes but nothing else does. The first record sortorder remains the same and the sortorder field of the edited record remains blank!

Could Access get confused with the two tables having the same SortOrder field name? I will give this a try.

[NeoPa]

Check that the control on your form is actually bound. It sounds as if maybe it's not.

[OzNet]

OK - I worked out what was wrong.

I created another combobox which worked and then compared the different properties.

The problem was a piece of code which I had attached to the OnClick property:

Me.Requery

and must have interfered with the action of the OnChange piece of code.

I would have added this code to refresh the data and then later added the OnChange code.

[NeoPa]

The solution :)

Thanks for posting it. We always appreciate that where possible.