Update another field using Select Case statement based on ComboBox sel

[futbolista]

I have attempted to write to a field on a form , using a Select Case statement, based on the user's selection in a ComboBox. The row source of the ComboBox is a query of all of the companies in the table that is the record source for the form. In other words, when the user selects "AT&T" from the ComboBox, I want the appropriate Manager's Initials to be written to the ResponsibleManager field. (This field is displayed in a text box on the form.) I prefer to use a Select Case Statement, because sometimes, there will be new companies entered with no predetermined reponsible Manager, and the Case Else statement can write a zero length text string to the field until the Select Case statement is updated. Any Suggestions?

 

[Sameal]

Write some code into the AfterUpdate event of your combo box. Basically after the combo box is updated you want to take the choice, pass it through your select case statement and assign the appropriate initials to the manager box. I'm going to assume that the manager's initials are written into the code and not pulled out of a table, but with a little work you could make it pull the initials out of a customer table.

Assuming your combo box is named "cmbCust", your text box with the initials is called "txtMgr", and your form is called "frmCust" then this code will do the trick. Realize that this is as basic as you can get and you will still want to add some error control to this sub-routine.

Private Sub cmbCust_AfterUpdate()

Select Case [Forms]![frmCust]![cmbCust]
Case "Honda"
[Forms]![frmCust]![txtMgr] = "HA"
Case "Toyota"
[Forms]![frmCust]![txtMgr] = "TA"
Case Else
[Forms]![frmCust]![txtMgr] = ""
End Select

End Sub