By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,087 Members | 1,508 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,087 IT Pros & Developers. It's quick & easy.

How to edit a query and save in code?

P: 51
I have a query named "Tuition Calculation" saved in the database.
In the code, the criteria of the query need to be added/modified and be saved for use of another auto-generate report.
Is DoCmd.OpenQuery the right one to use? it seems not work
Below is the what I tried, please advice.. thanks :-)

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenQuery "Tuition Calculation", acViewDesign, acAdd
  2. [Queries]![Tuition Calculation].Criteria = "Year=" & Year & " and Semester=" & Semester
  3. DoCmd.Save acQuery
  4. DoCmd.Close acQuery, "Tuition Calculation"
  5.  
Aug 21 '07 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 126
I have a query named "Tuition Calculation" saved in the database.
In the code, the criteria of the query need to be added/modified and be saved for use of another auto-generate report.
Is DoCmd.OpenQuery the right one to use? it seems not work
Below is the what I tried, please advice.. thanks :-)

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenQuery "Tuition Calculation", acViewDesign, acAdd
  2. [Queries]![Tuition Calculation].Criteria = "Year=" & Year & " and Semester=" & Semester
  3. DoCmd.Save acQuery
  4. DoCmd.Close acQuery, "Tuition Calculation"
  5.  
I'm not sure how to do exactly what you want (ie, pass parameters and then open a query), but I can show you how to pass parameters and then refer to the fields in a query - first write the query so it has criteria such as =[TheYear] for year and =[TheSemester] for semester (the names here don't have to mean anything), then:
Expand|Select|Wrap|Line Numbers
  1. Dim qdf As DAO.QueryDef
  2. Set qdf = DBEngine(0)(0).QueryDefs(queryName)
  3. Dim rs As DAO.Recordset
  4. qdf.Parameters(0) = Year
  5. qdf.Parameters(1) = Semester
  6. Set rs = qdf.OpenRecordset
  7. If Not rs.EOF Then
  8.     msgbox rs!<some_field> 'or whatever you want to do here
  9. end if
  10.  
Hope that helps, and good luck.
Aug 22 '07 #2

P: 51
I'm not sure how to do exactly what you want (ie, pass parameters and then open a query), but I can show you how to pass parameters and then refer to the fields in a query - first write the query so it has criteria such as =[TheYear] for year and =[TheSemester] for semester (the names here don't have to mean anything), then:
Expand|Select|Wrap|Line Numbers
  1. Dim qdf As DAO.QueryDef
  2. Set qdf = DBEngine(0)(0).QueryDefs(queryName)
  3. Dim rs As DAO.Recordset
  4. qdf.Parameters(0) = Year
  5. qdf.Parameters(1) = Semester
  6. Set rs = qdf.OpenRecordset
  7. If Not rs.EOF Then
  8.     msgbox rs!<some_field> 'or whatever you want to do here
  9. end if
  10.  
Hope that helps, and good luck.
Thank you, Stwange. But I need this query's design to be changed and be used for another report. Means after running the program, double-click the query, the data source (where criteria) of the query is changed.
Aug 22 '07 #3

JKing
Expert 100+
P: 1,206
Hi there.

If you have a report based of a query that has no where clause you can specific the criteria as you open the report. I think this is what you would like to do rather than change the query criteria, open the report, change the query and open another report.

Here's an example of using the Where Condition parameter of the openreport command.

Let's assume we have a report based off this simple query for a book catalogue.

Expand|Select|Wrap|Line Numbers
  1. SELECT bookID, bookName, bookAuthor, bookLength, bookCoverType
  2. FROM tblBook
  3.  
Expand|Select|Wrap|Line Numbers
  1.  Docmd.OpenReport "rptBookListing", acViewNormal,, "[bookAuthor] = 'Jared'",acWindowNormal 
  2.  
The report would only show books that have the author as Jared.

How I interpreted this correctly if not let me know and I'll try to work something else out.
Aug 22 '07 #4

P: 51
Hi there.

If you have a report based of a query that has no where clause you can specific the criteria as you open the report. I think this is what you would like to do rather than change the query criteria, open the report, change the query and open another report.

Here's an example of using the Where Condition parameter of the openreport command.

Let's assume we have a report based off this simple query for a book catalogue.

Expand|Select|Wrap|Line Numbers
  1. SELECT bookID, bookName, bookAuthor, bookLength, bookCoverType
  2. FROM tblBook
  3.  
Expand|Select|Wrap|Line Numbers
  1.  Docmd.OpenReport "rptBookListing", acViewNormal,, "[bookAuthor] = 'Jared'",acWindowNormal 
  2.  
The report would only show books that have the author as Jared.

How I interpreted this correctly if not let me know and I'll try to work something else out.
Oh, yes. this works for the purpose. Thank you !!
Aug 27 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.