Can't get a value from a querry into a forms text box

I have a main Shippin Control table, and a main Shipping Control form 
with description of cargo and quantity text boxes. I have a querry which 
is a join of tblValues and tblShipping_Control. The SQL is below

SELECT Shipping_Control.DescriptionofCargo, Shipping_Control.CargoQty, 
tblValues.Value, ([Shipping_Control].[CargoQty]*[tblValues].[Value]) AS 
CommercialValue, Shipping_Control.ShippingDate, Shipping_Control.ShipTo, 
Shipping_Control.ShipFrom, Shipping_Control.CollectionNoteNo, 
Shipping_Control.PackingListNo, Shipping_Control.ShipmentID, 
Shipping_Control.Customer
FROM Shipping_Control INNER JOIN tblValues ON 
Shipping_Control.[DescriptionofCargo] = tblValues.[HGGPPart]
WHERE (((Shipping_Control.Customer)<>"GDF SUEZ"))
ORDER BY Shipping_Control.DescriptionofCargo;

I want to get the CommercialValue from the above into the 
CommercialValue box in the main form.

I have tried using D Lookup,  =DLookUp("[CommercialValue]", 
"[qryPartValue]", <criteria to select the
 > desired part>)

Adding this to the ControlSource in form design view for the commercial 
value text box puts the same value in every record in the main form, 
which is the first value from the qryPartValue

I have also tried adding the relevant qurries in querry design, but I am 
not getting the results I want. Maybe I amnot adding the corrct tables 
or qurries??

I have tblShipping_Control, the main table
tblValues, Values of the parts to ship from 3 sites
tblValueTees, Values of parts from another site
tblPart, listing every part which needs a value
qryPartValue, from tblValues
qryValueTees, from tblValueTees
qryShipping_Control, main qry
frmShipping_Control, main form with all required fields including 
Commercial Value, CargoQty, and CargoDescription.


Is there anyway this can be done.

Thanks
0
Bob
1/26/2010 7:28:27 PM
access.queries 6343 articles. 1 followers. Follow

0 Replies
638 Views

Similar Articles

[PageSpeed] 35

Reply:

Similar Artilces:

Filter: Multiple values in a cell
Sorry if this was covered here before - spent long time searching... I'm trying to use excel as a database ;) I have a very long Excel table that lists the features in th application. Easy part: I need to keep an information about the customer wh requested this particular feature and to be able to sort the list o features by the customer name. So the easiest way will be to add a column with the customer name in i and auto-filter by this column. Hard part: one feature can be requested by multiple customers. S somehow I need to put multiple customer names into one cell and stil be able ...

combo box in each cell of a column #2
Dear Frank, your suggestion works fine and solved my problem. Thanks a lot Robert -- Robert ----------------------------------------------------------------------- Roberts's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1576 View this thread: http://www.excelforum.com/showthread.php?threadid=27276 ...

Specifying greater than criteria for numerical values
I have the following SQL for a query. The query returns all values and does not limit it to those greater than 1. Anyone have any ideas??? SELECT [Ships & On Hands].UPC, [Ships & On Hands].[Item Desc 1], Sum([Ships & On Hands].[Range 2 POS Qty]) AS [SumOfRange 2 POS Qty], Count([Ships & On Hands].[Range 1 Curr Str On Hand Qty]) AS [CountOfRange 1 Curr Str On Hand Qty] FROM [Ships & On Hands] GROUP BY [Ships & On Hands].UPC, [Ships & On Hands].[Item Desc 1] HAVING (((Count([Ships & On Hands].[Range 1 Curr Str On Hand Qty]))>1)); ...

XP Themes on Forms
Sorry to re-post this, but I still need a little assistance. "thatsalok" <thatsalok@NO_gmail.com_I_DONT_NEED_SPAM> wrote in message news:%23hl9kRlFFHA.2508@TK2MSFTNGP10.phx.gbl... > You need to Enable Xp theme in your Appliation look for more info here > http://www.developer.com/net/asp/article.php/3101831 > > > -- > > With Regards > Alok Gupta > Visit me at http://alok.bizhat.com > > "I think this will Help" > "axis" <nospam@nospam.org> wrote in message > news:E42dndp3Y8E8KYvfRVn-ig@com...

Converting Text Boxed to .GIF files when exporting to web
I have a Publisher file that is converting text boxes to .gif files. Is there a way to have it convert it to something that is editable in the text\html editor? Andy wrote: > I have a Publisher file that is converting text boxes to .gif files. Is > there a way to have it convert it to something that is editable in the > text\html editor? It is converting to *.gif because you have text boxes or object that are too close or overlapping. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com ~pay it forward~ This posting is provided "AS IS" with no ...

