runsql command in acces VB return "Run time error 2342"

[stpgmn]

I'm executing the following procedure within Access mdb. The query that I'm trying to run works nolrmally when executed as Query.

Do anybody has any idea of reason of the error?.

Thanks a lot



Private Sub Form_BeforeInsert(Cancel As Integer)
Dim Istruz As Variant
Dim MaxOfVersione As Integer
Istruz = "SELECT Max(ControlloVersioniBDG.Versione) AS MaxOfVersione" _
& " FROM ControlloVersioniBDG" _
& " GROUP BY ControlloVersioniBDG.BDGType, ControlloVersioniBDG.Anno" _
& " HAVING (ControlloVersioniBDG.BDGType=[Forms]![F_BDG]![TipoBDG])" _
& " AND (ControlloVersioniBDG.Anno=[Forms]![F_BDG]![DetControlloVersioni])"

DoCmd.RunSQL Istruz
[Forms]![NewControlloVersioniBDG]![Versione].DefaultValue = MaxOfVersione + 1

End Sub

[ChipR]

You Dim MaxOfVersione, but you never set it equal to anything. The "AS MaxOfVersione" in your query just creates a column with that title, and you don't do anything with it. You might want to save the query and then use the DMax() function, or the DLookup() function.

[Stewart Ross]

Hi. Queries referring to form textboxes in their WHERE or HAVING clauses can cause run-time errors when run within the VBA environment. In particular, you may receive a 'too few parameters - expected 1' or somesuch error, which I think is what error 2342 is.

To resolve this, pass the value of the form control in the SQL statement, not the name of the control. If your form control holds a string value you need to pass its value inside single quotes, as shown in the HAVING clauses below:

 
& " HAVING (ControlloVersioniBDG.BDGType = " _
& "'" & [Forms]![F_BDG]![TipoBDG])  & "'"_
& " AND (ControlloVersioniBDG.Anno = '" & [Forms]![F_BDG]![DetControlloVersioni] & ')"
 

If the values are numbers and not strings you will not need the single quotes before and after the values.

-Stewart

[ChipR]

The error is actually due to using RunSQL for a query that is not an action query. RunSQL on a SELECT statement, nothing is going to happen. If you really want to do a SELECT, you could use DoCmd.OpenQuery, but I don't think that is the goal in this case.

[stpgmn]

Thankyou to all of you.

I realize that DoCmd.RunSQL doesn't works for SELECT query.

I change my procedure as follow using DoCmd.OpenQuery method and it works.
However, the query I execute return exactly what I expect, the question now is: how I can refer to 'MaxOfVersione' field returnd by the query. I have to put the query's result value (plus 1), to a field in a form.

Thanks again.

regards.
=========
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim MaxOfVesrione As Integer


DoCmd.OpenQuery "FindMaxOfVersione", , acReadOnly
[Forms]![NewControlloVersioniBDG]![Versione].Value = [FindMaxOfVersione]![MaxOfVersione] + 1

DoCmd.Close acQuery, "FindMaxOfVersione"

End Sub

[ChipR]

My first post (Post #2) addresses this. There is no field returned by the query, so you need to save the query and use a function to grab the value.