Avatar of SOTA
SOTAFlag for Canada

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
 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

Open in new window

results in an error.
How can I set the Form's RecordSource in this case programmatically?
Microsoft AccessSQL

Avatar of undefined
Last Comment
SOTA
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of SOTA
SOTA
Flag of Canada image

ASKER

Thanks! I just remembered to do that and it is working perfectly!! Thanks!!
Russ :)
Avatar of SOTA
SOTA
Flag of Canada image

ASKER

Awesome!!!!
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo