macro to remove field and restore original qry

Posted on 2013-12-04
Last Modified: 2013-12-04
Hi Experts

need a macro that will remove the field region  from the following vba code

Sub CreateQuery() dim strSQL as string dim qdf as QueryDef strSQL = "SELECT Field1, Field2, Region FROM YourTable WHERE core = '" & me.txtCore & "' ORDER BY [GroupBy]" set qdf = Currentdb.CreateQueryDef("qryMyQuery", strSQL) docmd,OpenQuery "qryMyQuery" end sub

and restore the qry back to its original form..
Question by:route217
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 61

Expert Comment

ID: 39695569
As I mentioned in your previous question - I don't think this is the best/most efficient way of doing this.

What are you ultimately using these queries for?
Why not just select the fields you need from your table when and where you need them rather than building on and reverting your query to its original state?

Author Comment

ID: 39695606
Hi mbizup

using the qry to populate figures.....year end...

i understand what u are saying but the end users have no working knowledge of access. ..very limited. hence the vba route they are happy to click buttons. .

so would strsql = DELETE * FROM qry xxxx WHERE Region
LVL 61

Accepted Solution

mbizup earned 250 total points
ID: 39695723
Here's what I would suggest doing...

Create a form, default view Datasheet view use your query (with ALL the fields) as its recordsource, using textboxes bound to each of the fields.

If you want two separate views, with and without the region, you can do something like this:

Private sub cmdShowAll_Click()
       Docmd.OpenForm "YourFormName"
End Sub

Open in new window

Private sub cmdShowWithoutRegion_Click()
       Docmd.OpenForm "YourFormName"
       Forms!YourFormName!txtYourRegion.Visible = false
End Sub

Open in new window

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 39695730
i'll repost my comment from the previous thread

to preserve the original sql statement you have to store it in a variable and used it back when your done with adding/altering the original sql statement

dim oSql as string, qd as dao.querydef, db as dao.database
set db=currentdb
set qd=db.querydefs("qry salesbyarea")


'do your thing here
strSql="select..... <with added> fields ..etc.."

'now return the original sql statement
LVL 61

Expert Comment

ID: 39695748
The reason I'm suggesting the form approach is that it is more standard, generally considered 'best practice' to show the data through a form interface rather than directly displaying tables or queries (it gives you more control).  

That way, your users can see the data they want at the click of a button, and you as the developer are working with a single form and a single query, which have plenty of possibilities for *easily* displaying the data in a wide variety of ways.
LVL 61

Expert Comment

ID: 39695769
Your criteria can also be easily handled like this.

Assuming you have a textbox on your form for CORE, set the SQL of your query to include all fields, but NOT the where clause:

SELECT Field1, Field2, Region FROM YourTable

Then this will display all fields with the core criteria:

Private sub cmdShowAll_Click()
       Docmd.OpenForm "YourFormName", WhereCondition := "Core = '" & me.txtCore & "'"
End Sub

Open in new window

Then this will display without the region and without the core criteria
Private sub cmdShowAll_Click()
       Docmd.OpenForm "YourFormName"
       Forms!YourFormName.txtRegion.Visible = False
End Sub

Open in new window


Author Comment

ID: 39695775
Thanks experts

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
default value of combobox 25 70
Access 2010 Only Including Numbers After Certain Limit 3 47
Access Runtime Error '3070' 5 52
Cant delete records in query 8 50
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question