Query not working from module.

I have the following query that works perfectly from within a form:
 
DoCmd.RunSQL ("Delete * from tblScheduleCOLORSMAKE")
Dim strSQL As String
strSQL = "INSERT INTO tblScheduleCOLORSMAKE ( press, tool, colorno, Expr2, tstart, matno, qty ) " & vbCrLf & _
"SELECT pressxlsbu.press, pressxlsbu.tool, pressxlsbu.colorno, Mid([colorno],InStrRev([colorno],""-"")+1) AS Expr2, pressxlsbu.tstart, pressxlsbu.matno, Sum(pressxlsbu.qty) AS SumOfqty " & vbCrLf & _
"FROM pressxlsbu " & vbCrLf & _
"GROUP BY pressxlsbu.press, pressxlsbu.tool, pressxlsbu.colorno, Mid([colorno],InStrRev([colorno],""-"")+1), pressxlsbu.tstart, pressxlsbu.matno " & vbCrLf & _
"HAVING (((pressxlsbu.press)=""MLD33"")) " & vbCrLf & _
"ORDER BY pressxlsbu.tstart;"

DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
DoCmd.OpenForm "frmScheduleListCOLOR", , , "[press] = 'MLD33'", , , False
Forms!frmScheduleListCOLOR!txtPress = "MLD33"

Open in new window


I have moved it back into a module to make it easier to call from different MLD locations, but now it doesn't work and I can't figure out why:

 
Public Function colorQRY(MOLDNUMBER As String)

DoCmd.SetWarnings False

DoCmd.RunSQL ("Delete * from tblScheduleCOLORSMAKE")
Dim strSQL As String
strSQL = "INSERT INTO tblScheduleCOLORSMAKE ( press, tool, colorno, Expr2, tstart, matno, qty ) " & vbCrLf & _
"SELECT pressxlsbu.press, pressxlsbu.tool, pressxlsbu.colorno, Mid([colorno],InStrRev([colorno],""-"")+1) AS Expr2, pressxlsbu.tstart, pressxlsbu.matno, Sum(pressxlsbu.qty) AS SumOfqty " & vbCrLf & _
"FROM pressxlsbu " & vbCrLf & _
"GROUP BY pressxlsbu.press, pressxlsbu.tool, pressxlsbu.colorno, Mid([colorno],InStrRev([colorno],""-"")+1), pressxlsbu.tstart, pressxlsbu.matno " & vbCrLf & _
"HAVING (((pressxlsbu.press)="" & MOLDNUMBER & "")) " & vbCrLf & _
"ORDER BY pressxlsbu.tstart;"

DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True


DoCmd.OpenForm "frmScheduleListCOLOR", , , "[press] = ' & MOLDNUMBER & '", , , False
'DoCmd.OpenReport "qrySchedule", acViewReport, , "[press] = 'MLD11'", , False
Forms!frmScheduleListCOLOR!txtPress = MOLDNUMBER

End Function

Open in new window


I know it's in the actual SQL statement because the table is not getting populated. Any ideas? Thanks for any help you can give me on this.

PS..I am calling it like this:

Call colorQRY("MLD33")

LVL 1
G ScottAsked:
Who is Participating?
 
mbizupCommented:
Also try this corrected SQL:

strSQL = "INSERT INTO tblScheduleCOLORSMAKE ( press, tool, colorno, Expr2, tstart, matno, qty ) " & vbCrLf & _
"SELECT pressxlsbu.press, pressxlsbu.tool, pressxlsbu.colorno, Mid([colorno],InStrRev([colorno],""-"")+1) AS Expr2, pressxlsbu.tstart, pressxlsbu.matno, Sum(pressxlsbu.qty) AS SumOfqty " & vbCrLf & _
"FROM pressxlsbu " & vbCrLf & _
"GROUP BY pressxlsbu.press, pressxlsbu.tool, pressxlsbu.colorno, Mid([colorno],InStrRev([colorno],""-"")+1), pressxlsbu.tstart, pressxlsbu.matno " & vbCrLf & _
"HAVING (((pressxlsbu.press)='"  & MOLDNUMBER &  "')) " & vbCrLf & _
"ORDER BY pressxlsbu.tstart;"

0
 
mbizupCommented:
The syntax of your openform statement is wrong:

>> DoCmd.OpenForm "frmScheduleListCOLOR", , , "[press] = ' & MOLDNUMBER & '", , , False

You're  missing a double quote in the criteria before & MOLDNUMBER.  Fixed:

DoCmd.OpenForm "frmScheduleListCOLOR", , , "[press] = '" & MOLDNUMBER & '", , , False
0
 
mbizupCommented:
Another correction - one more missing double quote.

Try this:

DoCmd.OpenForm "frmScheduleListCOLOR", , , "[press] = '" & MOLDNUMBER & "'", , , False
0
 
G ScottAuthor Commented:
Hey mbizup, that worked great. Whew, I made you work for that one, huh? Those pesky single and double quotes!! Thanks so much for your help.
0
 
mbizupCommented:
Ha!

Yes, you did make me work for it.  Those embedded single quotes are tough to see, especially for old eyes.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.