Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 14th, 2006, 09:48 AM
Registered User
Join Date: Apr 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Auto-populate table data into form

Hey guys, I'm pretty new to Access VBA, and realized that this one was instantly over my head. I have a form in an Access database that is used for data entry into a table called tblOrderEntry. Much of the data that is keyed into the form already exists in other tables in the database, but the form itself writes to a totally different table. I'd like to find a way to key in my OrderID and have certain fields populate with data related to that ID that is stored in other tables. I tried an AutoLookup query, but this locks down fields, and I have other controls in the form that need data input into that are not found in other tables, such as a tracking number. If anyone has anything that may work, I'd really appreciate it. Thanks a lot.

Jeff Neuenschwander
Old April 14th, 2006, 01:43 PM
Friend of Wrox
Join Date: Dec 2005
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts

You need to set your recordsource for the form to be a query that links the tables with the related data. Right click on the form in the Design View, select Properties, change the one you view (probably says Detail now) to Form, go to the Data tab, on the first line enter something along the lines of:

"SELECT tbl1.field1, tbl1.field2, tbl1.field3, tbl2.field1, tbl2.field3, ... FROM tbl1, tbl2, ... WHERE tbl1.field1 = tbl2.field2 AND tbl1.field2 = tbl3.field1 ..."

Old April 19th, 2006, 02:55 PM
Registered User
Join Date: Apr 2006
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts

You can, as the other user suggests, use a query to join the tables. This might not work if you have a lot of tables or some without common keys. However, a larger scenario might be to rethink the organization of the entire project if you have a lot of data duplication in your tables. That's generally a symptom of a project that needs better organization. Search Google for Normalization.

Failing that you can use a DLookup command fired by the AfterUpdate event procedure to grab data from any table based on the criteria you're entering, e.g.

Private Sub UserID_AfterUpdate()
Me!FName = DLookup("FName", "Users", "UserID = Forms!ThisForm!UserID")
End Sub

This uses the UserID to fetch the first name from the UserID table and put it on the form.



Similar Threads
Thread Thread Starter Forum Replies Last Post
populate a drop list fro a data table greenbriar ADO.NET 2 July 22nd, 2008 10:18 PM
how to populate a table with xml data in xslt miccipynewbie XSLT 6 May 28th, 2007 07:29 AM
Auto Populate fields in a form mnemec24 Access 8 March 9th, 2005 02:11 PM
populate data from Access Table into datagrid bisigreat VB How-To 6 August 17th, 2004 06:58 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.