DCount problem

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Using Access 2002, I am using the following code to set the source for a
subform

Dim strSQL As String

strSQL = "SELECT * From UnInvStockQ"
Me![StockSub].Form.RecordSource = ""
Me![StockSub].Form.RecordSource = strSQL

That works OK.

What I want to achieve is to use DCount to count the number of records found
and for that number to be shown on the main form

Any advice would be appreciated

tom
 
So what's the problem? You should just be able to have an unbound text box
and do something like..

me.controlname=dcount("*", "UnInvStockQ")
 
Hi Tom,

put a textbox on the form, and then make the put the following line in
the control source for the textbox

=DCount([yourprimarykey],"UnInvStockQ")

or the long way

build a sql statment like 'Select count(*) as Total from UnInvStockQ"
and open it with DAO/ADO etc.
Then pass the value of the total field to the textbox or label

Of course in both instances you may want to filter the result e,g,

'Select count(*) as Total UnInvStockQ where x=1'


=DCount([yourprimarykey],"UnInvStockQ","x=1")

Hope it helps.

Alex
 
Hi Rob & Alex

Thanks for your input.

Is there any way of getting a count figure from the existing strSQL? It is
likely that various different filters are going to be applied to the
UninvoicedStockQ and instigated via a case select statement, that strSQL,
being the recordsource for the subform, would therefore already have a
filtered result and only requiring 1 line of code for a count value
irrespective of which filter had been applied.

Tom
 
Hi Rob & Alex

Thanks for your input.

Is there any way of getting a count figure from the existing strSQL? It is
likely that various different filters are going to be applied to the
UninvoicedStockQ and instigated via a case select statement, that strSQL,
being the recordsource for the subform, would therefore already have a
filtered result and only requiring 1 line of code for a count value
irrespective of which filter had been applied.

Tom
 
You can always return the number of records on a subform by using...

Me.SubformControlName.Form.Dynaset.RecordCount


Tom said:
Hi Rob & Alex

Thanks for your input.

Is there any way of getting a count figure from the existing strSQL? It is
likely that various different filters are going to be applied to the
UninvoicedStockQ and instigated via a case select statement, that strSQL,
being the recordsource for the subform, would therefore already have a
filtered result and only requiring 1 line of code for a count value
irrespective of which filter had been applied.

Tom

Alex said:
Hi Tom,

put a textbox on the form, and then make the put the following line in
the control source for the textbox

=DCount([yourprimarykey],"UnInvStockQ")

or the long way

build a sql statment like 'Select count(*) as Total from UnInvStockQ"
and open it with DAO/ADO etc.
Then pass the value of the total field to the textbox or label

Of course in both instances you may want to filter the result e,g,

'Select count(*) as Total UnInvStockQ where x=1'


=DCount([yourprimarykey],"UnInvStockQ","x=1")

Hope it helps.

Alex
Click to expand...
Click to expand...
 
Back
Top