Referring to a text box on my Access form

Using the event procedure below which executes a pass through query how would i tell the query to delete the record which is showing in a text box on my form. Usually i would use something like = Forms![DeleteImages]![CheckA but that doesn't work in this instance.
The form's name is DeleteImages and the text box name is CheckA.  

Dim dbs As Database
Dim qdfPassThrough As QueryDef
Dim qdfTemp As QueryDef

Set dbs = CurrentDb()

Set qdfPassThrough = dbs.CreateQueryDef("DeleteFormA")

qdfPassThrough.Connect = _
"ODBC;DSN=NewEis1;Description=NewEis1;APP=2007 Microsoft Office system;DATABASE=EIS;Trusted_Connection=Yes;TABLE=dbo.SCANFORMA"


qdfPassThrough.SQL = "DELETE * FROM SCANFORMA WHERE SCANFORMA.IMAGE = '000000006467'"

qdfPassThrough.ReturnsRecords = No

qdfPassThrough.Execute

dbs.QueryDefs.Delete "DeleteFormA"
dbs.Close
    'delete object variable
Set dbs = Nothing
restockettAsked:
Who is Participating?
 
omgangConnect With a Mentor IT ManagerCommented:
Chr(34) is a quotation mark, e.g. "
I should have used an apostrophe (silly me).
qdfPassThrough.SQL = "DELETE * FROM SCANFORMA WHERE SCANFORMA.IMAGE = " & Chr(39) & strCriteria & Chr(39)
OM Gang
0
 
jppintoCommented:
Did you've tryed like this?

= Forms![DeleteImages]![CheckA].Text
0
 
omgangIT ManagerCommented:
Dim dbs As Database
Dim qdfPassThrough As QueryDef
Dim qdfTemp As QueryDef
Dim strCriteria As String

Set dbs = CurrentDb()

Set qdfPassThrough = dbs.CreateQueryDef("DeleteFormA")

qdfPassThrough.Connect = _
"ODBC;DSN=NewEis1;Description=NewEis1;APP=2007 Microsoft Office system;DATABASE=EIS;Trusted_Connection=Yes;TABLE=dbo.SCANFORMA"

strCriteria = Me.CheckA

'qdfPassThrough.SQL = "DELETE * FROM SCANFORMA WHERE SCANFORMA.IMAGE = '000000006467'"
'qdfPassThrough.SQL = "DELETE * FROM SCANFORMA WHERE SCANFORMA.IMAGE = " & Chr(34) & strCriteria & Chr(34)

qdfPassThrough.ReturnsRecords = No

qdfPassThrough.Execute

dbs.QueryDefs.Delete "DeleteFormA"
dbs.Close
    'delete object variable
Set dbs = Nothing

OM Gang
0
 
omgangIT ManagerCommented:
Sorry, should have removed the comment
'qdfPassThrough.SQL = "DELETE * FROM SCANFORMA WHERE SCANFORMA.IMAGE = '000000006467'"
qdfPassThrough.SQL = "DELETE * FROM SCANFORMA WHERE SCANFORMA.IMAGE = " & Chr(34) & strCriteria & Chr(34)


Note that you could just also do
qdfPassThrough.SQL = "DELETE * FROM SCANFORMA WHERE SCANFORMA.IMAGE = " & Chr(34) & Me.CheckA & Chr(34)

OM Gang
0
 
restockettAuthor Commented:
I got an odbc --call failed error when i use the code you suggested

also is Chr(34) a bracket?  or an appostrophe?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.