Returning value from related table

Access 03
Have four entities. Customer, Order, Order Details and Product.

In an order form (based on Order as parent and OrderDetails as
SubForm) I want to return the price of an item stored in the products
table.

I have tried to build a query and use the value from the ProductID
field as the criteria and place the looked up PartPrice in the control
source of the ProductPrice field but get a #name error.

This works fine on a standalone form, don't understand why it won't
work on a subform.

Any help appreciated.

Thanks

Mick
0
Mick
11/29/2007 8:22:34 PM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies
386 Views

Similar Articles

[PageSpeed] 14

Do not use criteria as you would need to refresh everytime you changed records.
Use a join in you query.
-- 
KARL DEWEY
Build a little - Test a little
0
Utf
11/29/2007 8:51:01 PM
Reply:

Similar Artilces:

Countif + External Reference = #Value
It appears that the countif function (along with sumif and others i'm betting) do not function properly with external links. The only workaround I've been able to find is to write a macro to open the various externally referenced sheets and then close them. Is there a better way for this? If you mean external linked workbooks that are closed then COUNTIF won't work but you can use SUMPRODUCT instead of both COUNTIF/SUMIF =COUNTIF(A1:A10,">0") =SUMPRODUCT(--(A1:A10>0)) will return the same result =SUMIF(A1:A10,">0",B1:B10) =SUMPRODUCT(--(A1:A10...

update a table based on a sum query
Hi All, I have seen the zillions of answers to this that state "never add a total into the table.." But there are cases, such as mine when it is prefered. I want to update a project table with the final invoice cost. I want it entered as a hard figure so that I can then export the table for accounting purposes. Additionally, once an invoice has been generated, there isn't any need for the total to ever, ever change! In fact it shouldn't. So the question is.... is there a way to do this? To update a table with a value from a sum query? Thanks for any help. ...

Comparing data between two tables
Dear all, In my access database there are two tables. The first one has complete information: customer_code, item_code, item_code_2, company, and values. The second has only the item_code, item_code_2 and values. Now I need to compare the values between the tables, but I have a big problem: I need the all data in the first table, and when I execute the consult the values stay in duplicity. I already think in create another table, but I don't know... because I have some data in one table that I don't have in another... How can I solve this problem? Thanks a lot!!! Andr=E9. On Oct...

how do i make pie chart ignore data labels where value is zero?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have a pie chart tracking the amount of time spent in a day. I have lots of possible categories and i certainly don't use them all each day, and the chart shows a bunch of overlapping labels in between slices if their value is zero. Does anyone know how to make the chart exclude these labels? <br><br>Thanks in advance! On 4/7/10 1:02 PM, Steppenwolf@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: > Intel I have a pie chart tracking t...

tableadapter Query linked to Access 2003 table
I'm trying to write a query for my Dataset's table adapter. It is linked to an access 2003 database. Basically, I'm trying to duplicate the following query. SELECT Parts_Inventory.Aisle, Parts_Inventory.Rack, Parts_Inventory.Shelf, Parts_Inventory.QNTY, Parts_Inventory.MFG_PN, Parts_Inventory.Manufacturer, Parts_Inventory.Description FROM Parts_Inventory WHERE (((Parts_Inventory.Manufacturer) Like "*" & [?] & "*")); I'm using a text box as my control to the manufacturer field. If it is blank, it will show all my parts. I'm able...

how to store values in a 2 dimensional array
i want to store Sheet name and its corresponding password in a 2 dimension array . hope anyone can provide a code snippet for storing sheet name and its corresponding password and also code for popping out values from an array . i am newbie to vba . providin code wil be really helpful . i need something like i need to store the values in this fashion Array(0) ----- > should contain sheetname and its password value Array(1) ----- > should contain sheetname and its password value while popping out i shuld put the values in 2 variables . sheet name in Variable 1 its corresp...

League Table follow up
Hi Although this is posted as a new thread it is really a supplementary question a reply fro0m Mike H. I show again an extract from my worksheet and the formula to get the draws from the fixtures. =SUMPRODUCT(($A$1:A$6=E1)*($C$1:$C$6=$D$1:$D$6))+SUMPRODUCT(($B$1:$B$6=E1)*($D$1:$D$6=$C$1:$C$6)) My question is that this formula works fine for games that have already been played & results entered in Columns C&D, however also assumes that unplayed games where no results have been entered are also draws and so can you suggest a simple amendment to the formula to disregar...

Can you write protect a table in a database?
Hi, I wonder if it's possible to make a table (for all users) write protected (I don't want people by accident changing data)? Cheers, Mikael Sweden Well, that's one way to get around the problem. But I really want to know if it's possible to write-protect the table, and if so, how? Cheers, Mikael Well, that's one solution to the problem. But I would really like to know if it's possible to write-protect a table, and if so, how? Cheers, Mikael If you are using Access 2003 or earlier you can use User Level Security to have the table 'read only' for c...

