Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jul 2007
    Thanked 0 Times in 0 Posts

    Code Help (Access 97)

    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"

  2. #2
    2 Star Lounger
    Join Date
    Apr 2001
    Wellington, New Zealand
    Thanked 0 Times in 0 Posts

    Re: Code Help (Access 97)

    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:

    <font color=6495ed>
    Dim strSQL as String

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

    DoCmd.RunSQL strSQL

    </font color=6495ed>

    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:

    <font color=6495ed>DoCmd.RunSQL "Delete * from IssuedTo where [voucher#] = " & RST</font color=6495ed>

    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?



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts