Page 1 of 1

[Solved] Problem with DLookup (Access2Base) in MySQL

PostPosted: Sat Aug 20, 2016 10:10 pm
by inizul
When I execute de following code:

Code: Select all   Expand viewCollapse view
Sub IPF_ActualizarDenominacionComponente ()
  Dim sCodigo as string
  Dim sDenominacion as string
 
  sCodigo=getValue("Forms!InformePreserieFicha!codigo")
  sDenominacion=Application.DLookup("[Denominacion]", "[piezas]","[Codigo]='" & scodigo & "'")
  '             [Application.]DLookup(expression,       domain           [, criteria]             [, orderclause]))
  setValue("Forms!InformePreserieFicha!Componente.Value", sDenominacion)
End Sub


The program ABORT (see atached image):

Code: Select all   Expand viewCollapse view
SQL=SELECT TOP1 'Denominacion' AS 'TEMP48341' FROM 'piezas' WHERE 'Codigo'='K57M411200'


Is the SQL query correct? Or am I doing something wrong?

Database type: MySQL (JDBC) 5.7.11 x86
Database Engine: MyISAM
Server: 5.7.11 - MySQL Community Server (GPL)
LibreOffice: 5.1.4.2

Re: Problem with DLookup (Access2Base) in MySQL

PostPosted: Sat Aug 20, 2016 10:46 pm
by inizul
But this code work well

Code: Select all   Expand viewCollapse view
Sub ActualizarDenominacionComponente()
' Busca en la tabla de piezas la denominación a partir del código
' Ejecuta: la actualización del registro
  Dim sCodigo as string
  Dim sDenominacion as string
  Dim oCodigo as Object
  Dim oFormulario as object
  Dim oControl
  Dim oRecordset as object

  set oFormulario=Forms("InformePreserieFicha")
  set oControl=oFormulario.Controls("codigo")
  sCodigo=oControl.Value
  Set oRecordset = Application.CurrentDb().OpenRecordset("SELECT [Denominacion] FROM [piezas] WHERE [Codigo]='" & sCodigo &"'")
  'msgbox "RecordCount: " & oRecordset.RecordCount
  sDenominacion=oRecordset.Fields(0).Value
  set oControl=oFormulario.Controls("Componente")
  oControl.Value= sDenominacion
  oRecordset.mClose
End sub
...

Re: Problem with DLookup (Access2Base) in MySQL

PostPosted: Mon Aug 22, 2016 12:51 pm
by JPL
Hi Inizul,

indeed there is a bug in Access2Base/LO 5.1 caused by the fact that MySQL does not recognize the "SELECT TOP 1" syntax as HSQLDB does.

It has been corrected in LO 5.2 after its mention HERE.

Thanks.
JPL

Re: Problem with DLookup (Access2Base) in MySQL

PostPosted: Mon Aug 22, 2016 5:42 pm
by inizul
Ups!!

Sorry for not correctly read the documentation :oops:

JPL: Thanks for the clarification.