Query not recognizing form value (1 Viewer)

utzja1 · Jun 11, 2013

I have a query that serves as the data source for a report. Previously, I had a unit cost value that the User entered as a parameter, but I wound up adding an unbound text box on my form so that I could validate the input before executing the query. So now, the parameter is coming from a form. The text below is from my query, but Access still pops up with a parameter box as though Expr3 is undefined.

Expr3: [Forms]![BridgeRptsF]![CostSF]

I have verified that the field name on the form is correct, and whenever I try to run the report, I do it from the [BridgeRptsF] form and I make sure the CostSF field is populated. I feel like this is a pretty straightforward fix but I've not stumbled upon it yet.

All suggestions appreciated.

pbaldy · Jun 11, 2013

You want the name of the textbox, if different. Is it asking for the form or Expr3?

billmeye · Jun 11, 2013

Access never lies. If it can't resolve a name something must be entered incorrectly. Do you want to post your form/query/report?

utzja1 · Jun 11, 2013

pbaldy said:
You want the name of the textbox, if different. Is it asking for the form or Expr3?

It's just asking for Expr3. I went back and double-checked the name of the text box and it matches.

utzja1 · Jun 11, 2013

utzja1 said:
It's just asking for Expr3. I went back and double-checked the name of the text box and it matches.

Here's an excerpt of the SQL. Thanks for your time.

SELECT NbiBridge.StateStrNum, [Forms]![BridgeRptsF]![CostSF] AS Expr3, NbiBridge.CurrNbiDeck, NbiBridge.CurrNbiSuper, NbiBridge.CurrNbiSub, MaintItemsT.MaintItem, MaintItemsT.ItemDate, Sum(([MaintItemsT]![Quant]*[MaintItemsT]![UnitCost])) AS Expr1, ConditionRtgNames.RtgName, ConditionRtgNames.RtgDescr, ConditionRtgNames_1.RtgName, ConditionRtgNames_1.RtgDescr, ConditionRtgNames_2.RtgName, ConditionRtgNames_2.RtgDescr, NbiBridge.TotBridgeLength, [TotBridgeLength]*[NbiBridge]![BridgeWidth] AS Expr4, [TotBridgeLength]*[NbiBridge]![BridgeWidth]*[Expr3]*0.3 AS CostRedeck, [TotBridgeLength]*[NbiBridge]![BridgeWidth]*[Expr3]*0.75 AS CostSuper, [TotBridgeLength]*[NbiBridge]![BridgeWidth]*[Expr3] AS CostReplace, NbiBridge.BridgeWidth, MaintTotalQ.Cost, "http://maps.google.com/maps?q=" & [CoordX] & "," & [CoordY] & "&iwloc=A &hl=en" AS MyCoord, [MaintItemsT]![Quant]*[MaintItemsT]![UnitCost] AS Expr2
FROM ConditionRtgNames INNER JOIN ((ConditionRtgNames AS ConditionRtgNames_2 INNER JOIN (ConditionRtgNames AS ConditionRtgNames_1 INNER JOIN (MaintItemsT RIGHT JOIN NbiBridge ON MaintItemsT.StrNum = NbiBridge.StateStrNum) ON ConditionRtgNames_1.ElementRtg = NbiBridge.CurrNbiSuper) ON ConditionRtgNames_2.ElementRtg = NbiBridge.CurrNbiSub) LEFT JOIN MaintTotalQ ON NbiBridge.StateStrNum = MaintTotalQ.StateStrNum) ON ConditionRtgNames.ElementRtg = NbiBridge.CurrNbiDeck
GROUP BY NbiBridge.StateStrNum, NbiBridge.CurrNbiDeck, NbiBridge.CurrNbiSuper, NbiBridge.CurrNbiSub, MaintItemsT.MaintItem, MaintItemsT.ItemDate, ConditionRtgNames.RtgName, ConditionRtgNames.RtgDescr, ConditionRtgNames_1.RtgName, ConditionRtgNames_1.RtgDescr, ConditionRtgNames_2.RtgName, ConditionRtgNames_2.RtgDescr, NbiBridge.TotBridgeLength, [TotBridgeLength]*[NbiBridge]![BridgeWidth], [TotBridgeLength]*[NbiBridge]![BridgeWidth]*[Expr3]*0.3, [TotBridgeLength]*[NbiBridge]![BridgeWidth]*[Expr3]*0.75, [TotBridgeLength]*[NbiBridge]![BridgeWidth]*[Expr3], NbiBridge.BridgeWidth, MaintTotalQ.Cost, "http://maps.google.com/maps?q=" & [CoordX] & "," & [CoordY] & "&iwloc=A &hl=en", [MaintItemsT]![Quant]*[MaintItemsT]![UnitCost], [Expr3], NbiBridge.CoordX, NbiBridge.CoordY
HAVING (((NbiBridge.StateStrNum)=[Forms]![BridgeRptsF]![SingleBridgeID]))
[FONT=&quot]ORDER BY Sum(([MaintItemsT]![Quant]*[MaintItemsT]![UnitCost])) DESC;[/FONT]

