I have a textbox in report 'rtpTutorSummary', whose value is based, in some part, on the value of a textbox ('countofcandidatecode') in its subform 'rptSubGroupCompleted'. I need to get the value of this textbox but im not sure how to reference it:

txtStatus.Value = [Reports]![rptSubGroupCompleted]![CountOfCandidateCode].Value

doesnt work, report not open

txtStatus.Value = [Reports]![rptTutorSummary]![rptSubGroupCompleted]![CountOfCandidateCode].Value

doesnt work, report not open

The subform is called chdGroupCompleted:

txtStatus.Value = Me.chdGroupCompleted!CountOfCandidateCode.value

Doesnt work either.

I must be able to reference the info, surely. Anybody help me out with the syntax?

Cheers

It's


The .Value property is not needed. The Me. is a shortcut to the main report's name.


Edited by: SerranoG on Wed Apr 20 8:39:03 EDT 2005.

Thanks for that greg, i had to refer to the report by its subreport name (chdGroupCompleted), rather than the reports filename (rptSubGroupCompleted):

txtStatus = Me.chdGroupCompleted.Report.CountOfCandidateCode

but otherwise it worked perfectly.

I still have a little problem, as the textbox in chdGroupCompleted is based on a count, when the count is zero, the integer zero is not returned but a null. so when i run it against a 0 count i get "You entered an expression that has no value" error box. ive tried

if txtStatus = Me.chdGroupCompleted.Report.CountOfCandidateCode = null then

then assigning a variable to zero, but this doesnt work, i still get "You entered an expression that has no value".

Any ideas?

Use the IsNull function

That doesnt seem to work either, ive got (few var names changed since last post):

stillActive = 0
If Not IsNull(Me.chdGroupStillActive.Report.CountOfCandidateCode) Then
stillActive = Me.chdGroupStillActive.Report.CountOfCandidateCode
End If
txtStatus = stillActive

But i still get "You entered an expression that has no value" for zero-counts (error on line "stillActive = Me.chdGroupStillActive.Report.CountOfCandidateCode").

Your code can be condensed by using the Nz function


Put the code in the report's detail section's ON FORMAT event, not on report header's ON FORMAT or the report's ON OPEN event, etc.

thanks for all your help greg, but i tried that, in right place and *still* got error "You entered an expression that has no value" (i dont see how isnull, nz et al dont catch this)

Anyway, a somewhat longwinded fix, a use ADO to search the query, if its gonna return null i manually set status to zero without running problem code:

Dim stAppName As String

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open ("Select * from qryGroupStillActive")
strSQL = "[GroupCode] = " & Me.GroupCode

rst.Find strSQL
If rst.EOF Then
Me.txtStatus = "0"
Else
Me.txtStatus = Nz(Me.chdGroupStillActive.Report.CountOfCandidateCode, 0)
End If
rst.Close
Set rst = Nothing

Not the nicest solution on earth, but it works.

When a report opens, not all the fields get populated into every "nook and cranny", i.e. every control element immediately. Therefore, if you try to reference a textbox in your detail section in your ON OPEN event or the header's ON FORMAT event it would produce that error because the value for that textbox based on that field has not been populated yet. That's why I said use the code in the subform's detail section's ON FORMAT event.

But if your works that's great! Note, what you wrote, however...


Do you want Me.txtStatus to be the string "0" or do you want it to be the actual number zero? The first part of the IF statement sets it to the string whereas the second half sets it to the number. Either change the first half to Me.txtStatus = 0 or change the Nz to Me.txtStatus = Nz(Me.chdGroupStillActive.Report.CountOfCandidateCode, "0") to be consistent with your wishes.