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,products.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.
