vba that will change criteria

[lowanam1]

I have attached a sample db. What I am trying to do is create code that will change the criteria in the qrycomparisionsheet for each of the 121 disciples in the tblDisciplines. The reason this needs to happen is that I need to have reports for each discipline rptComparisionSheet. A drop down box to open each of these reports is ideal. Can anyone help?? :banghead: Thank you in advance

 

[boblarson]

If you want us to see it, you have to include the password or remove it before uploading.

 

[lowanam1]

my fault its "LOCK" all caps

 

[boblarson]

Okay, first off - that query is going to be a problem for you. Unless there is data which matches EVERYTHING in EVERY TABLE, nothing will show up in the query. You are using all INNER JOINS which means that there has to be a matching record in the linked table or else it will not show. You need to use some OUTER JOINS to show data where there MAY be data or MAY NOT be data.

Second, you don't need to change the query at all for each disciplineID. You just need to open the report using a filter (once you get your table joins worked out), you can use a combo box(as you have suggested) to list all of the disciplines and have the bound column be the DisciplineID and the displayed part be the text. In the after update event of the combo box you can open the report like this:

Dim strWhere As String

strWhere = "[DisciplineID]=" & Me.ComboBoxNameHere

DoCmd.OpenReport "rptComparisionSheet", acViewPreview, WhereCondition:=strWhere

 

[lowanam1]

My query works fine it just didnt have any quotes in the db. I have added some numbers for your review. Also i cannot use the report filter for two reason one because I have a sub report in the rptComparisionSheet so it does not read correctly unless the criteria is changed for each discpline and also the users only have a runtime version of access and can not use the filter feature. I need code that will change the criteria unless u know how to solve the other two problems. Thanks for your help.

 

[boblarson]

Just want to reiterate that unless you have data for a particular record in ALL of the tables that record will not show up.

Also i cannot use the report filter for two reason one because I have a sub report in the rptComparisionSheet so it does not read correctly unless the criteria is changed for each discpline

If you have your subreport linked to the main report via the Master/Child properties you can use the method of opening the report with the where clause.

and also the users only have a runtime version of access and can not use the filter feature.

That is an incorrect statement. They are not using the filter feature. You have to provide the interface but you can filter to your heart's content in a runtime version. But you would not be "filtering" anyway. You would be using the WHERE CONDITION part of the DoCmd.OpenReport code like I said before.

But you can use a querydef to replace the sql of an entire query if you wish.

Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.QueryDefs("QueryNameHere")

qdf.SQL = "your sql string here"

qdf.Close

and then you can use the query.

 

[lowanam1]

yes you are correct the data won't show unless the data is in all the tables. Do you have a link for the where condition I am not following you. I am an amaeter access user. Thanks

 

[lowanam1]

This is what I inputted but I'm not sure what my SQL string would be...?? I highlighted that part in red below


Private Sub cboDiscipline_AfterUpdate()
Dim mySQLWork As String
Dim mySQLSub As String
'create a query based on the discipline combo box to filter the required work list
mySQLWork = "SELECT qryWorkItemWorkReq.pkWorkReqID, qryWorkItemWorkReq.WorkItemNumber,"
mySQLWork = mySQLWork & " qryWorkItemWorkReq.WorkRequired, qryWorkItemWorkReq.fkDisciplineID"
mySQLWork = mySQLWork & " FROM qryWorkItemWorkReq"
mySQLWork = mySQLWork & " WHERE qryWorkItemWorkReq.fkDisciplineID=" & Me.cboDiscipline
mySQLWork = mySQLWork & " ORDER BY qryWorkItemWorkReq.[WorkItemNumber];"
'assign that query to the required work list
Me.lstWorkReq.RowSource = mySQLWork

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryComparisionSheet")
qdf.SQL = "mySQLWork"
qdf.Close

End Sub

 

[boblarson]

Change:
qdf.SQL = "mySQLWork"

to
qdf.SQL = mySQLWork

without the quotes