SOTA
asked on
Access 97: How to programmatically set a Form's RecordSource to a string longer than 255 characters?
I have Access 97 and need to set a Form's recordset to a string, Problem is my SQL string exceeds 255 characters, and so using
How can I set the Form's RecordSource in this case programmatically?
Dim db As Database
Dim rs As Recordset
Set db = DBEngine.Workspaces(0).Databases(0) ' Create database reference.
Dim strSQL As String
strSQL = "SELECT InvoiceOrderEntry.InvoiceID" & vbCrLf
strSQL = strSQL & " , InvoiceOrderEntry.InvoiceType" & vbCrLf
strSQL = strSQL & " , InvoiceOrderEntry.MainID" & vbCrLf
strSQL = strSQL & " , InvoiceOrderEntry.InvoiceNumber" & vbCrLf
strSQL = strSQL & " , RepairsRMAInformation.OriginalShippingDate" & vbCrLf
strSQL = strSQL & " , InvoiceOrderEntry.ShipDate" & vbCrLf
strSQL = strSQL & " , Products.ProductID" & vbCrLf
strSQL = strSQL & " , Products.ProductModelNumber" & vbCrLf
strSQL = strSQL & " , RepairsRMAInformation.UnitSerialNumber" & vbCrLf
strSQL = strSQL & " , RepairsRMAInformation.WarrantyPeriod" & vbCrLf
strSQL = strSQL & " , IIf([OriginalShippingDate]+([RepairsRMAInformation].[WarrantyPeriod])<=Date(),""Expired"",IIf(IsNull([OriginalShippingDate]),""Unknown"",""Active"")) AS WarrantyStatus" & vbCrLf
strSQL = strSQL & " , IIf([OriginalShippingDate]+([RepairsRMAInformation].[WarrantyPeriod])<=Date(),False,IIf(IsNull([OriginalShippingDate]),False,True)) AS UnderWarrantyYN" & vbCrLf
strSQL = strSQL & " , RepairsRMAInformation.Technician" & vbCrLf
strSQL = strSQL & " , Sum(InvoiceOrderDetails.OrderAmountMGC) AS SumOfOrderAmountMGC" & vbCrLf
strSQL = strSQL & " FROM ((InvoiceOrderEntry " & vbCrLf
strSQL = strSQL & " INNER JOIN RepairsRMAInformation
strSQL = strSQL & " ON InvoiceOrderEntry.InvoiceID = RepairsRMAInformation.InvoiceID) " & vbCrLf
strSQL = strSQL & " INNER JOIN Products " & vbCrLf
strSQL = strSQL & " ON InvoiceOrderEntry.ProductModelNumber = Products.ProductModelNumber) " & vbCrLf
strSQL = strSQL & " INNER JOIN InvoiceOrderDetails " & vbCrLf
strSQL = strSQL & " ON InvoiceOrderEntry.InvoiceID = InvoiceOrderDetails.InvoiceID" & vbCrLf
strSQL = strSQL & " WHERE (((InvoiceOrderEntry.Shipped)=True))" & vbCrLf
strSQL = strSQL & " GROUP BY InvoiceOrderEntry.InvoiceID" & vbCrLf
strSQL = strSQL & " , InvoiceOrderEntry.InvoiceType" & vbCrLf
strSQL = strSQL & " , InvoiceOrderEntry.MainID" & vbCrLf
strSQL = strSQL & " , InvoiceOrderEntry.InvoiceNumber" & vbCrLf
strSQL = strSQL & " , RepairsRMAInformation.OriginalShippingDate" & vbCrLf
strSQL = strSQL & " , InvoiceOrderEntry.ShipDate" & vbCrLf
strSQL = strSQL & " , Products.ProductID" & vbCrLf
strSQL = strSQL & " , Products.ProductModelNumber" & vbCrLf
strSQL = strSQL & " , RepairsRMAInformation.UnitSerialNumber" & vbCrLf
strSQL = strSQL & " , RepairsRMAInformation.WarrantyPeriod" & vbCrLf
strSQL = strSQL & " , IIf([OriginalShippingDate]+([RepairsRMAInformation].[WarrantyPeriod])<=Date(),""Expired"",IIf(IsNull([OriginalShippingDate]),""Unknown"",""Active""))" & vbCrLf
strSQL = strSQL & " , IIf([OriginalShippingDate]+([RepairsRMAInformation].[WarrantyPeriod])<=Date(),False,IIf(IsNull([OriginalShippingDate]),False,True))" & vbCrLf
strSQL = strSQL & " , RepairsRMAInformation.Technician" & vbCrLf
strSQL = strSQL & " HAVING (((InvoiceOrderEntry.InvoiceType)=""CR"") " & vbCrLf
strSQL = strSQL & " AND ((InvoiceOrderEntry.ShipDate) Between [Forms]![RepairSummaryReport_preselect]![FromDate] " & vbCrLf
strSQL = strSQL & " AND [Forms]![RepairSummaryReport_preselect]![ToDate]) " & vbCrLf
strSQL = strSQL & " AND ((Products.ProductID)=[Forms]![RepairSummaryReport_preselect]![ProductModelNumber]))" & vbCrLf
strSQL = strSQL & " ORDER BY InvoiceOrderEntry.ShipDate DESC;"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges, dbOptimistic)
Me.RecordSource = strSQL
results in an error.How can I set the Form's RecordSource in this case programmatically?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome!!!!
ASKER
Russ :)