RunSQL error message- help

[Toeying]

Hello All,
I need some help. I am trying to run the following code and I keep getting the error message " A RunSql action requires an argument consisting of an SQL statement". I have no idea why its not recorgnising strSQL as an SQL statement

strSQL = "SELECT COUNT(*) FROM HSMum WHERE [MumHSReferenceNumber]=" & List2.Value & ";"
Call DoCmd.SetWarnings(False)
Call DoCmd.RunSQL(strSQL)
Call DoCmd.SetWarnings(True)

Thanks for your help.

 

[Galaxiom]

Set a break point and check the value of strSQL.

 

[JANR]

RunSql only works for action queries, not SELECT queries.


edit:

Use Dcount instead

JR

 

[Toeying]

I have done that ant the value is as follows

strSQL ="SELECT COUNT(*) FROM HSMum WHERE [MumHSReferenceNumber]=HS111;"


note: HS111 is the current value of List2.Value in the original statement

thanks for your help

 

[Toeying]

HMM Hello JANR, how then can I know the number of records affected by a select query? I thought it was the db.Execute that doesnt work with SELECT?
Anyway, what I want to do is find the number of records in a table that satisfy a particular criteria. how do i do that? Thanks
TY

 

[JANR]

Well like I said use DCount function

DCount("*", "HSMUm", "[MumHSReferenceNumber]= '" & Me.List2 & "'")

JR

 

[Galaxiom]

As JANR says you can use a DCount.

However do avoid the common tempation to use the apparent simplicity of the Domain functions to retrieve values for each of multiple controls with information from a table. Each domain function is in esence a query. It opens a recordset and retrieves a value.

Where multiple values are required it is much, much faster to put the controls in a subform and retrieve their values with a single query as their recordsource. Unless you are retrieving a single value always try to use a query as the recordsource with bound controls or a recordset to populate multiple controls via VBA.

 

[Toeying]

Ok, didnt see your edit. thanks a lot. works perfectly now. you are the bomb!!