Dlookup

[Rdog0505]

Ok I am trying to make an access database form and having trouble figuring out how to do one function I need. I have a table that has names and values for those names.

Product1 17
Product2 19
Product3 5

Now. In the form that i have I have made a combo box that lists all of the Product#'s from the Products table. Now I have another field that needs to auto populate itself based on what is entered into this combo box. So in the form if they select Product1, I want 17 to automatically appear in this new field. I am not sure how to do this in access.

Originally I created this in excel and it was simple Vlookup("Cell to look at","table to search","return column"), but I can't seem to repeat this in access.

Any help would be greatly appreciated

 

[PHV]

Have a look to the Column property of the ComboBox object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

How about DLookup?


Randy

 

[Rdog0505]

Here is a copy of what i have been trying to work on. In the form I want to be able to select an item from the combobox and have the next field dlookup the corresponding value from the products table. I tried to put in a dlookup but it doesn't work correctly.

 

[randy700]

Sorry, don't know what happened to the rest of my response.

=DLookup("ProductName", "tblProducts", "ProductID = " & Forms!FormName!ComboBoxName.Column(1))

Combo box columns start counting with 0.
This basically says, lookup the product name in the products table WHERE the product ID equals the value in the 2nd column of the combo box.



Randy

 

[Rdog0505]

Ok using that logic I came up with

=DLookUp("Standard Time","Products","Product = " & Forms![Team Information]!Production_Product1.Column(1))

For the database that I linked above. Only problem is that it doesnt work.

 

[dhookom]

I think you can only use the bound column of a combo box so the Column(1) might be useless. If you want to use a non-bound column, create a text box bound to the non-bound column of the combo box.

Your expression expects the Product field to be numeric.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya Rdog0505 . . .

If you include the [blue]value field[/blue] in the combobox as well you [blue]won't need[/blue] DLookUp! In the [blue]After Update[/blue] event of the combo, you'd have:

[blue]   Me![[purple][B][I]FieldName[/I][/B][/purple]] = Me![[purple][B][I]ComboboxName[/I][/B][/purple]].Column([purple][B][I]?[/I][/B][/purple])[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Rdog0505]

Perfect! Worked like a charm.

Thanks TheAceMan1.

And thank you everyone else that was trying.

 

[dhookom]

I question whether or not you need to store the information since you can always look it up. Storing this type of information is generally considered bad practice.

You could view the value from the column with a text box having a control source of:
[tt][blue]
=Production_Product1.Column(1)
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[Rdog0505]

That is exactly what i did. The information is not stored simply displayed. It is then used in calculations after that.

I do have another question though. After getting that to work I have a text box where a number is entered. Then I have that box that autopopulates with the correct number. I then have another box that takes the text box number and multiplies it by the autopopulated number.

Product Quantity Value Total Value
Product1 110 10 1100

Basically like that. Now that works out just fine the way it is. Down below though I have a field that adds the total of the [Total Value] fields. The problem I am having is that there are 10 possible fields and not all get used so some remain blank. Because these numbers are not fields themselves I can not seem to get them to have a default value of 0 so that the add function will add them all correctly. Right now I get a blank because its adding a number to a bunch of blank values.

 

[PHV]

Have you tried the Nz function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

What are the "10 possible fields" since they are not fields? You shouldn't be storing calculated fields. If you have a null that affects your results, you might be able to use something like:
[tt][blue]
=Sum(Nz([Quantity],0) * Nz([Value],0))[/blue][/tt]

If you have multiple quantity and value fields then I expect you need to change your table structure.


Duane
Hook'D on Access
MS Access MVP

 

[Rdog0505]

I am not storing the calculations, but for the other calculations to work i need these to work. This is what it looks like.

Product Quantity Value Total
Product1 110 10 1100
Product2 50 8 400
Blank 0
Blank 0
Blank 0
Blank 0
Blank 0
Blank 0
Blank 0
Blank 0



Total Work Done 1500
Total Possible 2400
Time Loss 900

Efficiency 62.5%


The Product Field and the Quantity Field are stored in the database. The Product Field is a dropbox from another table and the value field looks at that drop box and enters the value corresponding to that. This part works just fine. The Total column is not a saved field and is simply a calculation source=[Quantity]*[Value]. This works just fine as well. The problem i have is with the Total Work Done Calculation. This is supposed to take and sum all the Total calculations, but as you can see some of those calculations are not done. I want this to be flexible and work no matter how few or many products are chosen. So right now i have it written as source=[total1]+[total2]+[total3]+......[total10]. Since some of these totals are blank that is what i get as the total. Not an error...simply blank.

 

[PHV]

What about this ControlSource ?
=Sum([Quantity]*[Value])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Rdog0505]

Since some of the Value fields are not populated i get the same thing. I basically need a way to have those be 0 unless i choose a product...then i would need it to return the correct value for the product. i have Source=Product1.Column(1) and that works great, but I cant get it to default 0 when nothing is in Product1 field.

 

[Rdog0505]

Nevermind I figured out how to use an If statement to make it work. Under value I put

=IIf([Quantity1]=0,0,Product1.Column(1))

that did the trick.. Thanks everyone though