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

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 ..."

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.

Cheers



AvGuy