Code Help (Access 97)

Topic

New Reply

I have 2 tables with identical fields. IssuedTo and ImportTempGRE. I would like to modify this code to compare the voucher# in ImportTemptbl then delete it from the IssuedTo table. This code will do this as written but it prompts me for the RST value. Which when I type in the value (Voucher#) it will delete that value from the IssuedTo Table. What I would like is for it to delete all the matching voucher #’s from the IssueTo table and not have it prompt the user. I’m pretty sure this can be fixed but I’m not to Sure how. All help is appreciated. Thanks.

Dim Rst As String
Dim IssuedTo As String
Dim ImportTemp As String
ImportTemp = “ImportTemptbl”
IssuedTo = “IssuedTo”
DoCmd.OpenTable “IssuedTo”
DoCmd.OpenTable “ImportTemptbl”

Rst = “[Tables]![ImportTemptbl]![Voucher#]”

DoCmd.RunSQL “Delete * from IssuedTo where [voucher#] = RST”

Reply | Quote

Replies

I think that you need to approach this problem from a slightly different angle. Instead of using criteria in your SQL (which probably won’t work the way you have it set up), use a Join between the two tables.

I would probably make the code to look something like this:

Dim strSQL as String

strSQL = “DELETE IssuedTo.* ” & _
“FROM IssuedTo INNER JOIN ImportTemptbl ” & _
“ON IssuedTo.[Voucher#] = ImportTemptbl.[Voucher#];”

DoCmd.RunSQL strSQL

What this code does is it creates the SQL in the variable strSQL that joins the two tables by voucher# and where any fields match in the two tables, it deletes the record from the IssuedTo table.

I haven’t tested this, but give it a try and see what happens (make a back-up of your tables first though just in case).

Incidentally the main problem that you were having in the code above was that you had the RST variable inclosed with the quotes of the SQL string you were trying to run.

If you changed the line to:

DoCmd.RunSQL “Delete * from IssuedTo where [voucher#] = ” & RST

It would use the variable, but you will still find that it probably generates an error, because the variable doesn’t contain a value, but rather contains a reference to a field in a table (but for which record?). This is why I would do it the way that I have presented it above.

One other question? Why do it in code? You could just create a DELETE query and just run that at the appropriate time?

Cheers

Jayden

Reply | Quote