DAP: How do I display ad-hoc calculations?

[SMerrill]

Environment: SQL Server 7, MS Access XP Data Access Pages

Background: I have a main DAP page with a command button on it that brings up a popup form.
The popup form is bound to child records, i.e. records that are on the Many-side of the relationship with the main.

Request: My user would like me to display the count of child records in parenthesis, in a read-only text place like the button caption. So the button caption should look like "Child Records (3)"

Problem: I'm somewhat stumped as to how to make an ad-hoc query in the middle of a DAP, that queries a recordset is nearly irrelevant to the DAP it is on.

 

[TCVance]

Something you might try is to make a read-only text field and have it equal to "Child (xxx)". Where "xxx" is the value of a dCount function.

xxx = dCount("[fld1]","tbl1","key1 = key2&quot
fld1 = just a field in the many record table.
tbl1 = many record table.
key1 = the field in the many record table that relates
to the main table.
key2 = field in the main table that relates to the
many record table.

Hope this helps.

 

[SMerrill]

Thanks for your response: I figured out a solution. My basic confusion originated from not knowing how to tie the query into the MSODSC object within the DAP. Here is my solution for running ad-hoc queries against the database from the DHTML client:

private sub MSODSC_Current(dscei)
dim cnn  
dim rst  
dim sCount  
set cnn = MSODSC.Connection  
set rst = cnn.execute("SELECT count(*) from Child WHERE ParentKey= '" & ParentKeyField.value & "'")  
if not rst.eof then	
  sCount= rst(0)  
else    
  sCount= "0"  
end if  
set rst = nothing  
set cnn = nothing  
cmdChildButton.innerHTML = "ChildButton Caption (" & sCount & ")"
end sub

--Shaun Merrill