Football League Table Spreadsheet
Hi, Forgive me if I have posted in the wrong section but it is my first post. I am looking to use a spreadsheet to contain a league table and fixture list for a church league which has ten teams playing each other twice in a season. I want to be able to just add the scores in to the fixture list and have the table update itself accordingly. I want the table to have columns for the following: Played/Won/Lost/Drawn/Goals For/Goals Against/Goal Difference/Points I will be using Excel 2002. Many thanks...Gracust -- gracust ---------------------------------------------------------------------...

Calculated Field in Pivot Table
Hi, How can I create a coloum in a pivot table that is the sum of two others coloumns? Thanks, Tamir :-) EXCEL 2007 Click in the Pivot Table / PivotTable Tools / Options / Tools group / Formulas / Calculated Field / complete the Name: field / enter the Formula: field and remove the 0 / Click the first Field: you wish to add / Insert Field / enter the + sign / Click the second Field: you wish to add / Insert Field / OK If my comments have helped please hit Yes. "Tamir" wrote: > Hi, > How can I create a coloum in a pivot table that is the ...

An image as a backgroud for a table in a template
I want to have an image as a background for a table in a template. Then i want to add text boxes in each cell. The image will then be as a help for users were to put an X on the image. I use Word 2003. Insert the image and format it as Behind Text. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Roy" <Roy@discussions.microsoft.com> wrote in message news:C9FEDB40-3F6F-4B2E-88C9-ABCD56EC99BD@microsoft.com... >I want to have an image as a background for a table in a template. Then i > want to ad...

Pass table data from Access to a C dll and get it back
Hi, I want to pass a table's data into C code for further processing and get the processed data back into another table in Access. I have seen sample code in C++ opening an Access db and using automation on it. However, this is not permitted in my case; the driver application has to be Access itself. So maybe I have to pass the data into VBA and then further into the C code? Sample code for this anyone? Just a simple table with a few columns (properties) will do. TIA, Nikolas "rdrnws" <rdr@nws.com> wrote in message news:fh9nfp$rpo$1@aioe.org... > Hi, I want to ...

Change Time in a Cell from [hh]:mm to decimal value ie 16:45
Here lies the problem. I'm cheap, I want to be paid for every minute that I work, I do the time cards for work and I'm using Excel to perform the functions. I have no problem with adding and subtracting the times in single cells or totalling all of the time. If I come up with a person having 47 hours and 13 minutes how do I get Excel to convert 47:13 to 47.22 so that I can then multiply the 40 by hourly rate and the 7.22 by 1.5 the hourly rate ? -- Thank You, God Speed, and Semper Fidelis Hi! This all depends on how you have things setup. To convert 47:13 to 47.22 just mu...

Not all code paths return a value?
The intent of my web service is an RSS feed from a blog. Originally I used a StringBuilder to make the XML and returned a string from the webmethod. But this doesn't display properly in IE. So now I'm trying an XmlTextWriter instead. I whipped-up another webservice based on this: http://www.codeproject.com/aspnet/RSSviaXmlTextWriter.asp?print=true This example isn't set up strictly as a webservice. It seems to output to an aspx web page, not an asmx web service page. And this code has a problem with the HttpContext. Anyway, I put it in an asmx file so it can be called as a ...

Investment re-valuing
My 401(k) company uses fractional shares and fractional values. The accounts they use are not directly referenced on any particular exchange. I had to manually create the accounts, and I have to manually update the prices. recently, two of the investments were "re-valued". They reference it as a "Transfer" but all that really happens is all my shares for a given investment were listed as negative transactions with the current unit value (so that the dollar value of the transaction was negative). Then they immediately followed that with a positive transaction for the ...

List from a Table
Hi, Suppose i have some data across 2 columns starting A2 and B2. Owner of car, and Car Model respectively. Column A may contain repeated entries (Owner can have more than 1 car of different companies AND can also have 2 same cars. ) B also contains repeated entries. abc ford def ford xyz nissan xyz suzuki efg honda abc toyota I want to enter in C1 name of the owner and get list of cars he has across C2:C5000. Same in D1, name of car and get list of owners across D2:D5000. ...

