SQL syntax with variables

Question

SQL syntax with variables

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

I'm building a database in Access, and I want to run an update query in vba .

I know the syntax a little bit, but it's giving me a bug.

The problem is that I try to pass some values to him by means of two variables, and it seems that he does not recognize those variables well.

The variables are of integer type.

The error that jumps me is the following:

The '3075' error occurred at runtime: syntax error (operator missing) in the query expression 'Where TBL_Residual_Name.Id_Residual_Name ='

The fields in the table are of type integer. The Id_Nombre_Residuo field is the primary key.

Can someone tell me where I'm wrong?

This is the code:

Private mIntIdResiduo, mIntTipoIdResiduo As Integer

Private Sub cmbTipoResiduo_Click()

    Me.txtTipoResiduo.Value = DLookup("[N_Tipo_Residuo]", "TBL_Tipo_Residuo", "[Id_Tipo_Residuo] =" & Me.cmbTipoResiduo.Value)
    mIntTipoIdResiduo = Me.cmbTipoResiduo.Value

    SQL = "UPDATE TBL_Nombre_Residuos SET TBL_Nombre_Residuos.Tipo_Residuo = " & mIntTipoIdResiduo & _
    "WHERE TBL_Nombre_Residuos.Id_Nombre_Residuo =" & mIntIdResiduo

    DoCmd.RunSQL SQL
End Sub

sql vba ms-access

asked by Fco. Javier 13.03.2017 в 14:53

source

2 answers

When to use clousures? CREATE TABLE mysql query does not work

score 2 · Answer 1

I have managed to solve the problem.

The error was in the syntax. It seems that SQL in Access does not accept the passage of parameters by variable very well. Instead I changed the variables directly by the objects that store the values. The code would be like this.

DoCmd.RunSQL "UPDATE TBL_Nombre_Residuos SET TBL_Nombre_Residuos.Tipo_Residuo = [Formularios]![FM_Actualizar_Residuo]![cmbTipoResiduo]" _
& "WHERE (([TBL_Nombre_Residuos]![Id_Nombre_Residuo]=[Formularios]![FM_Actualizar_Residuo]![cmbElegirResiduo]));"

cmbTypeResidue is a combo box and cmbElegirResiduo is another combo box. The click event works well, but it seems that you have to call the object, not a variable.

Greetings, and thanks to everyone.

score 1 · Answer 2

You have included the variables outside the procedure environment.

You have a procedure called cmbTipoResiduo_Click that inside it does not contain any declaration of variables, being out of the procedure you do not have access to it.

In addition, the declaration of mIntIdResiduo and mIntTipoIdResiduo you have made privadas , to make it more difficult if possible to cmbTipoResiduo_Click that can access them.

To include these variables you should only have them within the procedure cmbTipoResiduo_Click .

Private Sub cmbTipoResiduo_Click()

    Private mIntIdResiduo, mIntTipoIdResiduo As Integer

    Me.txtTipoResiduo.Value = DLookup("[N_Tipo_Residuo]", "TBL_Tipo_Residuo", "[Id_Tipo_Residuo] =" & Me.cmbTipoResiduo.Value)
    mIntTipoIdResiduo = Me.cmbTipoResiduo.Value

    SQL = "UPDATE TBL_Nombre_Residuos SET TBL_Nombre_Residuos.Tipo_Residuo = " & mIntTipoIdResiduo & _
    "WHERE TBL_Nombre_Residuos.Id_Nombre_Residuo =" & mIntIdResiduo

    DoCmd.RunSQL SQL
End Sub