Dlookup value in table, multiply it with a value in form field 1 = value form field 2

[prtc]

Hello ,

I have made some progress with what I would like to do but I am now questioning if it can be achieved at all
MS Access 2007.

Table Extraction Line

Field: Extraction ID: text data type (Primary key Compound)
Field: Atomic number:text data type (Primary key Compound)Field: Tonnage:Number data type

Field: worth:Currency data type

The 2 primary key fields are taken from the two tables that make up the 1:M relations for the extraction line table.
That is 1 resource is present in many extraction lines .
and 1 Mine extraction contains many extraction lines.

I have created a standard wizard form based on the fields of the extraction line table:


What I want to happen is:

As soon a user enters the tonnage value in the form.


In the afterupdate event for the tonnage field, but my preferred method would be in the control source for the worth field itself a Dlookup will look at the Resource table and find the Price field (currency data type).

It then multiplies the Resource table Price value with the tonnage amount field (e.g) $50 x 5 etc, the $250 value is then added to the worth field.

So far a simple Dlookup works I have entered this in the worth field control source for the extraction line form field :
=DLookUp("[Price]","[Resource]")

this displays 75 in the immediate test window and in the form is multiplied by whatever value I enter in the tonnage field.

The problem is I cannot get the dlookup to lookup up the value in the resource table that is also associated with the extraction ID so I get that specific resource value and not as I am getting the first price from the first record in the resource table. So far i have tried:

Attempt 1

=DLookUp("[Price]","[Resource]","'[Atomic Number] =[Extraction ID] And [Extraction ID] = [Atomic Number]'")*[tonnage]

Attempt 2

=DLookUp("[Price]","[Resource]","[Atomic Number] ="'[Extraction ID] And [Extraction ID] = [Atomic Number]'")*[tonnage]
error expected list separator or )



Attempt 3

=DLookUp("[Price]","[Resource]","[Atomic Number] =" & [ExtractionID])


=DLookUp("[Price]","[Resource]","[Atomic Number] =" & [Atomic Number] & " And ExtractionID=" & [ExtractionID])


Attempt 4
=DLookUp("[Price]", "[Resource]","[Atomic Number] = Form![EmployeeID]")

version 5

=DLookUp("[Price]", "[Resource]","[Atomic Number] = Form![Atomic Number]")

version 6

=DLookup("[Price]", "[Resource]","[Atomic Number]='" & [Atomic Number] & "'")


version 8

=DLookup("[Price]", "[Resource]","[Atomic Number]='" & [Atomic Number] & "'")


=DLookup("[Price]", "[Resource]", "[Atomic Number]='" & Forms![Extraction Line]![Atomic Number] & Forms![Extraction Line]![Extraction ID])

lol its not for the want of trying.

On some attempts I have omitted the * tonnage multiplication due to errors in the immediate window for the lookup part only.

Is it possible to do this with dlookup and i am making typo errors or is it not possible to Dlookup a value in a table then multiply it with a value in form field 1 to = a value in the form field 2

cheers

[June7]

Certainly possible. Concatenation of criteria value from form is correct approach.

The last attempt shows two fields concatenated for criteria value but does not have second apostrophe delimiter.

It may be possible to retrieve the value through table joins in form RecordSource.

I need to know more about data structure. Want to provide db for analysis? Follow instructions at bottom of my post.

Will need VBA code to save the result to field, if that is your intent.

[prtc]

Hi will this help as its the tables of concern.

What i think is needed is

The correct dlookup syntax or where clause that looks up the resource price associated with atomic number primary key and the Extraction ID primary key from the extraction line table.


I am guessing here but will the syntax need to jump back to the parent table (resource table) to get the price value associated with the 1:m relationship.


http://imageshack.us/photo/my-images/821/image4lm.jpg/


http://imageshack.us/photo/my-images/37/image2zzc.jpg/

[June7]

Options:

1. Include Resource table in RecordSource of data entry form for Extraction Line records. Join type "Include all records from Extraction Line...". Bind textbox to Price field, set properties Locked Yes, TabStop No.

2. Multi-column combobox that has properties:
RowSource: SELECT [Atomic Number], [Atomic Name], Price FROM Resource ORDER BY [Atomic Name]
BoundColumn: 1
ControlSource: Atomic Number (from Extraction Line table)
ColumnWidths: 0";0.25";0.25"

3. My last choice would be DLookup - assuming Atomic Number is a number field:
=DLookup("[Price]", "[Resource]","[Atomic Number]=" & [Atomic Number])

Now a textbox that calculates Worth:
=PriceTextboxname * Tonnage
or
=PriceComboboxname.[Column](2) * Tonnage

The Worth can be calculated whenever needed, no requirement to save in table. However, if you want to save to table so that it won't be changed if Price changes, use VBA code in some event. The trick is figuring what event to put code in. Code in that event would save the calculated value: Me!Worth = Me.WorthTextbox. When I want to control user interaction that tightly, I customize db to disable Navigation pane, ribbon, shortcut menus, and X close. Then create a custom 'Close' or 'Next Record' button that would have the code to save calculated data.

[prtc]

Hi and thanks for help and choice of solutions.

Yep I went straight to option 3 and here is what happened:

Followed MS recommended way to use Dlookup syntax even tho it does not provide an exact example of what I am trying to do:
http://support.microsoft.com/kb/208786

All tested in vba immediate window only, thinking if the immediate window test answer works then wherever i put the Dlookup after the test will work:

The Atomic Number field value that I am looking up is a text field value:

? DLookUp("[Price]","[Resource]","[Atomic Number]=" & [Atomic Number])
error external name not defined

? DLookUp("[Price]","[Resource]","[Atomic Number]= & 'Atomic Number'")
? DLookUp("[Price]","[Resource]","[Atomic Number]= & '[Atomic Number]'")
run time error 3075

? DLookUp("[Price]","[Resource]","[Atomic Number]= & ""Atomic Number""")
? DLookUp("[Price]","[Resource]","[Atomic Number]= & ""[Atomic Number]""")
run time error 3075

? DLookup("[Price]", "[Resource]", "[Atomic Number]='" & [Atomic Number] & "'")
error external name not defined

Will try the other options as adding those Dlookup test syntax to the ones tried in my first post .....for neither love nor money is DLookup going to pull the value required.

will post back on success of other options.

[June7]

Testing in immediate window won't work because VBA does not know where the criteria [Atomic Number] is. If a form were open and a textbox on that form bound to that field, this should work:

? DLookup("[Price]", "[Resource]", "[Atomic Number]='" & Forms!formname![Atomic Number] & "'")

But expression in query or in textbox ControlSource would be referencing the [Atomic Number] of the current record so form prefix not needed.

DLookup would be my last choice for this situation. Domain aggregate functions can be slow, especially in larger datasets.