Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete function (Access 2000)

    How can I delete all the other entries when updating one of them? For example, if I am updating the field branch0, to delete all the other 9 branches, that is branch 1,branch2, etc ? My function is :
    Dim sql As String
    sql = " UPDATE products INNER JOIN products1 ON products.productid=products1.productid SET " & _
    " products1.branch0 = products.branch0"
    CurrentDb.Execute sql

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete function (Access 2000)

    You could use

    sql = " UPDATE products INNER JOIN products1 ON products.productid=products1.productid SET " & _
    " products1.branch0 = products.branch0, products1.branch1 = Null, products1.branch2 = Null, ..."

    You'll have to fill in the ... yourself for the rest of the branch fields.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete function (Access 2000)

    Thank you very much! Your delete addition is excellent and works in the line i have mentioned.I am trying now to improve my function and instead of enumerating each separate case with Select case statement, i used an integer and option box called office with the following result:

    Dim city As Integer
    city = Me!office - 1
    Dim sql As String
    sql = " UPDATE products INNER JOIN products1 ON products.productid=products1.productid SET " & _
    " products1.branch" & city & " = products.branch" & city & ""
    CurrentDb.Execute sql

    In this case, how should i apply the delete function to delete all the other branches?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete function (Access 2000)

    Your table design is not optimal, but I've told you that before, so I won't repeat the argument here.

    You could use code like this:

    Dim city As Integer
    Dim sql As String
    ' First part of SQL string
    sql = "UPDATE products INNER JOIN products1 ON products.productid=products1.productid SET "
    ' Loop through the cities
    For city = 0 To 9
    ' Value from form
    If city = Me!office - 1 Then
    sql = sql & "products1.branch" & city & " = products.branch" & city & ", "
    Else
    sql = sql & "products1.branch" & city & " = Null, "
    End If
    Next city
    ' Remove last ", "
    sql = Left(sql, Len(sql) - 2)
    ' Execute SQL string
    CurrentDb.Execute sql

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete function (Access 2000)

    Just to let you know that the function you have suggested is perfect !! Thank you very much !

  6. #6
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete function (Access 2000)

    Dear Hans

    As i have told you the function is excelent.Also i know my table needs an improvement but it is difficult for me to do it. now. On my table i have besides the 10 fields branches , also the ten fields called items.I wanted to do the same thing as with branch and i suceeded. But trying to combine them into one function gies me erors
    even though it should be possible since all the fields are in one and the same table.Can i combine the two functions into one?
    Private Function One_Click()
    Dim city As Integer
    Dim sql As String
    ' First part of SQL string
    sql = "UPDATE products INNER JOIN products1 ON products.productid=products1.productid SET "
    ' Loop through the cities
    For city = 0 To 9
    ' Value from form
    If city = Me!office - 1 Then
    sql = sql & "products1.branch" & city & " = products.branch" & city & ", "
    Else
    sql = sql & "products1.branch" & city & " = Null, "
    End If
    Next city
    ' Remove last ", "
    sql = Left(sql, Len(sql) - 2)
    ' Execute SQL string
    CurrentDb.Execute sql

    End Function
    Public Function Two()
    Dim city As Integer
    Dim sql As String
    Dim sql1 As String

    ' First part of SQL string
    sql = "UPDATE products INNER JOIN products1 ON products.productid=products1.productid SET "
    ' Loop through the cities
    For city = 0 To 9
    ' Value from form
    If city = Me!office - 1 Then
    sql = sql & "products1.items" & city & " = products.items" & city & ", "
    Else
    sql = sql & "products1.items" & city & " = Null, "
    End If
    Next city
    ' Remove last ", "
    sql = Left(sql, Len(sql) - 2)
    ' Execute SQL string
    CurrentDb.Execute sql


    End Function

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete function (Access 2000)

    Does this do what you want?

    Private Function One_Click()
    Dim city As Integer
    Dim sql As String
    ' First part of SQL string
    sql = "UPDATE products INNER JOIN products1 ON products.productid=products1.productid SET "
    ' Loop through the cities
    For city = 0 To 9
    ' Value from form
    If city = Me!Office - 1 Then
    sql = sql & "products1.branch" & city & " = products.branch" & city & ", "
    sql = sql & "products1.items" & city & " = products.items" & city & ", "
    Else
    sql = sql & "products1.branch" & city & " = Null, "
    sql = sql & "products1.items" & city & " = Null, "
    End If
    Next city
    ' Remove last ", "
    sql = Left(sql, Len(sql) - 2)
    ' Execute SQL string
    CurrentDb.Execute sql
    End Function

  8. #8
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete function (Access 2000)

    yes that was it and thank you so much for the wonderful help !

Posting Permissions

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