Solved Run-time error 2465. Trying to save an SQL string into strSQL (1 Viewer)

[autumnnew]

I keep getting a "Run-time error '2465' message. Access can't find the field '|1' referred to in your expression" error message.

Here is my code. I'm guessing I have the syntax wrong somewhere.

    strSQL = "SELECT Borings.BoringID AS bBoringID, Borings.HoleDepth, Borings.[Continuous To] AS ContTo, Borings.[Every Other] AS EveryOther, Borings.[Sample Length] AS bSampleLength, Borings.[Manual Sampling] AS ManualSampling, Max([Samples].[Depth]+[Samples].[Length]) AS MaxSampleDepth, Max(Samples.Number) AS MaxSampleNumber " _
            & "FROM Borings " _
            & "INNER JOIN Samples " _
            & "ON Borings.BoringID = Samples.BoringID " _
            & "GROUP BY Borings.BoringID, Borings.HoleDepth, Borings.[Continuous To], Borings.[Every Other], Borings.[Sample Length], Borings.[Manual Sampling], Borings.ProjectID " _
            & "HAVING Borings.HoleDepth <> 0 AND Borings.[Manual Sampling] = False AND Max([Samples].[Depth]+[Samples].[Length]) < " & [Borings].[HoleDepth] & " AND Borings.ProjectID = " & [TempVars]![tmpProjectID] & ""

The purpose: I'm not very good at articulating what I'm trying to say, but I'll try my best. I'm writing code to append new records of additional soil samples that have been drilled for soil borings that have already been drilled. For example, if a boring was originally drilled to 20 feet, and all samples down to 20 feet were populated, and then drillers went back and drilled down to 40 feet, then I need code to populate/append the samples for the additional 20 to 40 feet drilled. The strSQL automatically identifies these borings by comparing the depth of the deepest sample to the depth of the borings. The query design works correctly, but I can't seem to get it to work in VBA.

Sample lengths are 2 feet long, so for a boring drilled to 20 feet, the deepest sample would be 18 feet.
MaxSampleDepth: Max(Samples.SampleDepth (18) + Samples.SampleLength (2)) = 20
Boring Depth = 20

Therefore, MaxSampleDepth = Boring Depth, so no additional samples need to be added for this boring.

But for a boring that was drilled to 20 feet, then later drilled to 40 feet, the query compares these values to find that new samples between the depths of 20 to 40 should be appended to this boring.

 

[pbaldy]

This is the common technique for debugging SQL in VBA:

Debugging

 

[autumnnew]

Okay, thank you. I've fixed it, but now I have a new error message. Run-time error 3061. Too few parameters. Expected 1.

 

[pbaldy]

What is the resulting SQL from the Immediate window? How are you trying to use it? Are any of the sources queries with form criteria?

 

[theDBguy]

Okay, thank you. I've fixed it, but now I have a new error message. Run-time error 3061. Too few parameters. Expected 1.

Hi. I can't see why you would be getting a 3061 error; but just in case you're trying to build a recordset, maybe this will help.

Code Snippets

 

[autumnnew]

What is the resulting SQL from the Immediate window? How are you trying to use it? Are any of the sources queries with form criteria?

SELECT Borings.BoringID AS bBoringID, Borings.HoleDepth, Borings.[Continuous To] AS ContTo, Borings.[Every Other] AS EveryOther, Borings.[Sample Length] AS bSampleLength, Borings.[Manual Sampling] AS ManualSampling, Max([Samples].[Depth]+[Samples].[Length]) AS MaxSampleDepth, Max(Samples.Number) AS MaxSampleNumber  FROM Borings INNER JOIN Samples ON Borings.BoringID = Samples.BoringID  GROUP BY Borings.BoringID, Borings.HoleDepth, Borings.[Continuous To], Borings.[Every Other], Borings.[Sample Length], Borings.[Manual Sampling], Borings.ProjectID  HAVING (((Borings.HoleDepth)<>0) AND ((Borings.[Manual Sampling])=False) AND ((Max([Samples].[Depth]+[Samples].[Length]))<[Borings].[HoleDepth]) AND ((Borings.ProjectID)=[TempVars]![tmpProjectID]))

