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

    Function Product Strings (Access 2000)

    Recently, on the present Forum,with the help of several persons as Charlotte,Hans ,etc i have found out quite a wonderful way to write my code for the different branches in the offices.This code used the following trick:
    Dim city As Long
    city = Forms![FOrderInformation]![office] - 1

    And then the code folllowed like:
    strSQL = "UPDATE Products SET " & _
    " products.branch" & city & " = products.branch" & city & " + " & StrCartons & strWhere

    etc etc

    This function works excellent ,we in our company are very happy since it saves us a lot of work.We dont need to hard code each office separately.
    We see now a further function in our programme, already obsoltee because of these new methods, and we would like to ask to help us to rearrange
    it in the same way as before

    Our function is the following:

    Public Function ProductStrings()

    Dim strSQL As String
    strSQL = " SELECT products.Productid, products.grade, products.code, products.size, products.branch0, products.items0, products.pack, products.branch1,

    products.branch2, products.branch3, products.branch4, products.branch5, products.branch6,product.branch7,products.itemso,p roducts.items1,

    products.items2, products.items3, products.items4, products.items5,products.items6,products.items7 FROM products"
    Dim strWhere As String
    Dim strVaP As String
    Dim strBuP As String
    Dim strBlP As String
    Dim strHaP As String
    Dim strPlP As String
    Dim strTaP As String
    Dim strTarP As String
    Dim strRsP As String

    strBuP = " WHERE(((products.branch0) > 0))ORDER BY products.grade ASC"
    strVaP = " WHERE(((products.branch1) > 0))ORDER BY products.grade ASC"
    strBlaP = " WHERE(((products.branch2) > 0))ORDER BY products.grade ASC"
    strHaP = " WHERE(((products.branch3) > 0))ORDER BY products.grade ASC"
    strPlP = " WHERE(((products.branch4) > 0))ORDER BY products.grade ASC"
    strTaP = " WHERE(((products.branch5) > 0))ORDER BY products.grade ASC"
    strTarP = " WHERE(((products.branch6) > 0))ORDER BY products.grade ASC"
    strRP = " WHERE(((products.branch7) > 0))ORDER BY products.grade ASC"

    Dim strDocName As String
    strDocName = "FOrderinformation"
    If IsOpen(strDocName) = True Then
    Select Case Forms![FOrderInformation]![office]
    Case 1
    strWhere = strBuP
    Case 2
    strWhere = strVaP
    Case 3
    strWhere = strBlP
    Case 4
    strWhere = strHaP
    Case 5
    strWhere = strPlP
    Case 6
    strWhere = strTaP
    Case 7
    strWhere = TarP
    Case 8
    strWhere = strRsP
    End Select
    ProductStrings = strSQL & strWhere
    End If
    End Function

    We use this function in the following way:
    Me.RecordSource = ProductStrings


    Will you help us rewrite it in the same manner as before?We hope it is possible to do it.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Function Product Strings (Access 2000)

    Try this:

    Public Function ProductStrings()

    Dim strSQL As String
    strSQL = " SELECT products.Productid, products.grade, products.code, products.size, products.branch0, products.items0, products.pack, products.branch1,

    products.branch2, products.branch3, products.branch4, products.branch5, products.branch6,product.branch7,products.itemso,p roducts.items1,

    products.items2, products.items3, products.items4, products.items5,products.items6,products.items7 FROM products

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function Product Strings (Access 2000)

    Thank you so much for your kind and so clever answer. I really think it makes wonders with our programme.I have applied it and it works
    excellent.
    May i ask you fir a little further help? When i tried to apply your suggestion to a code very similar to this, i somehow could not
    manage to get it right becasue i obtain the line of code called "bas" highlighted with red.I am wrong with my commas and brackets.Could you please have a look?
    AT first my new code made on the basis of your suggestions, and below the old function that i want to modify



    Public Function MainProductStrings()
    Dim city As Long
    city = Forms![FOrderInformation]![office] - 1
    Dim bas As String
    bas = " SELECT products.Productid, products.grade, products.code,products.size,products.pack, " & _
    " products.branch" & city ,& ", products.items" & city " & _
    " FROM Products - here i receive red highlighted letters from Access


    Dim strDocName As String
    strDocName = "FOrderinformation"
    If IsOpen(strDocName) = True Then
    bas = bas & " WHERE(((products.branch" & Forms![FOrderInformation]![office] & ") > 0))ORDER BY products.grade ASC"
    End If
    MainProductStrings = bas
    End Function





    Public Function OldMainProductStrings()
    Dim strOffice As String
    Dim strVaM As String
    Dim StrBuM As String
    Dim strBlM As String
    Dim strHaM As String
    Dim strPlM As String
    Dim strTaM As String
    Dim strTaM As String
    Dim strRsM As String

    StrBuM = " SELECT products.Productid, products.grade, products.size, products.branch0, products.item0 FROM products WHERE (((products.branch0) > 0))

    ORDER BY products.grade;"
    strVaM = " SELECT products.Productid, products.grade, products.size, products.branch1,products.items1 FROM products WHERE (((products.branch1) > 0))

    ORDER BY products.grade;"
    strBlM = " SELECT products.Productid, products.grade, products.size, products.branch2,products.items2 FROM products WHERE (((products.branch2) > 0))

    ORDER BY products.grade ;"
    strHaM = " SELECT products.Productid, products.grade, products.size, products.branch3,products.items3 FROM products WHERE (((products.branch3) > 0))

    ORDER BY products.grade ;"
    strPlM = " SELECT products.Productid, products.grade, products.size, products.branch4,products.items4 FROM products WHERE (((products.branch4) > 0))

    ORDER BY products.grade ;"
    strTaM = " SELECT products.Productid, products.grade, products.size, products.branch5,products.items5 FROM products WHERE (((products.branch5) > 0))

    ORDER BY products.grade ;"
    strTarM = " SELECT products.Productid, products.grade, products.size, products.branch6,products.items6 FROM products WHERE (((products.branch6) > 0))

    ORDER BY products.grade ;"
    strRsM = " SELECT products.Productid, products.grade, products.size, products.branch6,products.items6 FROM products WHERE (((products.branch7) > 0))

    ORDER BY products.grade ;"

    Dim strDocName As String
    strDocName = "FOrderinformation"
    If IsOpen(strDocName) = True Then
    Select Case Forms![FOrderInformation]![office]
    Case 1
    strOffice = StrBuM
    Case 2
    strOffice = strVaM
    Case 3
    strOffice = strBlaM
    Case 4
    strOffice = strHaM
    Case 5
    strOffice = strPl
    Case 6
    strOffice = strTrM
    Case 7
    strOffice = strTaM
    Case 8
    strOffice = strRsM

    End Select
    MainProductStrings = strOffice
    End If
    End Function


    P.S. if i am right i will not need to hard code each office separately, and i may add on aditional offices without caring to modify my code.
    Therefore i treasure so much the suggestions you have made

    Very best regards

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Function Product Strings (Access 2000)

    Maybe you didn't type it out correctly, but as it is written, this line
    " FROM Products - here i receive red highlighted letters from Access

    is missing a "
    " FROM Products"
    Regards
    John



  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Function Product Strings (Access 2000)

    That's ok, I see you may have a problem with an extra comma, your statement :

    bas = " SELECT products.Productid, products.grade, products.code,products.size,products.pack, " & _
    " products.branch" & city ,& ", products.items" & city " & _
    " FROM Products - here i receive red highlighted letters from Access

    should read:

    bas = " SELECT products.Productid, products.grade, products.code,products.size,products.pack, " & _
    " products.branch" & city & ", products.items" & city " & _
    " FROM Products - here i receive red highlighted letters from Access

    The problem was a comma after the first incidence of city on the second line, I believe this is the problem.
    HTH
    Pat

Posting Permissions

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