Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 14th, 2006, 09:48 AM
Registered User
Join Date: Apr 2006
Location: Lebanon, Tennessee, USA.
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
Reply With Quote
  #2 (permalink)  
Old April 14th, 2006, 01:43 PM
Friend of Wrox
Join Date: Dec 2005
Location: , , .
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 ..."

Reply With Quote
  #3 (permalink)  
Old April 19th, 2006, 02:55 PM
Registered User
Join Date: Apr 2006
Location: Central Point, OR, USA.
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.


Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 02:17 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.