Combo box losing functionality (1 Viewer)

[Vivirtruvian]

[SOLVED] Combo box losing functionality

Hi all,

I have a combo box (cboMaterial) on a form that updates a number of text boxes with values from a table (tblProductList). The purpose of the form is to allow easy data entry for staff where a whole lot of product data auto loads based on the material number the enter.

This has been working fine with the following code:

Private Sub cboMaterialNo_Change()
Me.txtDescription.Value = Me.cboMaterialNo.Column(1)
Me.txtRMFG.Value = Me.cboMaterialNo.Column(2)
Me.txtCustomer.Value = Me.cboMaterialNo.Column(3)
Me.txtDepartment.Value = Me.cboMaterialNo.Column(4)

End Sub

Private Sub cboMaterialNo_KeyPress(KeyAscii As Integer)
Me.txtDescription.Value = Me.cboMaterialNo.Column(1)
Me.txtRMFG.Value = Me.cboMaterialNo.Column(2)
Me.txtCustomer.Value = Me.cboMaterialNo.Column(3)
Me.txtDepartment.Value = Me.cboMaterialNo.Column(4)

End Sub

Important to note, the columns above are all text values except for the material number.

I have since tried to add another column, txtPS (pack size), which contains another number value pertaining to the weight per unit of each material. I simply tried adding this to the above code:

Private Sub cboMaterialNo_Change()
Me.txtDescription.Value = Me.cboMaterialNo.Column(1)
Me.txtRMFG.Value = Me.cboMaterialNo.Column(2)
Me.txtCustomer.Value = Me.cboMaterialNo.Column(3)
Me.txtDepartment.Value = Me.cboMaterialNo.Column(4)
[COLOR="Red"][B]Me.txtPS.Value = Me.cboMaterialNo.Column(5)[/B][/COLOR]

End Sub

Private Sub cboMaterialNo_KeyPress(KeyAscii As Integer)
Me.txtDescription.Value = Me.cboMaterialNo.Column(1)
Me.txtRMFG.Value = Me.cboMaterialNo.Column(2)
Me.txtCustomer.Value = Me.cboMaterialNo.Column(3)
Me.txtDepartment.Value = Me.cboMaterialNo.Column(4)
[COLOR="red"][B]Me.txtPS.Value = Me.cboMaterialNo.Column(5)[/B][/COLOR]

End Sub

However, as soon as I do this I lose the ability to type in the combo box correctly. If I were to type in material "123456" rather than enter a 1, then 2, then 3 and so on, each time I press a number the combo box will try to search for a new record. Everything lists correctly if I use the mouse and drop-down, but I need staff to be able to type values in rather than search through a drop-down list (although the list is useful!).

Is adding another number to the columns confusing the combo box in some way?

Thanks in advance!

 

[moke123]

try simply using the afterupdate event of the combobox.

Private Sub cboMaterialNo_AfterUpdate()
Me.txtDescription.Value = Me.cboMaterialNo.Column(1)
Me.txtRMFG.Value = Me.cboMaterialNo.Column(2)
Me.txtCustomer.Value = Me.cboMaterialNo.Column(3)
Me.txtDepartment.Value = Me.cboMaterialNo.Column(4)
Me.txtPS.Value = Me.cboMaterialNo.Column(5)

End Sub

It is the native function of a combobox to search as you type. the change and keypress events are firing with every key stroke which is causing your problem

 

[Vivirtruvian]

Thanks! Worked out a treat! Not sure where I got all the extra code from in the past, but glad I do not need it!

Thanks again.

 

[moke123]

your welcome. Best of luck with your project.