Running a Query in VBA 1

[rhnewfie]

I want to run a query against my current database from a vba module behind a report I am creating, is this possible?? I have tried the traditional methods but of course I get the "Database has been locked by blah blah" message. If someone could point me in the right direction it would be greatly appreciated.

Oh, I want to run a "Select count(fieldname) from tablename where id = " & me.id

where me.id is the id number that is currently being processed for the report.

Thanks in Advance

RHNewfie

 

[JoeMiller]

How about using the DCount function to get what you're after?

DCount("[FieldToCount]","TableName","[Criteria]=[Criteria]&quot

HTH Joe Miller
joe.miller@flotech.net

 

[rhnewfie]

Hey Joe

This actually stems from the problem you were trying to help me with over on Access Reports. I am using Count and DCOUNT side by side on my report footer, count gives me a count of all the outlets for an owner in an area and dcount is SUPPOSED to give me a count of all the outlets with a special feature for an owner in an area, the count works but dcount gives me a count of all oulets with a special feature for all owners in the area.

ie/ there are 125 outlets with the feature
one owner has 16 outlets, 3 with the feature.
Count gives 16
Dcount gives 125

The report is ordered by owner so only outlets for that owner are processed at one time. I want to run the query because I know that I can get the count correctly from the table that the report is based on.

Thanks Again

 

[JoeMiller]

Sounds like you need multiple criteria in your DLookup to restrict the records further. You can use AND / OR in criteria (just like in a query) to further restrict data in a DCount function.

DCount("[FieldToCount]","TableName","[Criteria]=[Criteria] AND [ID]=[ID]&quot Joe Miller
joe.miller@flotech.net

 

[rhnewfie]

That sounds good to me and I have tried it but I never knew how to specify [id](from the table) = [id] (currently going through the report) in the expression builder. Is there a way to do this? Oh, and is there some reason why I shouldn't run a query from the module? I wouldn't want to do something that increases the time to load a report by too much, it's slow enough as it is!!!

Thanks Again
RHNewfie

 

[JoeMiller]

To specify the id you need to reference an ID on your report. So if you have txtID your criteria would be:

[ID]=[txtID]

You can also hard code the ID if you know what it's going to be ahead of time. If it's a number just put the number in:

[ID]=123

If it's text (or alphanumeric) you need to use single quotes:

[ID]='ABC'

The Count field that runs the query, replace that with a DCount as well but put NO criteria in and you'll get ALL records in that table. Just make sure you select a field that is unique to count on if possible (or at least always filled in).

DCount("[MyField]","MyTable&quot


Does that help?
Joe Miller
joe.miller@flotech.net

 

[rhnewfie]

Thanks Joe, worked like a charm, I put in a hidden text box that held the id and referenced that in the expression, never thought of that before, thanks for helping.

RHNewfie