Populate a form record by selecting one textbox?

[Inky2201]

I want to try to populate a whole record of text boxes on a form by selecting one (e.g. Item Number) from a combo box. I dont think I am making it very clear. I want to select an item in the combo box which will pull through the rest of the record.
Can anybody help?
Regards
Inky

 

[zevw]

In your combobox.rowsource you have all the field you want to populate

and the the afterupdate event procedure you would the following code

here is an example:

the rowsource for vendor is

"SELECT DISTINCT Vendors.ID, Vendors.Name, Vendors.Contact, Vendors.Address, Vendors.Address2, Vendors.City, Vendors.State, Vendors.Zip, Vendors.Country, Vendors.Phone1 FROM Vendors ORDER BY Vendors.Name;"

Private Sub Vendor_AfterUpdate()

    Me.Contact = Nz(Me.Vendor.Column(2), "")
    Me.Address = Nz(Me.Vendor.Column(3), "") & IIf(Len(Me.Vendor.Column(4)) > 0, " - " & Nz(Me.Vendor.Column(4), ""), "")
    Me.CSZ = Nz(Me.Vendor.Column(5), "") & ", " & Nz(Me.Vendor.Column(6), "") & " " & Nz(Me.Vendor.Column(7), "") & IIf(Len(Me.Vendor.Column(8)) > 0, " - " & Nz(Me.Vendor.Column(8), ""), "")
    Me.Tel = Nz(Me.Vendor.Column(9), "")
    
End Sub

 

[PHV]

If the Form is bound you may try to simply follow the Combo wizard.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Inky2201]

Thanks zevw,
I will give it a try.
Regards
I

 

[Inky2201]

Hello again zevw,
Thanks for the code it worked well with a bit of tweaking.
I now have a problem further on down the line.
I have a sub form which contains the text boxes that have been populated with the code you kindly sent me, but this record needs to be linked to the ID of the main form. I have tried including it in the combo box list. Is there any way that you know of that I can pull this through to link to the master field within your code?
Also I have realised that if this does link it will only be relevant to one master record and I want to be able to log these parts into a number of assemblies. Would you create a table for this?
Hope you understand, it is difficult to explain without writing an essay.
Regards
I