WMP12 2010s Auto PlayList Value
I'm using WMP12 on Win7. The Release Year field in auto playlists allows for creating playlists by decades (e.g. 1980s, 1990s, 2000s). Seems there is special cose that interprets 1980s as 198*. Unfortunately the list seems to forget that we just changed over to 2010 as there is no 2010s value in the drop-down list. So there appears to be no way to segregate 2000-2009 from 2010-2020. I opened the playlist file is notepad to see if there was SQL code but the value actually is "2000s" so somewhere in WMP it must do an interpretation. I also tried to put "2...

Time Table
hi members i want to develop a timetable for teachers and their periods. i want there should not be timeclash -- b166e ----------------------------------------------------------------------- b166er's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3491 View this thread: http://www.excelforum.com/showthread.php?threadid=57376 What have you done so far? Where are you getting stuck? If you provide some details, someone may be able to help. b166er wrote: > hi members > > i want to develop a timetable for teachers and their periods. > i want there sh...

group by week in pivot table
i have design a pivot table in excel, but I found no group by WEEK in the grouping function...is it possible to do that in other way or let me to define date of range ??? Million thanksss Right-click on the Date field button Choose Group and Show Detail>Group Select Days, and set the number of days to 7 Click OK Grey wrote: > i have design a pivot table in excel, but I found no group by WEEK in the > grouping function...is it possible to do that in other way or let me to > define date of range ??? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com...

Returning highest value
I have a list with value_name and its value. Is there a formula tha can look down the list and pick the highest value and return th value_name For example _Value_Name_ _Value_ Value A 5 Value B 10 Value C 33 Value D 8 Value E 51 Value F 32 Value G 47 Value H 9 Value I 41 So the formula would return Value E as its result. Would be much appreciated : -- Fishbon ----------------------------------------------------------------------- Fishbone's Profile: http://www.excelforum.com/member.php?action=getinfo&...

Export data from tables to Excel
Hi I want help on exporting data from tables to Excel. Im using a form with a command button when button is pressed i want all the data on the form to goto an Excel spreadsheet but i want the spreadsheet to auto save with the primary key as the file name. Thank you Andy Andy, you can use a macro with transfer spreadsheet. In the macro you specify the name for the new file. It is auto saved. Jeanette Cunningham "Andysc" <u41622@uwe> wrote in message news:803b29d5bc08a@uwe... > Hi > I want help on exporting data from tables to Excel. > Im using a form with a comma...

FA depn calculation and salvage value
FA calculate depn base on COST-SALVAGE. is it possible to have depn calculated based on COST, and the last depn is calculated as DEPN-SALVAGE. eg: cost=$12000 rate=10% depn=$1200 yrly, $100 mthly salvage value=$1 so, mth 1st to mth 119th, depn=$100 per mth then, mth 120th, depn = $99 instead of $100 is above possible in FA? ...

using VBA to retrieve values to ComboBox
i am trying to retrieve a range of data (the stock code of different phones) from worksheet("Inventory") based on the name of the branches of where the phones are stored. worksheet("Inventory") has branch (Column A) and stock code (Column B) i have 2 combobox. combobox1 contains values (name of branches) such as: 1-BS 2-EN 3-HG 4-JE 5-SP 6-TB 7-WS 8-YT combobox2 will have to retrieve values from the worksheet("Inventory") based on the value in combobox1. the values in combobox1 can be found in column 1 while the values i need for combobox...

Display values from 1st and 2nd field
Hi, I have a simple table that has two columns surname and firstname, how do I combine the results into a third column? Good at Excel but very new to Access. Thanks. Do it in a query like so: SELECT (FirstName + " ") & Surname AS FullName FROM YourTable; In query design view you'd do it by entering the following in the 'field' row of a blank column in query design view: FullName: ([FirstName + " ") & [Surname] By using the + operator to add the space between the names the space will be suppressed if by any chance someone was recorded only by th...

range names in pivot tables
Is there a way to categorize data by named ranges of the column headings rather than individual column headings? I need to be able to pivot between certain ranges rather than eliminate or add one column at a time. Or perhaps someone can suggest a different way to do it. I need to create 1 chart to pivot between the first 5, 7, 9 and 11 data columns. ...