Error Setting records on sqlStatement

[jmarcrum]

Hi everyone!

I'm getting an error that I can't figure out...here's my code...i'm trying to reference the first sqlStatement from the second sqlStatement and run the second sqlStatement. Am I doing it correctly?

The error is..."The Microsoft Jet database engine could not find the input table or query 'records'. Make sure that it exists and that its name is spelled correctly."

The error is on the DoCmd.RunSQL "sqlStateme nt2 line

[CODE=VB]Public Function CheckOutagesFor Review()

Dim sqlStatement1 As String
Dim sqlStatement2 As String
Dim records As Recordset
Dim dbs_curr As Database

Set dbs_curr = CurrentDb

'Get all Outages with chkOutage = True and group them by CaseNum
sqlStatement1 = "SELECT tblOutages.Case Number, Count(tblOutage s.RefNumber) AS CountOfRefNumbe r, Sum(tblOutages. CMI) AS SumOfCMI, tblOutages.chkO utages FROM tblOutages GROUP BY tblOutages.Case Number, tblOutages.chkO utages HAVING (((tblOutages.c hkOutages)=True ));"
Set records = dbs_curr.OpenRe cordset(sqlStat ement1, dbOpenDynaset, dbSeeChanges, dbOptimistic)

'Mark the outages that do not meet the criteria (Tree Growth (Cause = 38), Substation lock out (Swicth = FeederNumber),
'or CaseNum CMI >= 25,000) as Printed = True, Status = Reviewed, and Findings = "Outage did not meet minimum requirements for review"
sqlStatement2 = "UPDATE tblOutages SET tblOutages.Prin ted = True, tblOutages.Stat us = 4, tblOutages.Find ings = ""Outage did not meet minimum requirements for review"", tblOutages.chkO utages = False WHERE (((tblOutages.c hkOutages)=True ) AND ((tblOutages.Ca seNumber) In (SELECT CaseNumber FROM records WHERE records.CaseNum ber=tblOutages. CaseNumber)) AND ((tblOutages.CM I)<25000)) OR (((tblOutages.c hkOutages)=True ) AND ((tblOutages.Ca seNumber) In (SELECT CaseNumber FROM records WHERE records.CaseNum ber=tblOutages. CaseNumber)) AND ((tblOutages.Fe ederNumber)<>[tblOutages].[Switch])) OR (((tblOutages.c hkOutages)=True ) AND ((tblOutages.Ca seNumber) In (SELECT CaseNumber FROM records WHERE records.CaseNum ber=tblOutages. CaseNumber)) AND ((tblOutages.Ca use)<>38));"

DoCmd.SetWarnin gs False
DoCmd.RunSQL sqlStatement2
DoCmd.SetWarnin gs True

records.Close

End Function[/CODE]

[jmarcrum]

does anyone know why i could be getting this error?

[FishVal]

Hello, jmarcrum.

I hope Subqueries in SQL article will give you idea of how one query may be used as datasource in another query.

Regards,
Fish.

[NeoPa]

does anyone know why i could be getting this error?

You seem to be asking the SQL engine to reference an object that is only found in the routine that the SQL is PREPARED in. This is illogical. SQL would not be able to reference a VBA RecordSet object anyway, but even if it could, the scope would prohibit it from working in your context.

I suspect your whole SQL concept would benefit from linking in the subquery (what you refer to as [records]) in the FROM clause rather than the quite complex WHERE clause you're currently attempting.

[PianoMan64]

Hi everyone!

I'm getting an error that I can't figure out...here's my code...i'm trying to reference the first sqlStatement from the second sqlStatement and run the second sqlStatement. Am I doing it correctly?

The error is..."The Microsoft Jet database engine could not find the input table or query 'records'. Make sure that it exists and that its name is spelled correctly."

The error is on the DoCmd.RunSQL "sqlStateme nt2 line

[CODE=VB]Public Function CheckOutagesFor Review()

Dim sqlStatement1 As String
Dim sqlStatement2 As String
Dim records As Recordset
Dim dbs_curr As Database

Set dbs_curr = CurrentDb

'Get all Outages with chkOutage = True and group them by CaseNum
sqlStatement1 = "SELECT tblOutages.Case Number, Count(tblOutage s.RefNumber) AS CountOfRefNumbe r, Sum(tblOutages. CMI) AS SumOfCMI, tblOutages.chkO utages FROM tblOutages GROUP BY tblOutages.Case Number, tblOutages.chkO utages HAVING (((tblOutages.c hkOutages)=True ));"
Set records = dbs_curr.OpenRe cordset(sqlStat ement1, dbOpenDynaset, dbSeeChanges, dbOptimistic)

'Mark the outages that do not meet the criteria (Tree Growth (Cause = 38), Substation lock out (Swicth = FeederNumber),
'or CaseNum CMI >= 25,000) as Printed = True, Status = Reviewed, and Findings = "Outage did not meet minimum requirements for review"
sqlStatement2 = "UPDATE tblOutages SET tblOutages.Prin ted = True, tblOutages.Stat us = 4, tblOutages.Find ings = ""Outage did not meet minimum requirements for review"", tblOutages.chkO utages = False WHERE (((tblOutages.c hkOutages)=True ) AND ((tblOutages.Ca seNumber) In (SELECT CaseNumber FROM records WHERE records.CaseNum ber=tblOutages. CaseNumber)) AND ((tblOutages.CM I)<25000)) OR (((tblOutages.c hkOutages)=True ) AND ((tblOutages.Ca seNumber) In (SELECT CaseNumber FROM records WHERE records.CaseNum ber=tblOutages. CaseNumber)) AND ((tblOutages.Fe ederNumber)<>[tblOutages].[Switch])) OR (((tblOutages.c hkOutages)=True ) AND ((tblOutages.Ca seNumber) In (SELECT CaseNumber FROM records WHERE records.CaseNum ber=tblOutages. CaseNumber)) AND ((tblOutages.Ca use)<>38));"

DoCmd.SetWarnin gs False
DoCmd.RunSQL sqlStatement2
DoCmd.SetWarnin gs True

records.Close

End Function[/CODE]

The reason that your SQL Statement doesn't work is because there is a syntax error in your statement. When putting Quotes around something you need to know how to use the " and the ' when using quotes within a string value.

If you want to have a string within a string then you need to do something like this.

[code=vb]
sqlstatement2 = "Select * FROM tablename Where First = '" & variableName & "'"

[/code]

Hope that helps,

Joe P.

[NeoPa]

I'm not sure that's true Joe.

Although I definitely support that as the correct way to use strings in SQL, it is possible in Jet SQL to use the (") as a string delimiter (in fact that is the default Access uses for you).

When using such characters as SQL string delimiters (as opposed to VBA string delimiters) it is correct to double them up within a VBA string so they resolve to a single (") when passed to the SQL engine.