Assistance Needed Retrieving Record Data

[dragonmanUSMC]

I am having great difficulty retrieving data from records that are selected via a drop down list.

I am going to try and explain the relationships that I have in this DB.

First we are talking about data in two tables
1. Order Details
2. Products

Second we have two forms and two subforms
Forms
1. Order Details
2. Products
Subforms
1. Order Details Subform
2. Products Subform

I am trying to autofill an unbound field named QtyOnHand that resides in the Order Details Subform. When the user selects the Product in the Order Details Subform via a drop down list various info will autofill correctly. Price, Sales Price, etc... However the QtyOnHand Field displays ?NAME but the QtyOnHand field in the Products form displays correctly.?

To properly understand what is happening I need to explain what I did on the Products Form and Products Subform. On the Products form the QtyOnHand field (unbound) is getting its value based off of a calculation that is being calc. in the Products Subform.

I have two fields in the Products Subform named UnitsOrdered and UnitsSold using those two fields and the data they contain I have an unbound field 'QtyOnHand=Sum(nz([UnitsReceived])-nz([UnitsSold]))'

Only the Form Products is displaying the QtyOnHand=[Products Subform].[Form]![UnitsOnHand] correctly I need help getting this data to display in my Order Details Subform. Any help will be graciously accepted!

 

[BoxHead]

Take a look at DSum() in Help. You'll have to specify the product for which you are totaling by refering to the appropriate control on the subform.


HTH


John





When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[dragonmanUSMC]

Thank you for the reply, I will try your suggestions and post back with any new info.

Best regards

 

[dragonmanUSMC]

Well I'm afraid I'm still as lost as I was before, any other advise offered would be great. In the mean time I'm going to continue trying to work this out.

 

[BoxHead]

What do you mean by "lost"?

Is the issue with the structure and syntax of the DSUM() funtion or somethng else?

You have to be sure to use the correct references for the objects (not fields) on your form and subform. Like Forms!FormName!SubFormName!TextboxName

EG: Set ControlSource of the unbound textbox to

DSum("[NameOfQtyOnHandField]","NameOfTable","[NameOfFieldHoldingProductName] = '" & Forms!TheMainFormName!TheSubformName!TheNameOfTheTextboxHoldingTheProductName] & "'")
- DSum("[NameOfQtySoldField]","NameOfTable","[NameOfFieldHoldingProductName] = '" & Forms!TheMainFormName!TheSubformName!TheNameOfTheTextboxHoldingTheProductName] & "'")

HTH