This is the result I get in the immediate window. No, the sources are just two tables, Borings and Samples.

Now this is where the 3061 error is coming up.

 

[pbaldy]

What happens if you copy that SQL into a blank query and try to run it? Has that TempVar been created/populated?

 

[autumnnew]

Hi. I can't see why you would be getting a 3061 error; but just in case you're trying to build a recordset, maybe this will help.

Thanks for the link! I don't totally understand it or know quite how to apply it. Not quickly, anyway.

What happens if you copy that SQL into a blank query and try to run it? Has that TempVar been created/populated?

The SQL works perfectly; it gives me the results I'm looking for, for rs1. Yep, the TempVar works too. It is used to filter the borings by ProjectID.

 

[pbaldy]

Can you attach the db here?

 

[The_Doc_Man]

The way I read your initial description made me think you are somehow trying to relate the 20-ft and 40-ft samples but your method seems to not be working. Step back and give us a 10,000 foot overview of the problem and then tell us what isn't working - and in what way it is failing. We are working trying to see the forest here and you are showing us the trees.

 

[Gasman]

Need to review post #5 again, and just use that function.

 

[autumnnew]

Okay, here is more insight. Originally, I didn't want to overload you with more information than you need. So here's the forest for those who want it. I'm struggling with finding a balance in articulating this between a programmer's perspective and a user's perspective. Also, I'm still a beginner at VBA, but I've been fairly successful in researching solutions for what I need and applying it to my database. That doesn't mean the logic I applied to my problems is always the most logical, so if you see a better way that the code can be written, please let me know. I'll post this code in a subsequent post.

I have beautiful code that adds batches of borings and populating samples per boring. On initial project set up, the first batch of borings are defined and populated first after clicking Add Project, and then the user is taken to the Borings/Samples tab on the Main form to then make adjustments to individual borings. Then the samples are populated (clicking Update Samples button). Additional batches of borings can be added later in the drilling phase of the project. Upon adding these records, the borings have a hidden field called [Samples Added] that gets marked to True. It might be unnecessary, but until now, the code didn't have any way to differentiate between borings that have already been given samples. I didn't want a user to inadvertently (and easily) overwrite existing samples, so it was supposed to somehow act as 'locking' the samples.

But now I need the code to handle when a boring's depth has been adjusted AFTER the samples have already been set up. Upon adjusting the boring depth, boringDepth_AfterUpdate([Samples Added] = False), so borings with adjusted depths will be included in the query of borings that need samples appended/deleted.

The code needs to handle 4 different scenarios when a user clicks the 'Update Samples' button:

