vba question

[lowanam1]

I am wondering if you can get help with vba code that will change criteria in a query qryComparisionSheet by discipline then open rptComparisionSheet when it is selected from a drop down menu...let me know if i make sense.. thank u in advance a sample db is attached password LOCK

 

[jzwp22]

Typically, you would leave the query unfiltered and then just filter the report when it opens. So I would recommend that you alter your qryComparisonSheet by adding the pkDisciplineID field to the SELECT clause and removing the (tblDisciplines.SubDiscipline)="TANK") AND from the WHERE clause (see red highlighted areas below)

SELECT tblWorkItems.WorkItemNumber, tblWorkItems.WorkItemTitle, tblWorkRequired.MAKEBUYSUB, tblWorkRequired.HAVETOSUB, tblWorkRequired.fkDisciplineID, tblDisciplines.SubDiscipline, tblSubcontractorQuotes.fkCompanyID, tblSubcontractors.CompanyName, tblSubcontractorQuotes.fkQuoteStatusID, tblQuoteStatus.txtQuoteStatus, tblSubcontractorQuotes.ManHours, tblSubcontractorQuotes.Material, tblSubcontractorQuotes.Total, tblSubcontractorQuotes.SubExceptions, tblSubcontractorQuotes.SubComments, tblSubcontractorQuotes.fkAwardJustificationID, tblAwardJustification.txtAwardJustification, tblWorkRequired.WorkRequired, tblWorkItems.fkPackageTypeID, tblPackageTypes.txtPackageType, tblSubcontractors.fkContractTypeID, tblContractType.txtContractType, tblDisciplines.pkDisciplineID
FROM (tblWorkItemStatus INNER JOIN (tblPackageTypes INNER JOIN (tblAllocationAfterPMO INNER JOIN tblWorkItems ON tblAllocationAfterPMO.pkAllocationID = tblWorkItems.fkAllocationID) ON tblPackageTypes.pkPackageTypeID = tblWorkItems.fkPackageTypeID) ON tblWorkItemStatus.pkWorkItemStatusID = tblWorkItems.fkWorkItemStatusID) INNER JOIN (((tblDisciplines INNER JOIN (tblContractType INNER JOIN tblSubcontractors ON tblContractType.pkContractTypeID = tblSubcontractors.fkContractTypeID) ON tblDisciplines.pkDisciplineID = tblSubcontractors.fkDisciplineID) INNER JOIN tblWorkRequired ON tblDisciplines.pkDisciplineID = tblWorkRequired.fkDisciplineID) INNER JOIN (tblQuoteStatus INNER JOIN (tblAwardJustification INNER JOIN tblSubcontractorQuotes ON tblAwardJustification.pkAwardJustificationID = tblSubcontractorQuotes.fkAwardJustificationID) ON tblQuoteStatus.pkQuoteStatusID = tblSubcontractorQuotes.fkQuoteStatusID) ON (tblWorkRequired.pkWorkReqID = tblSubcontractorQuotes.fkWorkRequiredID) AND (tblSubcontractors.pkCompanyID = tblSubcontractorQuotes.fkCompanyID)) ON tblWorkItems.pkWorkItemNumberID = tblWorkRequired.fkWorkItemNumberID
WHERE (((tblDisciplines.SubDiscipline)="TANK") AND ((tblWorkItemStatus.WorkItemStatus)="ACTIVE"))
ORDER BY tblWorkItems.WorkItemNumber;

I would then create an unbound form and add a combo box based on tblDisciplines. Make the bound field of the combo box pkDisciplineID. Now in either the after update event of the combo box or a button (your choice), you will want to add code to open the report and filter it with the combo box selection. The code would look something like this to open the report in report preview mode:

DoCmd.OpenReport "rptComparisionSheet", acViewPreview, , "pkDisciplineID=" & Me.cboDiscipline, acNormal

 

[lowanam1]

