?
Solved

Referring to a text box on my Access form

Posted on 2011-02-18
5
Medium Priority
?
1,121 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:restockett
  • 3
5 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 34929525
Did you've tryed like this?

= Forms![DeleteImages]![CheckA].Text
0
 
LVL 28

Expert Comment

by:omgang
ID: 34929545
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
 
LVL 28

Expert Comment

by:omgang
ID: 34929560
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
 

Author Comment

by:restockett
ID: 34929921
I got an odbc --call failed error when i use the code you suggested

also is Chr(34) a bracket?  or an appostrophe?
0
 
LVL 28

Accepted Solution

by:
omgang earned 1000 total points
ID: 34930015
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question