1. If boring depth is left at zero (user forgot to enter or doesn't know the boring depth yet), then update [Samples Added] to False. No samples will be generated for this boring. 'This part of the code works.
2. If boring depth is changed to a shorter depth, then delete the deeper samples and update [Samples Added] = True. 'This part of the code works.
3. If boring depth is changed to a deeper depth, then append samples and update [Samples Added] = True. 'This code is where I'm getting the error.
4. If boring has a defined depth but has no samples, then add samples and update [Samples Added] = True. 'This part of the code works. This step used to be the ONLY step, but I recently added Steps 1-3 to handle other scenarios. I basically duplicated this code for Step 3, and I changed the definition of the recordsets and the If statements.

Important information about the sampling
Standard Sampling: When sampling these borings, 90-95% of the time samples are taken at a continuous depth from 0 feet down to a defined depth (default value = 10 feet), and then every other depth after that (default value = 5 feet). Sample length default value = 2 feet. Users are able to adjust individual samples after they've been created. But occasionally, there are projects where there is little consistency to the sampling method, and so users would have to manually enter samples.

So samples usually look like this for Boring 1 which is drilled 25 feet deep:

Sample Number	Sample Depth	Sample Length
1	0	2
2	2	2
3	4	2
4	6	2
5	8 (continuous down to 10 ft, every 5 ft after)	2
6	13	2
7	18	2
8	23	2

Custom sampling: When setting up borings, the user also has the option to select 'I will manually enter samples' ([Manual Sampling] = True) and enter custom samples at custom depths and custom sample lengths. These samples are not included in any code, except when a user needs to delete them. These borings are always excluded from the 'Update Samples' process.

The issue with Step 3 above
The code is supposed to:
1. Query the borings for selected project ([ProjectID] = [TempVar]![tmpProjectID] AND [Manual Sampling] = False). 'This code is where I'm getting the error, when defining the first recordset in the strSQL. The query is an aggregate query explained in the next step.
2. Compare the depth of the deepest sample of each boring to the depth of boring and determine if additional samples need to be appended. The MAIN definition of this aggregate query is: select Borings in Borings table WHERE tbleBorings.boringID = tblSamples.boringID, WHERE Max(tblSamples.Depth + tblSamples.Length) is less than tblBorings.HoleDepth. 'Is the aggregate query causing an issue in trying to insert it into a string strSQL? The query itself works and produces the results I need for this step.
3. The next step, which hasn't been tested yet, is to determine if the drilling stopped before the Continuous To depth. If so, then add samples until the Continuous To depth is reached, and then continue adding samples with the defined [Every Other] depth as long as Sample Depth is less than Boring Depth.

Non pertinent
Also, the code is supposed to number the samples starting at 1. I never figured out how to re-number samples if a user deleted a sample, so I have a dumb little query that does this. If it ain't broke... Before I got this far in developing my database and was finally forced to start tinkering with VBA, I was able to figure out how to get everything done with macros and queries. But I suppose the more programming is done by VBA, the better your database will perform, so if someone can help me with re-numbering samples, that would be very helpful.

 

[Gasman]

You probably just need

set rst = fDAOGenericRst(strSQL)

You will have to copy the function into your DB though?

 

[autumnnew]

You probably just need

set rst = fDAOGenericRst(strSQL)

You will have to copy the function into your DB though?

Yes, the function would have to be copied, but I don't understand what this does, or what other changes I'd need to make for that to work. Is it a function that correctly converts SQL into a VBA string? I looked at the link when you originally posted it, but I don't know what it means or how to use it.

For anyone who is waiting on the code to be posted, I can't post over 10,000 characters, so I'm taking out some irrelevant vba comments so that I can get it under 10k characters to post it. I could also send the database as well, it's a FE and BE database.

 

[Gasman]

I did not post the link, the DBguy did. I have however used it in the past for exactly the same reasons you need to use it now.

This is what it does (copied from the link)

The following functions were from fellow MVP, Leigh Purvis (LPurvis). It automatically evaluates any parameters in your SQL when using the OpenRecordset (DAO) or the Open (ADO) method to avoid getting Run-time error '3061': Too few parameters.

No changes to your sql, just replace you set rst = with my version.
It would help however if you understood what you were changing and why.

 

[autumnnew]

I did not post the link, the DBguy did. I have however used it in the past for exactly the same reasons you need to use it now.

This is what it does (copied from the link)


No changes to your sql, just replace you set rst = with my version.
It would help however if you understood what you were changing and why.

Okay, I changed it and added the function as a new module, but I got this error. Is that what I needed to do? It is supposed to have the same name or a different name?

 

[Gasman]

You cannot have a module the same name as a function/sub.

Name the Module SQLFunctions and put any other ones you find you need in that module.

 

[autumnnew]

You cannot have a module the same name as a function/sub.

Name the Module SQLFunctions and put any other ones you find you need in that module.

Ah, thank you very much! That has gotten me past this bug. There are more bugs (I knew there would be), but I should be able to work through these. If not, I'll be back here

Thanks again.