Open Query result want to hide

[rmayur]

"DoCmd.OpenQuery "q_RemovedItems" this is a command line. "q_RemovedItems" is a Select query. At the time of execution it showing its view but I want to hide it's view.

[pbaldy]

Why are you opening it at all?

[rmayur]

for execution. is there any other method to execute it. The working method is-
First step data from excel sheet imported in one table.
In second step of sorting these select query will fire on the respective table where data imported from excel is present.

[June7]

I repeat Paul's question - why open a SELECT query if you are just going to hide it?

[rmayur]

It is used to filter out some data from table by using where clause for condition.

[June7]

That still doesn't explain why you want to open then hide. What purpose does it serve to open a SELECT query if can't view the data on it?

If the query is used as the RecordSource for a form or report, there is no reason to open the query, just open the form or report.

[rmayur]

That select query used to remove the some records whose some fields are blank. Where clause used for condition

Dim str1 As String
str1 = "SELECT count(*) as counts FROM ImportDailyRedum;"
DoCmd.SetWarnings False
Set rst = CurrentDb.OpenRecordset(str1)
TotalRecords = rst!counts
rst.Close
Set rst = Nothing

DoCmd.OpenQuery "q_RemovedItems"

[June7]

Why did you post this code? It does not clarify the situation. Opening and closing a recordset in VBA has nothing to do with opening a query object.

SELECT query does not remove records - records are still in the table - query just filters the records.

What is TotalRecords - a variable or a field?

SetWarnings is not needed because not running an action SQL (UPDATE, DELETE, INSERT).

Should always SetWarnings back to True after completing procedure.

[drunkenneo]

Is that you want to executing delete statement from a saved query, but don't want the query to open for user?

Why recordset is used?

[rmayur]

yes.. executing delete statement. the result data I will shift into another table. I don't want query to open for user.

I will explain again..

I have imported data in one table. I want to delete some data from the table. For that purpose I write one select query named as "q_RemovedItems". I write the code line below -

"DoCmd.OpenQuery "q_RemovedItems"

At the time of execution Query get opened which I don't required. that query is saved in list of queries of access object.

[drunkenneo]

yes.. executing delete statement. the result data I will shift into another table. I don't want query to open for user.

I will explain again..

I have imported data in one table. I want to delete some data from the table. For that purpose I write one select query named as "q_RemovedItems". I write the code line below -

"DoCmd.OpenQuery "q_RemovedItems"

At the time of execution Query get opened which I don't required. that query is saved in list of queries of access object.

SELECT query is not for deleting the records, its for selecting. you need to make a delete query if you want to remove. What statement you have used in "q_RemovedItems" SELECT or DELETE?

[rmayur]

SELECT ImportDailyRedum.[Transaction Date], ImportDailyRedum.[Admin Site Order Id], ImportDailyRedum.[New Member Site Order ID], ImportDailyRedum.[Reward Title], ImportDailyRedum.[Variation SKU], ImportDailyRedum.[Variation Title], ImportDailyRedum.[Redemption Partner], ImportDailyRedum.[Redemption Category], ImportDailyRedum.[Unit Point Cost], ImportDailyRedum.[Redemption Quantity], ImportDailyRedum.[Points Redeemed on Reward], ImportDailyRedum.[Fulfilment Method], ImportDailyRedum.[Redemption Status], ImportDailyRedum.[Additional Field], ImportDailyRedum.[Business ID], ImportDailyRedum.[Business Name], ImportDailyRedum.[Contact First Name], ImportDailyRedum.[Contact Last Name], ImportDailyRedum.[Address 1], ImportDailyRedum.[Address 2], ImportDailyRedum.[Address 3], ImportDailyRedum.[County/State], ImportDailyRedum.[Postcode/Zip], ImportDailyRedum.Country, ImportDailyRedum.[Contact Login], ImportDailyRedum.[Contact Email Address], ImportDailyRedum.contact_phone_number, ImportDailyRedum.[Current Enrollment Status], ImportDailyRedum.[Current MembershipTier]
FROM ImportDailyRedum
WHERE (((ImportDailyRedum.[Contact First Name])='\n')) OR (((ImportDailyRedum.[Contact Last Name])='\n')) OR (((ImportDailyRedum.[Postcode/Zip])='\n') or ((ImportDailyRedum.contact_phone_number) Is Null)) OR (((ImportDailyRedum.contact_phone_number)='\n'));

[June7]

Don't save a DELETE query object then there is no chance for user to run or see it. Can do the DELETE action in VBA.

CurrentDb.Execute "DELETE FROM ImportDailyRedum WHERE [Contact First Name]='\n' OR [Contact Last Name]='\n' OR [Postcode/Zip]='\n' OR contact_phone_number Is Null OR contact_phone_number='\n'"

What do you mean by "the result data I will shift into another table"?