I will try your suggestion, but need to leave work at the moment. Here the
code from one button:
Private Sub cmdEstmDetail_Click()
On Error GoTo Err_cmdEstmDetail_Click
Dim stDocName As String
Dim strCriteria As String
stDocName = "rptEstimateDetail"
For Each varItem In Me!lstBidNumber.ItemsSelected
strCriteria = strCriteria & ", " & Me.lstBidNumber.ItemData(varItem)
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
Set qdf = CurrentDb.QueryDefs("qryEstimateDetailEXP")
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT Project.ProjectName, Bid.BidNumber, Item.RoomNumber & "" -
"" & Item.ItemNumber AS ItemLabel, Item.RoomNumber, Item.ItemNumber,
Product.LibraryReference, Item.RoomName, Item.ElevationReference,
Item.ProductSummary, ItemDetail.Quantity, ItemDetail.ProductDescription,
Product.UnitCost, ItemDetail.QuoteCost, Product.UOM,
[Quantity]*([UnitCost]+[QuoteCost]) AS LineTotalCost, ItemDetail.Markup,
[LineTotalCost]*[Markup] AS SellPrice, Project.SalesTax, Bid.[Engineering%],
Bid.SalesTaxAmount, Bid.BidDate, Bid.Estimator, Project.GCName,
ItemDetail.ItemDetailNotes, Bid.SalesTaxAmount, Project.GCContact,
Bid.PriceIncludes, Bid.PriceDoesNotInclude, Customer.GCStreetAddress,
[GCCity] & "" , "" & [GCState] & "" "" & [GCZip] AS CityState,
Bid.ScopeNotes, Bid.BidType, Project.SiteStreetAddress, [SiteCity] & "" , ""
& [SiteState] & "" "" & [SiteZip] AS SiteCityState, Product.CBDCode,
[Quantity]*[MaterialCost] AS LineMaterialCost,
[Quantity]*([MachineLaborHours]+[BuildingLaborHours]) AS LineTotalLabor" & _
" FROM (Labor INNER JOIN Product ON Labor.CBDCode = Product.CBDCode) INNER
JOIN (Bid INNER JOIN (Customer INNER JOIN ((ItemDetail INNER JOIN Project ON
ItemDetail.ProjectName = Project.ProjectName) INNER JOIN Item ON
(Item.ItemNumber = ItemDetail.ItemNumber) AND (Item.RoomNumber =
ItemDetail.RoomNumber) AND (Item.BidNumber = ItemDetail.BidNumber) AND
(Item.ProjectName = ItemDetail.ProjectName) AND (Project.ProjectName =
Item.ProjectName)) ON (Customer.GCName = Project.GCName) AND (Customer.GCName
= Project.GCName)) ON (Project.ProjectName = Bid.ProjectName) AND
(Bid.BidNumber = Item.BidNumber) AND (Bid.ProjectName = Item.ProjectName)) ON
Product.ProductDescription = ItemDetail.ProductDescription" & _
" WHERE Project.ProjectID = " & Me.cboProjectName & " And (Bid.BidNumber) in
(" & strCriteria & ")" & _
" ORDER BY Project.ProjectName, Bid.BidNumber, Item.RoomNumber & "" - "" &
Item.ItemNumber, Item.RoomNumber, Item.ItemNumber, Product.LibraryReference,
Product.ProductCode;"
qdf.SQL = strSQL
Set qdf = Nothing
DoCmd.OpenReport stDocName, acPreview
DoCmd.Close acForm, "frmEstimateSelector1"
Exit_cmdEstmDetail_Click:
Exit Sub
Err_cmdEstmDetail_Click:
MsgBox Err.Description
Resume Exit_cmdEstmDetail_Click
End Sub
Thank you in advance for taking some time to look at!
Kevin
OfficeDev18 via AccessMonster.com said:
I suggest you delete the ("_") continuation character and continue on the
same line. If that works, fine. Otherwise, it might help to copy and paste
the entire code of one button so we can see a whole picture.
HTH
I have a multi-select list box form where the users select their criteria,
and click on various command buttons to view various reports. All reports
feed off of the same query. They each contain alot of the same data, just
sorted and grouped differently and some in different formats. The SQL from
the query is included within the code of each command button. It is all on
one line surrounded by:
strSQL = "[then all the fields are listed]" & _
I need to add a few more fields to the query, and also add them to the code.
The trouble I'm having is that it will not let me type any additional
characters into the code. I can type ENTER and type on the next line, but
then I get the error:
"Compile Error Expected:End of Statement"
Is there a maximum amount of characters, or do I need to change to a
different view? What would be limiting this?
I appreciate any feedback!
