Form parameters in vba with sql

[thirty]

Hi,

There is a error in the next piece of program.
The error is the red text below

strMySql_zoekenOpId = "SELECT tbldrager.Id, tbldrager.Naam, tbldrager.Soort, tbldrager.Aantal, tbldrager.Formaat, tbldrager.Kostprijs, tbldrager.LEVERANCIER, tbldrager.Verpakking " & _
"FROM tbldrager " & _
"WHERE (((tbldrager.Id)= [Formulieren]![frm_gegevensinvoer]![txtId])) " & _
"ORDER BY tbldrager.Naam DESC"

Dim myCnn As ADODB.Connection
Set myCnn = CurrentProject.Connection
Dim Sqltabeldrager As New ADODB.Recordset
Sqltabeldrager.ActiveConnection = myCnn
Sqltabeldrager.Open strMySql_zoekenOpId, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Sqltabeldrager.MoveFirst

I changed the red code by a number 14.
Everything is working well. I have no error.
I use a textarea called textId.

I do something wrong.
Can anybody help me?

Thanks in advance

 

[pr2-eugin]

When using Form controls inside VBA you have to concatenate the field values by using " and &.. Something like..

[COLOR=#008080][COLOR=SeaGreen]WHERE  (((tbldrager.Id)=[/COLOR] [/COLOR][COLOR=#ff0000][B][COLOR=Blue]" [/COLOR][/B][/COLOR][COLOR=#ff0000][B][COLOR=Blue][COLOR=Blue][B]& [/B][/COLOR][/COLOR][/B][Formulieren]![frm_gegevensinvoer]![txtId][/COLOR][COLOR=SeaGreen][COLOR=Blue][B]& [/B][/COLOR][/COLOR][COLOR=SeaGreen][B][COLOR=Blue]"[/COLOR][/B])) " &[/COLOR]

 

[JackKaptijn]

I agree with pr2-eugin but also think about the type of the field.
When it is numeric you can use
... WHERE (((tbldrager.Id)= " &[Formulieren]![frm_gegevensinvoer]![txtId]& ")) " ...
but when it is a text value you must use
... WHERE (((tbldrager.Id)= '" &[Formulieren]![frm_gegevensinvoer]![txtId]& "')) " ...

 

[thirty]

Thats great! Thanks alot for the quick reply.

 

[pr2-eugin]

You are most welcome..