MS Access VBA SQL Not Working Properly

For some reason my SQL in VBA is showing blank in my forms box. The SQL works fine in a query. I have several other forms like this one and they all work fine but not this SQL for some reason. What's wrong with it?

Thanks for the help.

If Me!txtShowAllSourceJobs = True Then
'This will show all the jobs finshed or not.

 sSQL = "SELECT SourceSku.SourceSkuID, SourceSku.CustFirstNm, SourceSku.CustLastNm, SourceSku.CustBusNm, SKUs.SKU, Left([SkuNM],48) AS SkuName, SourceSku.dtmAdd, SourceSku.SourceJobComplete, SourceSkuStatus.Status, SourceSku.dtmEdit" _
& " FROM SKUs INNER JOIN (SourceSku LEFT JOIN SourceSkuStatus ON SourceSku.SourceSkuStatusID = SourceSkuStatus.SourceSkuStatusID) ON SKUs.SkuID = SourceSku.SkuID" _
& " WHERE (((SourceSku.SourceSkuID) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SourceSku.CustFirstNm) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SourceSku.CustLastNm) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SourceSku.CustBusNm) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SKUs.SKU) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((Left([SkuNm], 48)) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SourceSku.dtmAdd) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SourceSku.SourceJobComplete) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SourceSkuStatus.Status) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) ORDER BY SourceSku.SourceSkuID;"

 Forms!frmSourceSKUSearch!SearchResults.RowSource = sSQL
   Me!SearchResults.Requery

Open in new window

Microsoft AccessVBASQL

Last Comment

Dustin Stanley

8/22/2022 - Mon

Scott McDaniel (EE MVE )

4/20/2018

Try printing your SQL before assigning it to your RowSource:

Debug.Print sSQL

Post that back here after doing so ...

Dustin Stanley

4/20/2018

ASKER

SELECT SourceSku.SourceSkuID, SourceSku.CustFirstNm, SourceSku.CustLastNm, SourceSku.CustBusNm, SKUs.SKU, Left([SkuNM],48) AS SkuName, SourceSku.dtmAdd, SourceSku.SourceJobComplete, SourceSkuStatus.Status, SourceSku.dtmEdit FROM SKUs INNER JOIN (SourceSku LEFT JOIN SourceSkuStatus ON SourceSku.SourceSkuStatusID = SourceSkuStatus.SourceSkuStatusID) ON SKUs.SkuID = SourceSku.SkuID WHERE (((SourceSku.SourceSkuID) Like " * " & [Forms]![frmSourceSKUSearch]![SrchText] & " * ")) Or (((SourceSku.CustFirstNm) Like " * " & [Forms]![frmSourceSKUSearch]![SrchText] & " * ")) Or (((SourceSku.CustLastNm) Like " * " & [Forms]![frmSourceSKUSearch]![SrchText] & " * ")) Or (((SourceSku.CustBusNm) Like " * " & [Forms]![frmSourceSKUSearch]![SrchText] & " * ")) Or (((SKUs.SKU) Like " * " & [Forms]![frmSourceSKUSearch]![SrchText] & " * ")) Or (((Left([SkuNm], 48)) Like " * " & [Forms]![frmSourceSKUSearch]![SrchText] & " * ")) Or (((SourceSku.dtmAdd) Like " * " & [Forms]![frmSourceSKUSearch]![SrchText] & " * ")) Or (((SourceSku.Sou
rceJobComplete) Like " * " & [Forms]![frmSourceSKUSearch]![SrchText] & " * ")) Or (((SourceSkuStatus.Status) Like " * " & [Forms]![frmSourceSKUSearch]![SrchText] & " * ")) ORDER BY SourceSku.SourceSkuID;

Open in new window

PatHartman

4/20/2018

You could try:

Me.SearchResults.RowSource = sSQL
Me.SearchResults.Requery

I'm assuming that SearchResults is the name of the Subform control

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

Scott McDaniel (EE MVE )

4/20/2018

If that was posted after you create the sSQL variable, then your form references aren't working correctly. Try this instead (see the items highlighted in Bold):

sSQL = "SELECT SourceSku.SourceSkuID, SourceSku.CustFirstNm, SourceSku.CustLastNm, SourceSku.CustBusNm, SKUs.SKU, Left([SkuNM],48) AS SkuName, SourceSku.dtmAdd, SourceSku.SourceJobComplete, SourceSkuStatus.Status, SourceSku.dtmEdit" _
& " FROM SKUs INNER JOIN (SourceSku LEFT JOIN SourceSkuStatus ON SourceSku.SourceSkuStatusID = SourceSkuStatus.SourceSkuStatusID) ON SKUs.SkuID = SourceSku.SkuID" _
& " WHERE (((SourceSku.SourceSkuID) Like '*" & [Forms]![frmSourceSKUSearch]![SrchText] & "*')) Or (((SourceSku.CustFirstNm) Like '*" & [Forms]![frmSourceSKUSearch]![SrchText] & "*'))

And continue doing the same for all of your Form refereces ...

Dustin Stanley

4/21/2018

ASKER

Pat:
Me.SearchResults.RowSource = sSQL
Me.SearchResults.Requery

Does the same effect.

Scott:
That does not work in the query. In my search box on my form I have the query criteria in each field value as:

Like "*" & [forms]![frmSourceSKUSearch]![SrchText] & "*"

Open in new window

The code I provided in the question is running in a Afterupdate event of a Check box. I don't get it as I have many search forms just like this one but for some reason this exact SQL is giving me issues.

ASKER CERTIFIED SOLUTION

Dustin Stanley

4/21/2018

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

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Scott McDaniel (EE MVE )