Can't get code to work

[Roly Reefer]

I am learning VBA and have some code that i cant get to work. When the code runs on the combo box it doesn't pick up any of the values where it should. The code is definitely correct and i have tried a few things to get it working but nothing changes. In the four textboxes, avg, sum min, max 0 is always showing, despite showing up in the query as having sales. You can change that by removing the NZ part and before that it was coming up with an invalid use of null error.


Private Sub cboProductName_AfterUpdate()
'code to declare variables for the procedure
Dim strCriteria As String, strFieldName As String
Dim strFieldValue As String, strDomain As String
Dim strDomainField As String, sngAverage As Single


'code to assign values to declared variables
strFieldName = "ProductName"
strFieldValue = "cboProductName"
strDomain = "qryOrderDetails"
strDomainField = "Quantity"
strCriteria = strFieldName & "=" & Chr(34) & strFieldValue & Chr(34)


'code to calculate statistics using the domain aggregate functions
lblSum.Caption =Nz( DSum(strDomainField, strDomain, strCriteria), 0)
sngAverage = Nz(DAvg(strDomainField, strDomain, strCriteria), 0)
lblAvg.Caption = sngAverage
lblMax.Caption = Nz(DMax(strDomainField, strDomain, strCriteria), 0)
lblMin.Caption = Nz(DMin(strDomainField, strDomain, strCriteria), 0)



End Sub

Thanks very much for any help

 

[pbaldy]

Have you set a breakpoint and watched the code step by step, so you could examine the variables to make sure they return what you think they should?

 

[Roly Reefer]

I have tried setting some break points but nothing changes - it stops at every line highlighted in yellow. i am not sure really how to use this feature.

 

[pbaldy]

Some tips here:

http://www.baldyweb.com/Debugging.htm

Can you post the db?

 

[Roly Reefer]

I still cant find anything wrong with the code - when i run with breakpoints it just stops where they are. But still have 0 values for everything.

 

[Brianwarnock]

shouldn't
strFieldValue = "cboProductName"
be
strFieldValue = me.cboProductName

in order to get the value of rather than the name of the combo box.

Brian

 

[Roly Reefer]

Spot on Brian - thanks a lot. Thanks aswell baldy

 

[SOS]

shouldn't
strFieldValue = "cboProductName"
be
strFieldValue = me.cboProductName

in order to get the value of rather than the name of the combo box.

Brian

I couldn't see any place where strFieldValue is being used in this code at all anyway.

 

[pbaldy]

The way to have used the debugging tool would have been to either hover over strCriteria when the code was beyond that point, or typed

?strCriteria

into the Immediate window. Either would have pointed you to your problem.

SOS: it's there.

 

[SOS]

SOS: it's there.

I see it now, but I looked over and over and didn't see it then. I think I need to get some new glasses, eyes, or something. Maybe a vacation would work, although I can't take one.

Maybe a nap would be good (although my boss might not like me taking one right about now )