Have all fields appear when value is selected from a combo box on a form

[ryanmce92]

Hi All,



Have a supplier Database with the usual contact details on it, I have a form where I can select the supplier through a combo box and I want all of the fields to appear.

The code below seems to be working fine when I first go into the form however once I select a different supplier then only the StreetNo appears?.

Private Sub cboSupplier_AfterUpdate()
Me.tboStreetNo = Me.cboSupplier.Column(1)
Me.tboAddress1 = Me.cboSupplier.Column(2)
Me.tboAddress2 = Me.cboSupplier.Column(3)
Me.tboCity = Me.cboSupplier.Column(4)
Me.tboCounty = Me.cboSupplier.Column(5)
Me.tboCode = Me.cboSupplier.Column(6)
Me.tboName = Me.cboSupplier.Column(8)
Me.tboNo = Me.cboSupplier.Column(9)
Me.tboEmail = Me.cboSupplier.Column(10)
Me.tboDelivery = Me.cboSupplier.Column(11)
Me.tboQuality = Me.cboSupplier.Column(12)
Me.tboExp = Me.cboSupplier.Column(13)
Me.tboRating = Me.cboSupplier.Column(14)
End Sub


Any help you could give me would be brilliant, cheers.

[NTC]

Well your code indicates you are calling each field individually; so are these tbo fields unbound? if so then put the code into the unbound field itself as its control source rather than in an AfterUpdate event. i.e the unbound field tboAddress1 should have inside of it: = Me.cboSupplier.Column(2)

Then as you change cboSupplier all these fields will automatically change.

If the tbo fields are bound to the same table as cboSupplier; then your approach is not necessary at all. delete cboSupplier and remake it and be sure to select the correct option when the wizard pops up (the bottom option to select record).


Finally if tbo fields are bound - but not bound to the same table as cboSupplier - then essentially you are writing data from 1 table to another table which is not a good design at all and shouldn't be necessary in a relational database.

[Missinglinq]

So on picking another supplier tboAddress1, tboAddress2, tboCity, etc. are all blank? Not to belabor the obvious, but are you sure that the second supplier you're picking, while testing, actually has data in these Fields?

Linq ;0)>

[ryanmce92]

The purpose of this is to be able to edit the previous data so I want the data to appear and then give the user the option to change the details.

MissingLinq yes I have created 4 "test" suppliers and all of them have data entered.

I don't want to give the users access to the tables to edit the data, I want them to be able to edit it via a form, if there is another more sensible way then let me know?.

[ryanmce92]

Well your code indicates you are calling each field individually; so are these tbo fields unbound? if so then put the code into the unbound field itself as its control source rather than in an AfterUpdate event. i.e the unbound field tboAddress1 should have inside of it: = Me.cboSupplier.Column(2)

Then as you change cboSupplier all these fields will automatically change.

If the tbo fields are bound to the same table as cboSupplier; then your approach is not necessary at all. delete cboSupplier and remake it and be sure to select the correct option when the wizard pops up (the bottom option to select record).


Finally if tbo fields are bound - but not bound to the same table as cboSupplier - then essentially you are writing data from 1 table to another table which is not a good design at all and shouldn't be necessary in a relational database.

I cannot have the data in the control source as the control source is the field in the table which it is bound too. All the data is from one table,

I have a Supplier table and this includes all of the above fields, when a supplier is selected I want the data to appear in the appropriate fields, I want this so that the end user is capable of editing details of any supplier on the list without giving them access to the tables?.

If there is a more suitable way of doing this please let me know as I am open to ideas.