Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simplify code? (Access 2000)

    I have a function that has 10 updates for 10 different affiliates. Is there any way to simplify it, and write one or two lines of code?

    My function is the folowing:

    Public Function Updatings()
    CurrentDb.Execute SQLUpdate & " SET products.branch0 =

    products1.branch0, products.items0 = products1.items0"

    CurrentDb.Execute SQLUpdate & " SET products.branch1 =

    products1.branch1, products.items1 = products1.items1"

    CurrentDb.Execute SQLUpdate & " SET products.branch2 =

    products1.branch2, products.items2 = products1.items2"

    CurrentDb.Execute SQLUpdate & " SET products.branch3 =

    products1.branch3, products.items3 = products1.items3"

    CurrentDb.Execute SQLUpdate & " SET products.branch4 =

    products1.branch4, products.items4 = products1.items4"

    CurrentDb.Execute SQLUpdate & " SET products.branch5 =

    products1.branch5, products.items5 = products1.items5"
    etc etc.

    End Function

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

    Re: Simplify code? (Access 2000)

    Public Function Updatings()
    Dim i As Integer
    Dim strSet As String

    ' Loop to build strSet
    For i = 1 To 5 ' or whatever
    strSet = strSet & ", products.branch" & i & " = products1.branch" & i
    strSet = strSet & ", products.items" & i & " = products1.items" & i
    Next i

    ' Get rid of first ","
    strSet = Mid(strSet, 2)

    CurrentDb.Execute SQLUpdate & " SET " & strSet
    End Function

Posting Permissions

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