Save query coding 2

[PortyAL]

Hi

I have a routine as below which amends an existing query (query2) inserting a filter based on current value in Forms![frm Recs tracked jobs]![job id].

I want the routine to save this query naming it the same as the text in Forms![frm Recs tracked jobs]![job]

The query builds OK but I need help with the save command, as the one below does not work. I'm just feeling my way in VB coding so sorry if this seems a simple request!

Thanks

AL


Private Sub Command55_Click()
Dim strsql
Dim JobNo
Dim QueryName

Set db = CurrentDb
Set qdf = db.QueryDefs("query2")

JobNo = Forms![frm Recs tracked jobs]![job id]

QueryName=Forms![frm Recs tracked jobs]![job]

strsql = "SELECT [tbl Recommendations].[Rec ID], [tbl Recommendations].Job, [tbl Recommendations].Number, [tbl Recommendations].Condition, [tbl Recommendations].Recommendation, [tbl Recommendations].Response, [tbl Recommendations].Accepted, [tbl Recommendations].[Date of Implementation], [tbl Recommendations].[Officer Responsible], [tbl Recommendations].[Audit Comment], [tbl Recommendations].Status, [tbl Recommendations].[Find Rec], [tbl Recommendations].Priority, [tbl Recommendations].[Report Ref], [tbl Recommendations].Type, [tbl Recommendations].Heading, * FROM [tbl Recommendations] WHERE ((([tbl Recommendations].Job)= " & JobNo & "));"

qdf.SQL = strsql

Set qdf = Nothing
Set db = Nothing
DoCmd.OpenQuery "query2"

DoCmd.Save , QueryName

End Sub

 

[JerryKlmns]

Why keep changing the sql since you could achieve that with this one?

"SELECT * FROM [tbl Recommendations] WHERE Job=" & Forms![frm Recs tracked jobs]![job id]

 

[PortyAL]

Hi

The query is part of an audit monitoring database. As each audit is carried out recommendations are made for control improvements. Each recommendations requires a response from the manager of the service audited. My aim is to allow managers to do this via a data access page which will update the main database. However I only want managers to see the recommendations for their service only due to confidentiality issues.

The above routine creates a query of recommendations for a particular job. I then want to save it as the name of the job, then create a data access page for the new query, and make this page available to the relevant manager, thus restricting him/her to their recommendations only.

As you can probably guess I am only in the process of thinking this though so any suggestions you might have would be aprreciated.

Thanks

AL

 

[JerryKlmns]

Ok then

Dim QDF As DAO.QueryDef
Dim strSQL As String
Dim JobNo As Long
Dim QueryName As String

JobNo = Forms![frm Recs tracked jobs]![job id]
QueryName = Forms![frm Recs tracked jobs]![job]
strSQL = "SELECT * FROM [tbl Recommendations] WHERE Job =" & JobNo
Set QDF = CurrentDb.CreateQueryDef(QueryName, strSQL)
End Sub

 

[PortyAL]

Thanks for your help!

AL

 

[PortyAL]

Hello again

When I try running the code there is an error saying "Compile error: User defined type not defined", and the "As DAO.QueryDef" section of code is highlighted in the script window.

Hope that makes sense!

AL

 

[Crowley16]

I believe it's just querydef, not dao.querydef...

--------------------
Procrastinate Now!

 

[PortyAL]

Hi

Tried querydef. Same error shows.

Thanks

AL

 

[PHV]

menu Tools -> References ...
Tick the Microsoft DAO 3.x library.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PortyAL]

Thanks.

That's sorted it!

Much appreciated.

AL