how to get my window position in MFC?
my question : how to get my window position in MFC? thnks GetWindowRect ...

Preventing text wrapping
Hi I wasn't sure whether to post here or on the developer site, but here goes. Some of the shapes that a user can "drop" onto the page are small and the associated text tends to wrap around if the text is wider than the width of the shape. Is there any way of preventing this text wrap? Thanks for any help anyone can provide. Best regards, Marek Visio lacks the dotdotdot "Don't show all the te..." feature for it's shapes. On the other hand, you can get shapes to grow so that the geometry always encompasses the text. Open the Blocks stencil, and check out...

How to change text wrap in e-mail?
I can not figure out how to set a stationary's line length. I am trying to set the Right Margin. Can anyone help me? Thanks, John Mail format issue. Main screen, tools, options, mail format, internet format >-----Original Message----- >I can not figure out how to set a stationary's line >length. I am trying to set the Right Margin. Can anyone >help me? >Thanks, John >. > ...

Cannot get my Building Block template to show...
I have added my own template (MyTemplate.dotx) with its own specific Building Blocks into the folder: "c:\Documents and Settings\<UserName>\Application Data\Microsoft\Document Building Blocks\1033\" However, when looking at the Building Block Organizer I only see those elements that exist in Normal.dotx and Building Blocks.dotx. How can I get to see my elements from MyTemplate.dotx? I understand that it should list all Building Blocks from templates included in that folder - meaning that my elements should supplement those already in the list? Thanks Robi...

Trouble with Text Box and New Lines
I'm trying to use a textbox control a TextFormat of RTF. Whenever I input text from the body of an email into this box, the line returns disappear and I end up with one big long paragraph. For instance, text that looks like this in an Outlook email: "See the dog run." "See the cat watch." looks like this: "See the dog run.""See the cat watch." Any suggestions on how this can be corrected? Thanks, Tom S. A standard Access text box doesn't support RTF. You'll need to use an ActiveX control such as this: http://www.lebans.com/richtex...

Recall Deleted Form
I accidently deleted a very large form. By backup CD for some reason cannot be read. Is there any way I can recall a recently deleted form? Thanks Len On Thu, 12 Apr 2007 04:06:00 -0700, Len <Len@discussions.microsoft.com> wrote: >I accidently deleted a very large form. By backup CD for some reason cannot >be read. Is there any way I can recall a recently deleted form? > >Thanks > >Len Unfortunately, probably not. Note that a backup CD will have the database marked read-only - you can copy it to your hard disk and clear the Read Only property by viewing the...

Getting a "Not a Valid Win32 Application" when i try to open doc.
I am trying to open a publisher file that was sent to me. I have Publisher 95. I am getting the error message "......is not a valid win32 application" Can anyone tell me how to fix this? Publisher 95 will only open Publisher 1.0 and 2.0. Publisher 95 is a 16 bit program. Maybe your sender can convert the document to PDF. Or send it to me and I will convert it to PDF for you. I can open the 97 document but I won't be able to convert it to 95 for you. Remove "my" to reply... -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews...

Call value from table not form
I am probably overlooking something stupid here but, I need to pull a value from a table when a form is not open? Dim InternalEmail As String InternalEmail = Forms!frmCommitteeEmail!DMEmail this gives an error if the form frmCommitteeEmail is not already open. I am pretty sure that is the problem because if I add code to first open the form it works fine. Any help here will be appreciated. Thanks in advance Dave wrote: > I am probably overlooking something stupid here but, I need to pull a > value from a table when a form is not open? > > Dim InternalEmail As String > I...

New Document already filled with text before writing? WTF
Everytime I start MS Word my new document already has writing on it, how do I get rid of it. It was an ad I had composed a couple of months ago. Now everytime I open up a new document the ad is right there on the new document. I feel like I'm in the twilight zone. See http://word.mvps.org/FAQs/AppErrors/BlankDocNotBlank.htm. -- Stefan Blom Microsoft Word MVP "Bryandrew" wrote in message news:ePvAO6SyKHA.2552@TK2MSFTNGP04.phx.gbl... > Everytime I start MS Word my new document already has writing on it, how > do I get rid of it. It was an ad I had compose...

assign a number value to a text. ie, PL equals .5
I'm using different material "plate, angle, tube steel" I would like to assign a number value to that text. ie. plate would equal .5 Is there a way I can do this? Put the .5 in an un-used cell. Select the cell and then pull-down: Insert > Names > Define and enter plate then click add and click ok. You can then use the name in a formula: =plate*12.345 -- Gary's Student "JWDesigns" wrote: > I'm using different material "plate, angle, tube steel" I would like to > assign a number value to that text. ie. plate would equal .5 Is ther...

