Creating a textbox in access to lookup values?

Joseph S
Joseph S used Ask the Experts™
on
I am totally new to learning vba and access. If someone could teach me that would be appreciated. I have a command button and text boxes. Basically, I want to enter a value in the text box, and populates the rest of the text boxes related to that value.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
A little more context might help, but if the information is already in the database and you want to pull it up, you'd be better served using a combo box.  Please explain further and well do our best to help you get there.  Can you provide a concrete usage example.  A screenshot of your form.
Distinguished Expert 2017

Commented:
I'll give you a concrete example and you do the same for your situation.

On an order form, you choose a customerID from a combo box, your code then takes the shipping address fields from the combo's rowSource and places them into the ShipTo fields.  This set up allows us to have a permanent shipping address which is stored in the customer record but provides a way to over ride the ship to address for any given order.

In the AfterUpdate event of the CustomerID combo:

Me.ShipToAddr1 = Me.cboCustomerID.Column(5)
Me.ShipToAddr2 = Me.cboCustomerID.Column(6)
Me.ShipToCity = Me.cboCustomerID.Column(7)
Me.ShipToState = Me.cboCustomerID.Column(8)
Me.ShipToZip = Me.cboCustomerID.Column(9)


The RowSource of the combo is a zero based array.  The column numbers I used were arbitrary.  You would create the RowSource query and count the columns you selected.  The column # used above will be the ordinal number - 1 because the column index starts with0.  So field1 is .Column(0), field2 is .Column(1), etc.
Most Valuable Expert 2012
Top Expert 2014

Commented:
Be sure you're storing this data correctly. Pat's example of a Shipping address is valid, but in many cases you do not want to store data like this in "child" tables. Instead, you'd store the ID value of the "parent" record, and then pull data from that record as needed.

For example, in an Ordering system you'd typically have an Order_Header and an Order_Detail table structure. In the Order_header table you'd store a link to a Customer record, but you would NOT store the Customer's Name and such in the Order_Header table. By storing the ID value from the Customer table in Order_Header, you could always retrieve that data from the Customer table.

Same with Order_Detail. You would store the ID value from Order_Header in the detail table, but you would not store things like the Order Number, Order Date, Customer, etc. The link to the Order_Header table would be sufficient for you to pull that data when needed.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial