You are here:

Using MS Access/Default value using Dlookup?

Advertisement


Question
Pulling (the little remaining) hair out of my head and hope you can help:

Would like to code a form to do the following:

Form is built on ‘Order Entry – New’ table.  

User selects an order number from a drop down box (based on ‘Order Entry – Old’ table where order number is primary key).  Based on user choice, a number of other fields are automatically entered (Suppler Name, Supplier City, Supplier Zip Code) already associated with that order number on a different table.  Values entered into form should be default values where the user can tab into and make changes to data.

Goal here is to allow user to pull information from previous order number and save those values (or make edits) into a new order table.

My thought is that I could use Dlookup expression in the Default Value parameter of Supplier Name, etc. and then create a simple set of requery commands for the Supplier fields set to trigger AfterUpdate of Order Number field.

I am either wrong on this conceptually or am messing up the syntax…Rather than send code, I thought I would ask if I am even barking up the right tree first.  Is there a simpler, cleaner way to have a form update a bunch of fields from an unloaded table based on a common value?

Answer
"Is there a simpler, cleaner way to have a form update a bunch of fields from an unloaded table based on a common value?"

If I'm reading you correctly, you have field in your order table for Suppler Name, Supplier City, Supplier Zip Code. If so, this is a denormalized table and not proper design.

One of the principles of normalization is that data exists in ONE place and one place only. So You should have a suppliers table with a Supplier ID. This should then be a foreign key in your orders table. Similar for customers.

Then you can use the techniques in my blog on Displaying Data from Related tables (see blog address below) to display the supplier or customer info on your form.

However there is an exception to this rule. That exception would before info that might change only for a specific order. But I can't imagine that being the case for suppliers or customers.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.