Solved Auto Population of Form Field (1 Viewer)

[Juett]

Hi everyone.

I've come across a strange issue that I can't seem to figure out....

I have a combo box whose source is a query that runs and stores the results in the combo box drop down list, and it works fine.

I have some VBA that stipulates when a user selects a result/row from the combo box dropdown, the information in that result/row should be inserted into some text fields on the form.

This is standard stuff, and I've used this for other combo boxes and forms without issue. But for some reason it won't work the same this time.

The code is:

Private Sub Form_Load()
Me.CMB1.Requery
End Sub

Private Sub CMB_AfterUpdate()
Me.[Customer Name] = Me.CMB1.Column(0) & "  " & Me.CMB1.Column(1)
Me.[Contact Telephone] = Me.CMB1.Column(4)
Me.[Contact Email] = Me.CMB1.Column(3)
End Sub

The issue I have is that the first line of code works perfectly and the Customer Name field is updated and with the text from column 0 and 1, but, for whatever reason, I cannot get the data from column 3 and 4 to appear in the contact telephone and contact email fields.

After some messing around I noticed that the only way I can seem to get it to work is to open the ComboBox dropdown normally with the mouse left click, and then select the desired result/row with the RIGHT CLICK of the mouse. This works, and all fields are then updated. But it will not work with the normal mouse and left click.

Is there a reason for this?

Thanks as always.

 

[Juett]

I've solved this. Idiot moment - the field customer name was also the record source of the combo box, so by concatenating the first two columns and then inserting them into the customer name field, I was also wiping the query result before it could finish updating the other fields.

 

[Pat Hartman]

Copying data and saving it in multiple places leads to data anomalies. There are very limited situations where this is necessary. One common one is with price in an order entry application. Prices change over time and you always want the order to show the price at the time the order was filled, rather than the current price. There are other ways to do this if you keep price history.

Copying names and contact information may be valid. Maybe when you look at old records, you want to see old values, but usually with info like this, you always want to see the current contact info rather than old info.