pbaldy · Jun 11, 2013

You can't use Expr3 in other clauses, you need the form reference there.

pr2-eugin · Jun 11, 2013

Change the Group by on the fields that involve Expr3 to Expression..

utzja1 · Jun 11, 2013

pbaldy, you strike again. That was the problem; the report populates just fine now.

pbaldy · Jun 11, 2013

Happy to help.

utzja1 · Jun 12, 2013

OK, almost completely buttoned up but something is giving me fits on the report. In the SQL statement for the Select query, I changed the references from Expr3 to the forms reference, and all the calculated field that use this value are coming in correctly. IOn the report, I included a textbox to show what value the User input ("These numbers are based on an assumed cost of ....."). I have the data source for the text box as Expr3, and it consistently shows up blank. If I change Expr3 to a constant value (say, 150), it shows up on the report, so it has to be something screwy in the syntax as to how the form's field value is referenced. Any thoughts?

pbaldy · Jun 12, 2013

Odd; if the query has an aliased field named Expr3, having that as the control source of a textbox should work. Can you post the db here?

utzja1 · Jun 12, 2013

Sadly, database is too big to post to the site.

If I right-click on the query or report, I get prompted for the values from the form as though they are parameters. When I enter them, the UnitCost value shows up in the report.

When I use the macro-button to launch the report, the query that's launched correctly displays the calculated fields using the value from the form. The value for Expr3 (defined in the query as Expr3: [Forms]![BridgeRptsF]![UnitCost]) does not display in the report.

I'm beginning to think it may be a syntax issue with the how the report is called from VBA. Here is the code for that:

Private Sub ReportButton_Click()

Dim NumRecord As Integer

NumRecord = DCount("[ItemNum]", "Table", "[ItemNum] = " & Forms!BridgeRptsF!ItemNum)

If NumRecord = 0 Then
MsgBox "Item ID not in database. Please check and re-enter."
Forms!BridgeRptsF!ItemID = Null
Forms!BridgeRptsF!UnitCost = Null
Else
DoCmd.OpenReport "SingleBridgeR", acViewReport, , , acWindowNormal

End If

End Sub

utzja1 · Jun 12, 2013

OK, here's a head-scratcher for the more knowledgeable amongst us. In the Design View of the source query, I changed the definition for Expr3 from:

Expr3: [Forms]![BridgeRptsF]![CostSF1]

to

Expr3: [Forms]![BridgeRptsF]![CostSF1]*1

Now, Expr3 is a calculated field, and reports for duty every time I launch the report from the macro-button. Why it didn't work before is beyond my meager comprehension of Access, but I am satisfied that multiplying by unity doesn't punch a hole in the space-time continuum. If anyone has an idea why the first definition didn't work, it would go a long way towards satisfying my curiosity.

pbaldy · Jun 12, 2013

That is weird. No idea why it works, but I'm already starting to feel the pull of a singularity opening up. I think you have punched a hole in the space-time continuum.

Query not recognizing form value (1 Viewer)

utzja1

Registered User.

pbaldy

Wino Moderator

billmeye

Access Aficionado

utzja1

Registered User.

utzja1

Registered User.

pbaldy

Wino Moderator

pr2-eugin

Super Moderator

utzja1

Registered User.

pbaldy

Wino Moderator

utzja1

Registered User.

pbaldy

Wino Moderator

utzja1

Registered User.

utzja1

Registered User.

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread