Changing a combo box value from a form

[MartyL354]

I have a form with a Client Name combo box and a Client Code label. Whenever the user selects a Client Name from the combo box I populate the Client Code label with the coresponding value.

I need to allow them to edit any of the Client Name/Client Code combinations. To do that I pop up a form that has both a Client Name and Client Code text box. I do DAO database executes to update the table values (there are probably easier native Access ways to do this but I used to write VB6 code and am used to having to do ADO & SQL to the tables for everything).

This all works fine.

My problem is that when I Requery the Client Name combo box the new value doesn't display until the user selects a different Client Name and then selects the new one. For example: If I change a Client Name of "John Company" to "John's Company" I will still see "John Company" until I select a different name and then reselect "John's Company"

What I want is after the form I pop up (where I prompt for the changes) closes for the user to see the new value they changed to.

The code I use is this:
Private Sub cmbClient_DblClick(Cancel As Integer)

On Error GoTo Error_Handler

If Not cmbClient.Value & "" = "" Then
DoCmd.OpenForm FormName:="frmEditClient", _
WindowMode:=acDialog, _
OpenArgs:=UCase(Trim(cmbClient.Value)) & "~" & lblCode.Caption

cmbClient.Requery
End If

Exit_Procedure:
Exit Sub

Error_Handler:
DisplayErrorMessage Err.Number, Err.Description, "frmVoidedTickets", "cmbClient_DblClick"
Resume Exit_Procedure
Resume

End Sub

The form I pop up does all of the validation and table updates.

Thanks kindly in advance,

Marty

 

[jal]

I'm guessing that the value displayed in the cbo's editable text area - the selected value - won't automatically update when you requery the cbo's rowsource. If you keep track of the row number, though, you could probably update the displayed value like this:

cbo.Value = cbo.ItemData(rowNumber)

 

[MartyL354]

Thank you kindly. That works.

I was trying to do soemthing like that earlier. I was tracking the row with .listindex but then I was trying to move to a different row and then back using the .selected property to move off the row and then back. That doesn't seem to work at all.

However, according to the Online Help:
----------------------------------------------------------------------
You can use the Selected property to select items in a combo box by using Visual Basic. For example, the following expression selects the fifth item in the list:
Me!Combobox.Selected(4) = True
-----------------------------------------------------------------------

Anyway, Thanks,

Marty

 

[jal]

Glad you got it working. VBA keeps changing, and also differs among the Office Apps so the online help can be misleading.

Incidentally, I doubt there is a way to select a cbo item by row number. What my proposed code above does is a string matchup (this is not a problem if only one row has that string). However, if two or more rows in the cbo have the same value, the runtime will select the first row with that string, regardless of what row number you specified. Although the displayed value will be correct, the wrong row might be selected, which is a potential gotcha. You might ask, "but why would I have repeating values in a CBO?" If it's a multicolumn cbo (for instance a cbo loaded with the Customers table) you could have two customers with the same last name, for instance (but each row would have a different customerID).

The runtime performs the string matchup on the bound column. Here I am assuming that the bound column is the last name.

I like Access CBOs, especially love the multicolumn feature (which VB.Net lacks by the way), but I am a bit disappointed that you cannot "really" select by row number (here VB.Net has the advantage). I guess every language has its pros and cons.