Solved Dlookup question:

[Teri Bridges]

I have two tables I am working with. Small tables because I am learning. StatusT has four fields, 1. Primary key auto number, 2. Status, 3. Description, and
4. Percent. My Task table has four fields 1. Primary key auto number, 2. Course 3. Status and 4. Percent.
The status fields are short text in both tables and the Percent field is double, percent, two decimal in both tables. So my field types match in both tables.
I created a form, what I am wanting to do is select the Status from a combo box and have it update my TaskT. I did that and it works great. Now what I want to do is have the percent field auto populate based on the percent associated with the status selection from the StatusT. I have read articles and even watched several videos. I have tried the two ways I have seen. First Example - In the control source for the percent field enter the following:

= DLookup("Percent", "StatusT", "Status=" & [Status]) when I open the form the percent field shows #ERROR no matter which status I select.

Second Example - For the Status combo box in the event after update write the following code:

PercentID = DLookup("Percent", "StatusT", "Status='" & StatusID & "'") this results in nothing populating in the percent field.

As I said I am learning, but for the life of me I cannot figure out what I have done wrong. If you have any suggestions I would be most Grateful.

 

[arnelgp]

if you already have the percent on StatusT table, you do not need to Duplicate it on TaskT table.
you create a query that will join your TaskT and Status table (join on Status field).
see this demo.

 

[The_Doc_Man]

The status fields are short text in both tables

= DLookup("Percent", "StatusT", "Status='" & [Status] & "'")

When searching for a text match-up, you need to quote the thing that supplies your text. Since double-quotes are part of the DLookup syntax, you need single-quotes (apostrophes).

PercentID = DLookup("Percent", "StatusT", "Status='" & StatusID & "'")

Not sure what is going on here, but maybe... If you REALLY meant to use the status ID field, you would use

PercentID = DLookup("Percent", "StatusT", "StatusID=" & StatusID )

 

[Pat Hartman]

Here is a database with a sample that shows three ways to display lookup dat on your forms if you need more than just one field from the lookup table. ALWAYS remember to lock the lookup fields to prevent accidental update when you use the join method.

Also, if the percent is just a starting point and you need to be able to update it, then it is OK to copy it and store it in both places. The best way to do that is to include the percent in your RowSource query. Then in the AfterUpdate event of the combo, copy the percent from the RowSource to the control on the form.

Keep in mind that the columns property is a zero-based array so the fourth column is referenced as 3 because the first column is referenced as 0.

Me.txtPercent = Me.cboStatus.Column(3)

 

[Teri Bridges]

if you already have the percent on StatusT table, you do not need to Duplicate it on TaskT table.
you create a query that will join your TaskT and Status table (join on Status field).
see this demo.

OMG, I just fell in love! This was the perfect solution. The example you sent helped me figure out exactly what I needed. I cannot thank you enough.

Here is a database with a sample that shows three ways to display lookup dat on your forms if you need more than just one field from the lookup table. ALWAYS remember to lock the lookup fields to prevent accidental update when you use the join method.

Also, if the percent is just a starting point and you need to be able to update it, then it is OK to copy it and store it in both places. The best way to do that is to include the percent in your RowSource query. Then in the AfterUpdate event of the combo, copy the percent from the RowSource to the control on the form.

Keep in mind that the columns property is a zero-based array so the fourth column is referenced as 3 because the first column is referenced as 0.

Me.txtPercent = Me.cboStatus.Column(3)

@ Pat H. Oh my, I am so new. This attachment looks amazing, but way over my head for now. I am going to study it. I think I am having a hard time breaking from a excel spreadsheet. Thinking I have to have everything in one table.

 

[The_Doc_Man]

I think I am having a hard time breaking from a excel spreadsheet.

One of our members, Uncle Gizmo, had a bunch of video-linked posts to help people make exactly that transition from Excel to Access. Might be worthwhile to search for them.