I have an Inventory table set up like this:

• ItemCode is the primary key
• SalesPrice

I have a WSales table with:

• ID as primary key
• ItemCode (duplicates allowed)
• SalesPrice
• SalesQty

On the capture form I want to capture the SalesQty and the SalesPrice for an ItemCode. This SalesPrice is usually the same as the SalesPrice field in the Inventory table but it could vary. So to save capture time I would like the SalesPrice from Inventory to show on the form as the default value. I have created a default value for SalesPrice on the form as

=[Inventory]![SalesPrice]

Does not work as the form field remains empty The two tables are linked by ItemCode in a one to many relationship Any suggestions would be most welcome. By the way, new to Access so not yet up to speed on VBA or SQL Using Access in Office365

1 Answer

I believe the problem is that you are specifying a field where you want to return a single value. How should Access know which member of the SalesPrice field you want to return?

The answer is a query. In Access the command is called DLOOKUP. It would go something like this:

=DLookUp("[WholeSalePrice]","[Inventory]","[ItemCode]=" & Forms![WorkShop Sales Capture]![ItemCode])

This is like an SQL query that says give me the SalesPrice from the Inventory table where the ItemCode is the same as the ItemCode on Form Capture. You will have to ensure that the form name is correct. I put "Capture" in there because that is what you called the form.

3

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy