[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

macro to remove field and restore original qry

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..
0
route217
Asked:
route217
  • 4
  • 2
2 Solutions
 
mbizupCommented:
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?
0
 
route217Author Commented:
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. ..so hence the vba route they are happy to click buttons. .

so would strsql = DELETE * FROM qry xxxx WHERE Region
do????
0
 
mbizupCommented:
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

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Rey Obrero (Capricorn1)Commented:
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")

oSql=qd.sql

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




'now return the original sql statement
qd.sql=oSql
0
 
mbizupCommented:
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.
0
 
mbizupCommented:
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

0
 
route217Author Commented:
Thanks experts
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now