Problems with autofill code in order forms, autofills acting the fool

[jree3000]

Hello,

I have an order form set up that autofills a few fields when the customer's name is selected. I did this using the following On Change code:

Private Sub Customer_Change()
Me.txtCompany.Value = Me.Customer.Column(2)
Me.txtPhone.Value = Me.Customer.Column(3)
Me.txtEmail.Value = Me.Customer.Column(4)
Me.txtPosition.Value = Me.Customer.Column(5)

It works...kind of. There are two major problems though.

1.) After you initially select the customer it autofills as it should. However when you go to the next record, it keeps that autofilled information there, even if a different customer was already selected in that next record, thus giving it the wrong information.



2.) Once you leave the form and come back, the customer is still selected however the autofill information is gone. Forcing you to reselect the customer to get the autofilled information but then you still have problem #1 to deal with.

I have also tried using the code in After Update with the same results.

The order form is based off a order table, the customer information comes from a customer table. The tables are joined. Otherwise I don't think I'd get any customer information at all.

Anyone have any ideas? Thanks in advance for any input.

[John_G]

Hi -

It sounds like the 4 fields you are filling in the OnChange event are not bound to any table or query fields, and therefore will not change when you move to another record.

If the combo box is is bound to the Customer_ID field in the orders table, then you can put your code in the onCurrent event of the form, which will fire every time you move to a new record. That includes a new record though, so you don't want to do it there - use the Me.NewRecord property to test for it.

For new records, the AfterUpdate event is the place to put that code, not the OnChange.

HTH

John

[jree3000]

Update: The autofill fields were unbound. I had to add the customer fields to the order form. So now the autofills don't disappear. Unfortunately there is now a new problem. They autofilled to one of the customers information and now they all autofill to that same customer's information even if a different customer is selected. Selecting a different customer does not update the autofills. Any ideas?

[jree3000]

Thanks for the input, John G. That was useful in helping me figure this out.

Update: Solved! I realized that when the autofilled fields autofilled they were changing the information in the customer table. I went through and changed Enabled to No on the autofill fields and now it all works as they should. Just FYI in case anyone else is having similar problems.

[John_G]

Hi -

You should now unbind the customer information fields on the form, and remove those fields from the Orders table. They are redundant, and violate the normalization rules. All you need is the Custimer_ID in the Orders table.

John

[jree3000]

I do not have those fields in the Orders table, they come directly from the Customer table. Just the Customer ID is in the Orders table, like you suggested. Thanks!