Writing SQL to open a recordset, then it VBA gets confused... (1 Viewer)

[David R]

I've done this dozens of times, I don't know why this database is flipping out on me. (Apologies for the convoluted code, it started out giving the "too few parameters" error when runSQL referenced the form that was running the code, so I had to break the loop.

Dim addrs As String
                    Dim rs As DAO.Recordset
                    Dim qdf As DAO.QueryDef
                    Dim proj As String
                    Dim bpkg As String
                    Dim contrs As String
                    Dim olApp As Outlook.Application
                    Dim objMailContr As Outlook.MailItem
                    
                    Set olApp = Outlook.Application
                    contrs = ConcatRelated("ContractorEmail", "queryEmailContractors", , , ";")
                    proj = [Forms]![formLetters]![textProjNum]
                    bpkg = Val([Forms]![formLetters]![textBidPkg])
                    
                    'see how many addresses we'll be using
                    Set rs = CurrentDb.OpenRecordset("SELECT ProjectNumber, BidPackage, CaseAddress, CaseID " & _
                             "FROM tableCases WHERE ((ProjectNumber = " & Chr(34) & proj & Chr(34) & ") AND (BidPackage = " & bpkg & ") AND (CaseStatus < 50 AND CaseStatus <> 25))")

                    'Check to see if the recordset actually contains rows
                    If Not (rs.EOF And rs.BOF) Then
                        rs.MoveFirst 'Unnecessary in this case, but still a good habit
                        If rs.RecordCount = 1 Then
                            addrs = "<li>" & rs!CaseAddress & "<i>(" & ConcatRelated("StructureType", "queryExhibitAs", "CaseID = '" & rs!CaseID & "'", "ExhibitID", " AND ") & ")</i></li>"
                        Else
                            While Not rs.EOF
                                addrs = addrs & "<li>" & rs!CaseAddress & "<i>(" & ConcatRelated("StructureType", "queryExhibitAs", "CaseID = '" & rs!CaseID & "'", "ExhibitID", " AND ") & ")</i></li>"
                                rs.MoveNext
                            Wend
                        End If
                    Else
                        MsgBox "No addresses are coming up eligible for bid in this project, sorry."
                        Set rs = Nothing
                        Exit Sub
                    End If

Having gotten around that problem, now when it gets to ConcatRelated it is giving a VERY strange error.

Error 2465 DB Support Database can't find the field '!1' referred to in your expression.

If I run ConcatRelated in the Immediate window, it works just fine.

Ideas?

 

[theDBguy]

Hi David. I suggest you assign the SQL statement into a String variable, so you can examine what it is exactly you're asking OpenRecordset to do. For example,


Dim strSQL As String

strSQL = "SELECT..."
Debug.Print strSQL
Set rs = CurrentDb.OpenRecordset(strSQL,...)


You can then examine the result of Debug.Print in the Immediate Window to see what Access is complaining about.

 

[Cronk]

  "CaseID = '" & rs!CaseID & "'"

If CaseID is numeric, lose the single quotes.

DBguy's suggested approach is my go to when I encounter issues with generated sql.

 

[plog]

Is there a null CaseID value in your table? Is [CaseID] a field in tableCases?

 

[David R]

Hi David. I suggest you assign the SQL statement into a String variable, so you can examine what it is exactly you're asking OpenRecordset to do. For example,


Dim strSQL As String

strSQL = "SELECT..."
Debug.Print strSQL
Set rs = CurrentDb.OpenRecordset(strSQL,...)


You can then examine the result of Debug.Print in the Immediate Window to see what Access is complaining about.

Yes, I did check it against Immediate via breakpoints. The SQL agrees exactly with the stored query version, except of course that the variables have been given values.

  "CaseID = '" & rs!CaseID & "'"

If CaseID is numeric, lose the single quotes.

CaseID is alphanumeric, in this test case '999-999'.

There are no nulls to worry about because it is testing against the open value in the reference form (the code checks earlier to make sure the reference form has data, before proceeding).

 

[The_Doc_Man]

That is SQL's way of saying that the first field in (I guess in this case) a SELECT is unknown or not located where you think it is located. Check spelling first.

 

[David R]

That is SQL's way of saying that the first field in (I guess in this case) a SELECT is unknown or not located where you think it is located. Check spelling first.

Thanks Doc; long time no see!

Here's the thing. If I resolve the variables during a breakpoint, I get valid SQL:

SELECT ProjectNumber, BidPackage, CaseAddress, CaseID 
FROM tableCases 
WHERE ((ProjectNumber = "IH2019") AND (BidPackage = 1) AND (CaseStatus < 50 AND CaseStatus <> 25))

I get as far as the recordset populating now, but when it tries to calculate the function ConcatRelated() while the code is running, it breaks with Error 2465. Outside of breakpoints or code, I can run ConcatRelated() manually with the exact same inputs and get the correct result! (and yes, while code is running, rs!CaseID is populated correctly).

 

[theDBguy]

Hi David. Sounds like you're saying the problem is with the ConcatRelated() function then. Have you tried setting a breakpoint there? Just a thought...

 

[isladogs]

You have 3 instances of ConcatRelated though the first one (contrs) doesn't appear to be used anywhere.
Which one is failing?

 

[David R]

Yup, the same breakpoint suffices (sorry, I'm probably spending too much time on the form variables because that is what ate my head yesterday).

But for whatever reason (quantum mechanics?) everything now runs smoothly. A seemingly unrelated error was in my .htmlbody build, but that showed a different error and was swiftly neutralized.

Ahhh, Access.... :banghead:

 

[David R]

You have 3 instances of ConcatRelated though the first one (contrs) doesn't appear to be used anywhere.
Which one is failing?

contrs is used lower in the code (.to field of the email).

As noted elsewhere, I eventually seem to have pushed the error off of this plane of existence, without actually changing anything in the quoted code.

 

[The_Doc_Man]

I'll be more specific. In the context of manually running the SQL via, say, the Immediate Window, you are running something in the Access GUI. However, once you are talking about SQL actually opened and passed along to JET or ACE (either one would have the same problem), you are NOT NECESSARILY in that context. It's like the difference between DoCmd.RunSQL vs. CurrentDB.Executure. .RunSQL sees things in the GUI environment but .Execute does not.

So check whether the ConcatRelated is correctly declared Public in a general module AND its arguments are (or at least should be) visible to SQL. Note that if ConcatRelated is declared Public but from a Class module, the Class Module is the thing that isn't visible.

 

[theDBguy]

Yup, the same breakpoint suffices (sorry, I'm probably spending too much time on the form variables because that is what ate my head yesterday).

But for whatever reason (quantum mechanics?) everything now runs smoothly. A seemingly unrelated error was in my .htmlbody build, but that showed a different error and was swiftly neutralized.

Ahhh, Access.... :banghead:

Hi David. Glad to hear you got it sorted out. Good luck with your project.