RunSQL VBA Syntax

[latex88]

I have such hard time with the syntax. I'll be grateful if someone can provide a link where I can learn about various syntax when referencing variables in various functions.

I'm trying to delete a record from a table that is trigger by a button in a continuous form based on an ID found via dlookup. I was able to find the ID OK using Dlookup (which was amazing), but the DoCmd.RunSQL is killing me. Below is what I have so far. When I tried to run it, it's prompting for RecordToBeDeleted.

Dim RecordToBeDeleted As Integer
Dim strSql As String
RecordToBeDeleted = DLookup("ID", "myTable1", "[ID]= ([txtID])")
strSql = "Delete * FROM [myTable2] WHERE ([myTable2.ID] = RecordToBeDeleted )"

DoCmd.RunSQL strSql

 

[jdraw]

http://www.techonthenet.com/access/functions/index.php

 

[latex88]

Thanks, jdraw for the link. It does provide basic syntax for various functions with regards to different datatype, but I don't see syntax that deal with variables, which is I am really after.

 

[jdraw]

There are many good examples at Martin Green's site
Each of his examples is a great learning tool.

http://www.fontstuff.com/access/acctut14.htm

 

[latex88]

There are many good examples at Martin Green's site
Each of his examples is a great learning tool.

http://www.fontstuff.com/access/acctut14.htm

Thank you for the useful link. I still couldn't quite figure out, but I broke it in two different lines to below and it worked.

Dim RecordToBeDeleted As Integer
Dim strSql As String
RecordToBeDeleted = DLookup("ID", "myTable1", "[ID]= ([txtID])")
strSql = "Delete * FROM [myTable2] WHERE myTable2.[ID] = "

DoCmd.RunSQL strSql & RecordToBeDeleted

 

[jdraw]

Did you go thru his sql stuff, and get to this one????

http://www.fontstuff.com/access/acctut17.htm

 

[latex88]

Yes, but I couldn't quite figure out the exact syntax when using a numeric variable in the conditional statement.

 

[jdraw]

with numbers there are no quotes
with text you need quotes to enclose the text
with dates you need #s to enclose the date

 

[latex88]

with numbers there are no quotes
with text you need quotes to enclose the text
with dates you need #s to enclose the date

That's what I did originally, as seen on top. I have remove the parenthesis, tried quotes, quotes at different places. None worked until I broke out the variable away from the string.

 

[jdraw]

RecordToBeDeleted = DLookup("ID", "myTable1", "[ID]= ([txtID])")
strSql = "Delete * FROM [myTable2] WHERE ([myTable2.ID] =" & RecordToBeDeleted )

DoCmd.RunSQL strSql

Sorry I haven't been back to the first post. Try it.

 

[latex88]

That works great. When it works, it sure seems logical, but it's always getting there, that's tough, at least for me. I'm still learning. Thanks for your links and the final solution.