select statement results msgbox

[Guest]

I would like to do a select count statement and display the results in a
msgbox.
Is that possible?

ex
SQL = " Select count(StoreNum) " & _
"FROM GBPEXCFMASTERFLIFLC INNER JOIN TRANSFER_MEMBER " & _
"ON GBPEXCFMASTERFLIFLC.StoreNum = TRANSFER_MEMBER.CMCUNO " & _
"WHERE (((GBPEXCFMASTERFLIFLC.StoreNum)<>'0266' " & _
"And (GBPEXCFMASTERFLIFLC.StoreNum)<>'0673' " & _
"And (GBPEXCFMASTERFLIFLC.StoreNum)<>'0306' " & _
"And (GBPEXCFMASTERFLIFLC.StoreNum)<>'3060')) "

DoCmd.RunSQL (SQL)

msgbox (" 101 records will be deleted, do you want to delete these records?")

Where the 101 would be the result of the above select statement.

Or is there a better approach?

Any suggestions would be greatly appreciated.

 

[Guest]

RunSQL is only for Action (Append, Update, Delete) queries.
To do what you want I would suggest you create a stored query that looks
like what you posted. When you run the query, it will change the name of the
field StoreNum to CountOfStoreNum. Now, all you need to do is use the
DLookup function to return the value:

Assume you saved the query as qselStoreCount
=DLookup("[CountOfStoreNum]", "qselStoreCount")

 

[John Spencer]

Dim LCount as Long

SQL = " Select count(StoreNum) " & _
"FROM GBPEXCFMASTERFLIFLC INNER JOIN TRANSFER_MEMBER " & _
"ON GBPEXCFMASTERFLIFLC.StoreNum = TRANSFER_MEMBER.CMCUNO " & _
"WHERE (((GBPEXCFMASTERFLIFLC.StoreNum)<>'0266' " & _
"And (GBPEXCFMASTERFLIFLC.StoreNum)<>'0673' " & _
"And (GBPEXCFMASTERFLIFLC.StoreNum)<>'0306' " & _
"And (GBPEXCFMASTERFLIFLC.StoreNum)<>'3060')) "

LCount = CurrentDb().OpenRecordset(StrSQL).Fields(0)
MsgBox LCount & " records will be deleted, do you want to delete these
records?"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..