List Box Functions
I would like to create a listbox (I think this is the best thing) which gives say 5 or more different options. When the option is selected from box I want a diagram (which i mean to draw) to appear associated with the choice and certain conditions for the cells in the column to be associated with the choice. say for instance the two options in the selection box are sqaure and triangle, if i choose square then a square should appear in the row above the list box and the cell in row9 should equal row10. Cheers ------------------------------------------------ ~~ Message posted from http://w...

Questions On Calling Graphics via Cell Value
Thanks to a tip from Dave Peterson, he directed me to a site that has example code which I've included. There's also a link a the bottom of the referenced page to download a sample worksheet however it doens't appear to be working. (hangs) Here's the reference: http://www.mcgimpsey.com/excel/lookuppics.html Here's the code: Private Sub Worksheet_Calculate() Dim oPic As Picture Me.Pictures.Visible = False With Range("F1") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible...

can text be inverted by 180deg
Hi, Is it possible to invert text by 180 deg. Under Format - cells you onl have the option of 90 deg -- trye ----------------------------------------------------------------------- tryer's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1654 View this thread: http://www.excelforum.com/showthread.php?threadid=39979 from my archives sometime back i used this formula =OFFSET($B$1,MATCH($B$16,B1:B16,0)-1,0) my original data is in B1 to B16 I copied this to any cell and copy down try this. may be easier solutions available tryer <tryer.1ugkia_1125205502.54...

move a row of data values to a column in sorted order
Does anyone have a method of moving a row of random values to a separate column on the speadsheet in numerical order and capturing the heading fields sot that they are matched. Expample... A B C D 232 121 88 412 --- this is my row data column this is how I want to reformat the row data on the spreadsheet 88 - C 121 - B 232 - A 412 - D Hi, 88 - C Is that all in one cell? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answer...

Cell value change runs a macro?
Is there a way to make it so that if a cell value changes, a macro is called? -- Steach91 ------------------------------------------------------------------------ Steach91's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30234 View this thread: http://www.excelforum.com/showthread.php?threadid=553053 Sure You use a Worksheet_Change() event. To access this press alt+f11 to access the VBE, right click on the worksheet concerned and select view code... In the left drop-down in the resulting window select worksheet and in the right one select Change. You wil...

Getting Windows Installer when trying to open Excel files
I have suddenly begun experiencing a problem when trying to open Excel files or the application itself. I get Windows Installer saying it's trying to load Office XP Small Business, and asking for the CD. I don't have the CD, as my PC came loaded with Office already on it. When I subsequently click "Cancel", I get an "Error 1706... setup cannot find the required files". I have not done anything new to my PC, and suddenly this problem. What gives? Any help would be appreciated. Thanks in advance, AMC Contact the company who sold you the PC, I don't thin...

How disable an Excel dialog box?
My entire spreadsheet is locked/password protected in such a manner that users cannot select any locked or unlocked cells. However, if a user double clicks on any cell, Excel will display a dialog box that says the following: "The cell or chart you are trying to change is protected and therefore read-only!...." etc, etc.... Is it possible to disable this particular dialog box so I can display my own custom userform instead?? Can I somehow place some code in the "Worksheet_BeforeDoubleClick()" event subroutine to tell Excel to display my own cu...

Open form
Hi, I have an unbound form 'fTTSwitchboard' that has two combo boxes - one lists tech names 'CboTech' and the other lists job numbers 'CboJobNumberSelect. There is also a subform for list of jobs assigned to CboTech. The JobNumberSelect combo box lets them enter a job number that is not on their list and a form 'fGeneralInfoTT' opens giving specific about job with a subform 'fWorkLog' that has a combo box 'CboTech' where they can select their name and text boxes to enter start and stop times. Everything works well now, but I would like to...

Listbox to open a form (primary key text)
Hi, I have created by ID field to except text. The ID field is a primary key. With this primary key I have created a listbox. Now, through the listbox I would like to select the ID field and open the form for only that ID. However when I am using the code below, it shows a message saying Syntax error in string in query expression '[ID]=""& Me![ID]'. Private Sub Command8_Click() On Error GoTo Err_Command8_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "Issue - General" stLinkCriteria = "[Issue #]=" & ...

Color in Check Box
Is there some way to change the color of the check box area that has the check mark (not the labeling or caption area)? -- Bill @ UAMS ...