Msgbox displaying a value in the string

[isaacski]

Hi All,
I have a report with a subreport that are not linked. The subreport is specifically for summary purposes of another value gathered from the master table. The subreport states a supervisor's name and lets me know a number (the # is a count field that updates as user's put in data). When this main report opens I would like to program VBA to check if there is a value equal to 5 in this report and let me know which supervisor it is that is reporting at 5. I'm familiar with the general VBA for If then and msgbox, but I'm not sure how to reference a field in a subreport, nor do I know how to have a message box report something instead of just include what I write in the parentheses...Any thoughts?

Supervisor A - 3
Supervisor B - 4
Supervisor C - 5

On report open, Msgbox says, Supervisor C has 5 evaluations completed, and a vbokonly

I would appreciate any help on this!

Thanks,

Kim

 

[James Dudden]

You could open a recordset as the report opens that checks for a supervisor with a count of 5. Something like this:

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT .* FROM [TableName] WHERE CountNo = 5", dbOpenDynaset)
If rs.RecordCount <> 0 Then
rs.MoveFirst
Msgbox "Supervisor " & rs![SupervisorField] & " has 5 evaluations completed"
End If
rs.Close
Set rs = Nothing

NB: The exact SELECT statemnt needed will depend on your tables/fieldnames etc and you will need to replace teh SupervisorField with teh actual field name.

 

[eblieb]

You can also do something like


Do Until rs.EOF
for each x in rs.Fields
Msgbox "Supervisor " & rs![SupervisorField] & " has 5 evaluations completed"
next
rs.MoveNext
loop

That may not work as written but it is a general loop you can do to get more than the first record.