How to edit a query and save in code?

[anniebai]

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 :-)

DoCmd.OpenQuery "Tuition Calculation", acViewDesign, acAdd
[Queries]![Tuition Calculation].Criteria = "Year=" & Year & " and Semester=" & Semester
DoCmd.Save acQuery
DoCmd.Close acQuery, "Tuition Calculation"

[Stwange]

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 :-)

DoCmd.OpenQuery "Tuition Calculation", acViewDesign, acAdd
[Queries]![Tuition Calculation].Criteria = "Year=" & Year & " and Semester=" & Semester
DoCmd.Save acQuery
DoCmd.Close acQuery, "Tuition Calculation"

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:

Dim qdf As DAO.QueryDef
Set qdf = DBEngine(0)(0).QueryDefs(queryName)
Dim rs As DAO.Recordset
qdf.Parameters(0) = Year
qdf.Parameters(1) = Semester
Set rs = qdf.OpenRecordset
If Not rs.EOF Then
    msgbox rs!<some_field> 'or whatever you want to do here
end if

Hope that helps, and good luck.

[anniebai]

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:

Dim qdf As DAO.QueryDef
Set qdf = DBEngine(0)(0).QueryDefs(queryName)
Dim rs As DAO.Recordset
qdf.Parameters(0) = Year
qdf.Parameters(1) = Semester
Set rs = qdf.OpenRecordset
If Not rs.EOF Then
    msgbox rs!<some_field> 'or whatever you want to do here
end if

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.

[JKing]

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.

[code=sql]
SELECT bookID, bookName, bookAuthor, bookLength, bookCoverType
FROM tblBook
[/code]

[code=vb]
Docmd.OpenRepor t "rptBookListing ", acViewNormal,, "[bookAuthor] = 'Jared'",acWind owNormal
[/code]

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.

[anniebai]

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.

[code=sql]
SELECT bookID, bookName, bookAuthor, bookLength, bookCoverType
FROM tblBook
[/code]

[code=vb]
Docmd.OpenRepor t "rptBookListing ", acViewNormal,, "[bookAuthor] = 'Jared'",acWind owNormal
[/code]

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 !!