query to text box (brain fart?)

[madEG]

Hello,

I can't for the life of me see what is wrong with this. arg!

I have a report on which I wish to display values from separate queries.

e.g. On the textbox named: "txtTotalMembers" I wish to value with the result of the query

SELECT count(*) AS ["TotalMembers"]
FROM qryListMembersDistinctMemberNumbers;

I've tried in the control source for the textBox "txtTotalMembers":

=DLookup("TotalMembers","qryTotalDistinctMembers")



Before I even tried the dlookup I tried, VBA 'on open' (and tried on activate, too) for the report:

    Dim strSQL As String
    Dim txtTotalMembers As String

    strSQL = [queries]![qryTotalDistinctMembers]
    Set rs = New ADODB.Recordset
    rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    
             txtTotalMembers = rs("TotalMembers").Value
    
    rs.Close
    Set rs = Nothing

...but both the dlookup shows an #Error and the VBA results are that the textbox is blank showing nothing at all.


What gives? Why do I stink at reports so bad? (Ok, let's let that second question go for now...)

Ideas?

-Matt G.

 

[boblarson]

1. The SQL doesn't need the square brackets nor the quotes:

SELECT count(*) AS TotalMembers
FROM qryListMembersDistinctMemberNumbers;

Second, this should work once you have that fixed:

=DLookup("TotalMembers","qryTotalDistinctMembers")

 

[DCrake]

Answer

Me.Results = DCount("*","qryListMembersDistinctMemberNumbers")

 

[madEG]

I have a value... woop di doo!

Thanks!