Use sql statement in code

[Shytown_Turk]

Hello,

I am trying to execute a SQL statemtment in code in which the where clause
is pulled from a hidden text box. The problem is that the where clause is a
number and I am getting an error message. I know the syntax is incorrect,
but I can't see my mistake.

SQL$ = "Delete tblAllocation_Asset.ImplementationID " _
& "FROM tblAllocation_Asset " _
& "WHERE (((tblAllocation_Asset.ImplementationID)= txtImpID"

Thanks,
Shaun

 

[fredg]

Hello,

I am trying to execute a SQL statemtment in code in which the where clause
is pulled from a hidden text box. The problem is that the where clause is a
number and I am getting an error message. I know the syntax is incorrect,
but I can't see my mistake.

SQL$ = "Delete tblAllocation_Asset.ImplementationID " _
& "FROM tblAllocation_Asset " _
& "WHERE (((tblAllocation_Asset.ImplementationID)= txtImpID"

Thanks,
Shaun

1) Is txtImpID is a control on the same form in which this code is
being run?
If so, the control's value must be concatenated into the SQL.

2) The parenthesis don't match. You have 3 open parenthesis but just 1
closing. They aren't needed anyway.

3) Try:
SQL$ = "Delete tblAllocation_Asset.ImplementationID " _
& "FROM tblAllocation_Asset " _
& "WHERE tblAllocation_Asset.ImplementationID = " & Me!txtImpID

 

[Klatuu]

The reference to the control has to be outside the quotes. And ALWAYS
reference your controls to your form. Also, don't use special characters in
name with the excption of the underscore. Are you aware this will delete all
rows in th table that match the value in txtImpID?

strSQL = "Delete * FROM tblAllocation_Asset " _
& "WHERE ImplementationID = " & Me.txtImpID

CurrentDb.Execute(strSQL), dbFailOnError

The last line is a much faster way than RunSQL and you don't have to deal
with setting Warnings on and off. That is because it doesn't go through the
Access UI, it goes directly to Jet.

 

[John W. Vinson]

Hello,

I am trying to execute a SQL statemtment in code in which the where clause
is pulled from a hidden text box. The problem is that the where clause is a
number and I am getting an error message. I know the syntax is incorrect,
but I can't see my mistake.

SQL$ = "Delete tblAllocation_Asset.ImplementationID " _
& "FROM tblAllocation_Asset " _
& "WHERE (((tblAllocation_Asset.ImplementationID)= txtImpID"

Thanks,
Shaun

For starters, you have three open parentheses and one close parenthesis.

I'd actually suggest appending the *value* in the textbox to the SQL string
rather than its name, and I'd also suggest abandoning the obsolete $ suffix:

Dim strSQL As String
strSQL = "Delete tblAllocation_Asset.ImplementationID " _
& "FROM tblAllocation_Asset " _
& "WHERE tblAllocation_Asset.ImplementationID= " & Me!txtImpID

If you want to keep the forms reference in the SQL it needs to be the full
forms reference: Forms!nameofform!txtImpID rather than just txtImpID.

John W. Vinson [MVP]