Problem constructing an SQL sentence by code

[Jose Garcia]

Hi,

I want to construct a SQL sentence using VBA. Basically, the data that
compund the INSERTsentence belong to subform fields.

The data types of the fields are the following:
- Fecha --> (Date type)
- Designacion --> (String type)
- Numero --> (String type)
- Hoja -->(String type)
- Revision -->(Number type)
- Fecha_ult_revision-->(Date type)

I have tried to create the following expression, and code:

strSQL = "INSERT INTO Control (Fecha, Designacion, Descripcion, Numero,
Hoja, Revision, Fecha_ult_revision) VALUES (#" &
Format(Me!vigente.Form.Controls("Fecha"), "dd/mm/yyyy") & "#,'" &
Me!vigente.Form.Controls("Designacion") & "','" &
Me!vigente.Form.Controls("Descripcion") & "','" &
Me!vigente.Form.Controls("Numero") & "','" & Me!vigente.Form.Controls("Hoja")
& "'," & Me!vigente.Form.Controls("Revision") & ",#" &
Format(Me!vigente.Form.Controls("Fecha_ult_revision"), "dd/mm/yyyy") & "#)"

CurrentDb.Execute strSQL, dbFailOnError


But for my surprise, gives the following error: "It has produced '2465' in
runtime: Microsoft Office Access cannot find the 'vigente' field which is
referenced in a expresion".

I am shocked because Access doesn´t detect de subform name, but truthly has
this name.

I would very grateful if anybody could help me to solve it.

Regards

Jose

 

[Stefan Hoffmann]

h Jose,

I want to construct a SQL sentence using VBA. Basically, the data that
compund the INSERTsentence belong to subform fields.

The data types of the fields are the following:
- Fecha --> (Date type)
- Designacion --> (String type)
- Numero --> (String type)
- Hoja -->(String type)
- Revision -->(Number type)
- Fecha_ult_revision-->(Date type)

I have tried to create the following expression, and code:
strSQL = "INSERT INTO Control (Fecha, Designacion, Descripcion, Numero,
Hoja, Revision, Fecha_ult_revision) VALUES (#" &
Format(Me!vigente.Form.Controls("Fecha"), "dd/mm/yyyy") & "#,'" &
Me!vigente.Form.Controls("Designacion") & "','" &
Me!vigente.Form.Controls("Descripcion") & "','" &
Me!vigente.Form.Controls("Numero") & "','" & Me!vigente.Form.Controls("Hoja")
& "'," & Me!vigente.Form.Controls("Revision") & ",#" &
Format(Me!vigente.Form.Controls("Fecha_ult_revision"), "dd/mm/yyyy") & "#)"

I would rewrite it, but you can use

Debug.Print strSQL

to output the SQL string in the immediate window (Ctrl+G). Copy this SQL
into a new query to find the problem.

I am shocked because Access doesn´t detect de subform name, but truthly has
this name.

If the reference is part of the SQL string then the error is correct as
Execute does not resolve it as DoCmd.RunSQL does.

As a start it would use:

Dim strDescripcion As String
Dim strDesignacion As String
Dim strFecha As String
Dim strFecha_ult_revision As String
Dim strHoja As String
Dim strNumero As String
Dim strRevision As String
Dim strSQL As String

strFecha = SqlDate(Me!vigente.Form.Controls("Fecha"))
strDesignacion = SqlQuote(Me!vigente.Form.Controls("Designacion"))
strDescripcion = SqlQuote(Me!vigente.Form.Controls("Descripcion"))
strNumero = SqlQuote(Me!vigente.Form.Controls("Numero"))
strHoja = SqlQuote(Me!vigente.Form.Controls("Hoja"))
strRevision = SqlQuote(Me!vigente.Form.Controls("Revision"))
strFecha_ult_revision = SqlDate( _
Me!vigente.Form.Controls("Fecha_ult_revision")

strSQL = "INSERT INTO Control (" & _
"Fecha, " & _
"Designacion, " & _
"Descripcion, " & _
"Numero, " & _
"Hoja, " & _
"Revision, " & _
"Fecha_ult_revision" & _
") VALUES (" & _
strFecha & ", " & _
strDesignacion & ", " & _
strDescripcion & ", " & _
strNumero & ", " & _
strHoja & ", " & _
strRevision & ", " & _
strFecha_ult_revision & _
");"

CurrentDb.Execute strSQL, dbFailOnError


Public Function SqlDate(ADate As Variant) As String

Dim Result As String

If ADate Is Nothing Then
Result = "NULL"
Else
Result = Format(ADate, "#mm/dd/yyyy#")
End If

SqlDate = Result

End Function


Public Function SqlQuote(AString As Variant) As String

Dim Result As String

If AString Is Nothing Then
Result = "NULL"
Else
Result = "'" & _
Replace(AString, "'", "''") & _
"'"
End If

SqlDate = Result

End Function

mfG
--> stefan <--

 

[Jose Garcia]

As a start it would use:

Dim strDescripcion As String
Dim strDesignacion As String
Dim strFecha As String
Dim strFecha_ult_revision As String
Dim strHoja As String
Dim strNumero As String
Dim strRevision As String
Dim strSQL As String

strFecha = SqlDate(Me!vigente.Form.Controls("Fecha"))
strDesignacion = SqlQuote(Me!vigente.Form.Controls("Designacion"))
strDescripcion = SqlQuote(Me!vigente.Form.Controls("Descripcion"))
strNumero = SqlQuote(Me!vigente.Form.Controls("Numero"))
strHoja = SqlQuote(Me!vigente.Form.Controls("Hoja"))
strRevision = SqlQuote(Me!vigente.Form.Controls("Revision"))
strFecha_ult_revision = SqlDate( _
Me!vigente.Form.Controls("Fecha_ult_revision")

strSQL = "INSERT INTO Control (" & _
"Fecha, " & _
"Designacion, " & _
"Descripcion, " & _
"Numero, " & _
"Hoja, " & _
"Revision, " & _
"Fecha_ult_revision" & _
") VALUES (" & _
strFecha & ", " & _
strDesignacion & ", " & _
strDescripcion & ", " & _
strNumero & ", " & _
strHoja & ", " & _
strRevision & ", " & _
strFecha_ult_revision & _
");"

CurrentDb.Execute strSQL, dbFailOnError


Public Function SqlDate(ADate As Variant) As String

Dim Result As String

If ADate Is Nothing Then
Result = "NULL"
Else
Result = Format(ADate, "#mm/dd/yyyy#")
End If

SqlDate = Result

End Function


Public Function SqlQuote(AString As Variant) As String

Dim Result As String

If AString Is Nothing Then
Result = "NULL"
Else
Result = "'" & _
Replace(AString, "'", "''") & _
"'"
End If

SqlDate = Result

End Function

mfG
--> stefan <--

Hi, Stephan

Now run ok, but still there is a bit problem, (at the beginning, I didn´t
explain properly what I wanted).

I want to increase the number of the Revision field in 1, (revision +1), and
in the field Fecha_ult_rev I want to obtain the current Date (dd-mm-yyyy)

regards

 

[Stefan Hoffmann]

hi Jose,

Now run ok, but still there is a bit problem, (at the beginning, I didn´t
explain properly what I wanted). Ok.

I want to increase the number of the Revision field in 1, (revision +1), and
in the field

Makes no sense to me when you want to insert a record instead of
updating it. Otherwise you can simply add 1 before constructing your SQL
statement.

Maybe your looking for something like this:

UPDATE Control
SET Revision = Revision + 1,
Fecha_ult_rev = Now()
WHERE Numero = ?

mfG
--> stefan <--