[Access] How to use DLookup? and how to select the latest price? (1 Viewer)

yunhsuan

Member
Local time
Today, 16:00
Joined
Sep 10, 2021
Messages
54
Hello!
I made a form, containing project, site, stock and unit price. project and site are stored in table Project. stock and unit price are stored in table Stock_Name and Stock_Price. I tried to make site and unit price be filled in automatically according to project and sotck, but I met several problems.

Q1. I hope site can automatically show after project is selected and the content of site can be changed manually. So I tried to use DLookup to resolve problem like this:
Code:
Private Sub project_AfterUpdate()
    Me![site] = DLookup("site", "Project", [No] = Me![project])
End Sub
But nothing showed up. Can anyone tell me what happened with my code and how can I corrected it?

Q2. Table Stock_Name and Stock_Price stores the information about stock, containing stock, unit price and updated time (of unit price). I hope the latest unit price of the stock shows after the stock is selected. The history record could not be changed due to the update of the unit price. How can I do to achieve this? Or which fn could I use?

This is my test file: https://reurl.cc/Krbrve

Thanks in advance!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:00
Joined
Oct 29, 2018
Messages
21,358
For Q1: Try it this way.
Code:
DLookup("site", "Project", "[No]=" & Me.Project)
 

plog

Banishment Pending
Local time
Today, 03:00
Joined
May 11, 2011
Messages
11,613
It's really hard for me not to look at tables and Relationships. Here's the questions I have about yours:

1. Why do you have tables named "Form_X"? Tables should be data focused and named generically with the data they hold. There have to be better names than "Form_X" for these.

2. Why does Form_1 have a composite primary key composed of an autonumber and not just use the autonumber itself? Noo should be the only primary key in Form_1.

3. Why do you have [unit price] and price both as fields in Form_1? Normally you have a [unit price] and a Quantity field in a table and then use math in a query to arrive at TotalPrice. But you don't have a Quantity field, you just have 2 price fields. Is stock supposed to be the quantity field such that [unit price]* stock = price?

4. Form_1 should not have a site field. Because it is related to Project table and a project is related to a site, you do not duplicate the site value in Form_1. You simply connect Form_1 and Project in a query and get the site value from Project that way.

5. Form_2 shouldn't exist. Just store the Day value in Form_1, not the No value.

6. Use better names. Instead of [No] and [Noo] use prefixes to tell what they are from, like Form_1No, StockNo, PriceNo, etc. Also, you have 2 stock fields--one is a number in Form_1 and the other is text in Stock_Price. This will be confusing and could lead to errors. Use better names to distinguish them.
 

yunhsuan

Member
Local time
Today, 16:00
Joined
Sep 10, 2021
Messages
54
It's really hard for me not to look at tables and Relationships. Here's the questions I have about yours:

1. Why do you have tables named "Form_X"? Tables should be data focused and named generically with the data they hold. There have to be better names than "Form_X" for these.

2. Why does Form_1 have a composite primary key composed of an autonumber and not just use the autonumber itself? Noo should be the only primary key in Form_1.

3. Why do you have [unit price] and price both as fields in Form_1? Normally you have a [unit price] and a Quantity field in a table and then use math in a query to arrive at TotalPrice. But you don't have a Quantity field, you just have 2 price fields. Is stock supposed to be the quantity field such that [unit price]* stock = price?

4. Form_1 should not have a site field. Because it is related to Project table and a project is related to a site, you do not duplicate the site value in Form_1. You simply connect Form_1 and Project in a query and get the site value from Project that way.

5. Form_2 shouldn't exist. Just store the Day value in Form_1, not the No value.

6. Use better names. Instead of [No] and [Noo] use prefixes to tell what they are from, like Form_1No, StockNo, PriceNo, etc. Also, you have 2 stock fields--one is a number in Form_1 and the other is text in Stock_Price. This will be confusing and could lead to errors. Use better names to distinguish them.
1. This file is just for test, so all of item in this file is not real. I just want to check if the fn could work.

2. Form_2 is main form, and Form_1 is sub-form. No in Form_1 and Form_2 is connected. There would be several records in one document. This would cause replication of No, so I
create two table. One record the real No of document (Form_2), and the other store the item and it's No (Form_1). As for Noo, it is created to avoid replication of primary key.

3. All of qantity, unit price and total price fields are exist in my real database. Total price = quantity * unit price. “stock” here means the name of stock. I would change stock to stock name.

4. Not every project can corresponds to right site, such as sporadic cases. The site of it should be decide depending on situation. So I hope the site can be changed manually. Can site be changed causing query?

5. Like explained in 2, there would be several record in one document with same No. I just know this way to achieve this problem. Is there simpler way to resolve this? If yes, please tell me.

6. In my real database, there are better names. Stock in Form_1 and Stock_Price are the same thing. Both of them are the names of stock. Stock name in Stock_Price is store as reference. Stock name in Form_1 is connected to it in Stock_Price, and they connected by No in Stock_price. So stock in Form_1 is number, and it can be selected by drop-down.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:00
Joined
Feb 19, 2002
Messages
42,981
If you want us to look at your database, please use the upload feature of the site so it can be properly screened for virus'. I didn't look at the db so I don't know the schema BUT, in the RowSource of the combo that you use to select productID, you should also include the price. Then, in the AfterUpdate event of the combo:

Me.UnitPrice = Me.cboProductID.Column(3). This assumes the price is the FOURTH item - ProductID, ProductName, SupplierID, UnitPrice. Remember the rowSource of the combo is a zero based array.
 

Users who are viewing this thread

Top Bottom