Combo Box Issue

[bkessinger]

Good Morning All,

I have the following code in the AfterUpdate event of a combo box:

Private Sub BilledTo_AfterUpdate()
Me!Address = Me.BilledTo.Column(1)
Me!Address2 = Me.BilledTo.Column(2)
Me!City = Me.BilledTo.Column(3)
Me!State = Me.BilledTo.Column(4)
Me!Zip = Me.BilledTo.Column(5)
Me.Refresh
End Sub

The RecordSource for the combo box is a query based on a table:

SELECT tbl_Customers.BilledTo, tbl_Customers.Address, tbl_Customers.Address2, tbl_Customers.City, tbl_Customers.State, tbl_Customers.Zip
FROM tbl_Customers;

The combo box name is BilledTo. The issue is that AfterUpdate, the Me!Address text box is updated, but the remaining textboxes are not. What am I failing to see. Thanks in Advance.

Bill K.

[HansV]

The line Me.Refresh shouldn't be necessary, I'd remove it, but otherwise I see no problems or errors.

Would it be possible to attach a stripped down, compacted and zipped copy of the database to a reply? Remove all sensitive information from the copy.

[bkessinger]

Will send on Friday. Thanks

[JohnH]

What the the names of the form controls that are bound to the various address fields?

e.g. You might have a text box names Text51 that is bound to Zip.
In that case Me!Zip = Me.BilledTo.Column(5) will put the right value into the Zip field but you won't see it immediately on the screen.
If you revisit that record later you will see it there.

If you renamed the control to Zip you will see it immediately.
or change the code to refer to the control by name
Me!Text51 = Me.BilledTo.Column(5)

[bkessinger]

Attached is the db in question. Open frm_Search and click on the Customer Tab. That's where the problems are: BilledTo, etc. Thanks again.

[HansV]

Thanks. The Row Source of the combo box has 6 fields, but the Column Count is 1. If you change Column Count to 6, the text boxes will be filled correctly.

By the way, the current setup allows the user to change the address fields in tbl_Invoices independently of those in tbl_Customers. If that's what you intended, that's fine. But if you want the address fields to be fixed, you can lock them.
If the address fields should always be the same as those in tbl_Customers, you don't need those fields in tbl_Invoices. You can use a query based on tbl_Invoices and tbl_Customers, joined on BilledTo, to retrieve the address fields.

[bkessinger]

Thanks again for the quick reply. The address fields are okay the way they are, but thanks for the suggestions.