thanks so much. I got all the way to the code part and it is telling me that there is a snytax error (misisng operator). I put the code in the after update event. Not sure what is happening, should the code go somewhere else?. Thank you for your help

Private Sub Combo20_AfterUpdate()
DoCmd.OpenReport "rptComparisionSheet", acViewPreview, , "pkDisciplineID= & Me.cboDiscipline, acNormal"
End Sub

 

[jzwp22]

You have the ending double quote in the wrong place, it should be after the = sign not after acNormal

DoCmd.OpenReport "rptComparisionSheet", acViewPreview, , "pkDisciplineID=" & Me.cboDiscipline, acNormal

 

[lowanam1]

Ok i changed the quote and I still got the syntax error (missing operator)....

Private Sub Combo20_AfterUpdate()
DoCmd.OpenReport "rptComparisionSheet", acViewPreview, , "pkDisciplineID=" & Me.cboDiscipline, acNormal
End Sub

 

[jzwp22]

Is the bound field of the combo box the pkDisciplineID?

You could put a debug.print statement ahead of the docmd to make sure

Private Sub Combo20_AfterUpdate()
debug.print me.cboDiscipline
DoCmd.OpenReport "rptComparisionSheet", acViewPreview, , "pkDisciplineID=" & Me.cboDiscipline, acNormal
End Sub

You can then open the VBA immediate window after a selection in the combo box to see what value is being returned.

 

[lowanam1]

the immediate window says : NULL

 

[jzwp22]

The name of the combo box is Combo20 not cboDiscipline

Private Sub Combo20_AfterUpdate()
DoCmd.OpenReport "rptComparisionSheet", acViewPreview, , "pkDisciplineID=" & Me.cboDiscipline, acNormal
End Sub

 

[lowanam1]

that makes sense and that did it! thanks so much !!

 

[jzwp22]

My apologies for not picking up on it sooner.

 

[lowanam1]

oh thats ok...one more quick question. How would I add the sub report to also filter in the same code...rptComparisionSheetSubReport

DoCmd.OpenReport "rptComparisionSheettest", acViewPreview, , "pkDisciplineID=" & Me.Combo20, acNormal

 

[jzwp22]

You would probably use the main report to control the subreport. In the linking of the main to subreports, you can add the pkDisciplineID field to master and child. If the main and sub are already linked via another field just add a comma and then pkDisciplineID. You will have to make sure the subreport's record source has pkDisciplineID in it.

 

[lowanam1]

I tried everything and the sub report doesn't filter like the main report. the sub report shows all records

 

[lowanam1]

Can i use the AND function in the code ?

 

[jzwp22]

You can open only one report via the 1 docmd. In looking at your report yesterday, I noticed that the subreport had the same record source as the main report. I am curious as to why you set it up like that? Typically a subreport brings in related data to the record in the main report not the same data.

 

[lowanam1]

oh thats because I needed the sub report to show the grand totals of the subcontractors per discipine. I could not figure out how to have the report show the information grouped by work item show the grand total of each sub. So I created a subreport to show the grand total and put it into the main report. The only purpose of the sub report is to show the grand total of each sub by discipline. Maybe there is a better way to do this?

 

[jzwp22]

Since you are filtering to only one discipline, I don't see the need for the subreport. You can bring in the contractor information via the query and set up a grouping level on the contractor and in the footer of the group have the total.

 

[lowanam1]

ok it only lets me group by sub name if i take out the work item number in the query otherwise I have the problem of it totaling by work item and not sub name...

 

[lowanam1]

in the report I do need the work item number totals to also show but a seperate sub grand total in the footer of the report because I need to know the total per work item number and also the grand total per sub name/discipline

 

[jzwp22]

Since a work item has many disciplines (based on your relationship diagram) but you are filtering to only 1 discipline, your report will only show data for that one discipline for each work item. Is that what you really need? It sounds like filtering by discipline is not helping you achieve what you want.