Clean it up...

[Zippersabrat]

This is mainly a formating question: I have the following wordy, messy code in a database. Code works great but I know there is a better way to write it. Can anyone offer some shortcuts for this code? Thanks!

Private Sub Form_Close()

Dim SQLA1 As String
Dim SQLA2 As String
Dim SQLA3 As String
Dim SQLA4 As String
Dim SQLA5 As String
Dim SQLA6 As String
Dim SQLA7 As String
Dim SQLA8 As String
Dim SQLA9 As String
Dim SQLAX As String

SQLA1 = "Update InventoryList " & _
            "SET [AreaAssignment]='Area1' " & _
            "WHERE ([LocationCode] Like 'BR*' OR [LocationCode] Like 'MC*' " & _
            "OR [LocationCode] Like 'B1*' OR [LocationCode] Like 'LR*' " & _
            "OR [LocationCode] Like 'G-A8*' OR [LocationCode] Like 'G-A9*' OR [LocationCode] Like 'G-Mis*')"

SQLA2 = "Update InventoryList " & _
            "SET [AreaAssignment]='Area2' " & _
            "WHERE ([LocationCode] Like 'G-A1*' OR [LocationCode] Like 'G-A2*' " & _
            "OR [LocationCode] Like 'G-A3*' OR [LocationCode] Like 'G-A4*' " & _
            "OR [LocationCode] Like 'G-A5*' OR [LocationCode] Like 'G-A6*' OR [LocationCode] Like 'G-A7*')"

SQLA3 = "Update InventoryList " & _
            "SET [AreaAssignment]='Area3' " & _
            "WHERE ([LocationCode] Like 'L1*')"

SQLA4 = "Update InventoryList " & _
            "SET [AreaAssignment]='Area4' " & _
            "WHERE ([LocationCode] Like 'L2*' OR [LocationCode] Like 'T-D*' " & _
            "OR [LocationCode] Like 'T-A*' OR [LocationCode] Like 'T-BLC*' " & _
            "OR [LocationCode] Like 'T-Sh*' OR [LocationCode] Like 'T-W*')"

SQLA5 = "Update InventoryList " & _
            "SET [AreaAssignment]='Area5' " & _
            "WHERE ([LocationCode] Like 'CA*' OR [LocationCode] Like 'G-Br*' " & _
            "OR [LocationCode] Like 'Mark*' OR [LocationCode] Like 'EXRm-S*')"

SQLA6 = "Update InventoryList " & _
            "SET [AreaAssignment]='Area6' " & _
            "WHERE ([LocationCode] Like 'Ph-D*' OR [LocationCode] Like 'Ph-LC*' " & _
            "OR [LocationCode] Like 'Ph-Co*' OR [LocationCode] Like 'Ph-Mis*' " & _
            "OR [LocationCode] Like 'LBO*' OR [LocationCode] Like 'LBT*')"

SQLA7 = "Update InventoryList " & _
            "SET [AreaAssignment]='Area7' " & _
            "WHERE ([LocationCode] Like 'Ph-UC*')"

SQLA8 = "Update InventoryList " & _
            "SET [AreaAssignment]='Area8' " & _
            "WHERE ([LocationCode] Like 'IA*' OR [LocationCode] Like 'P1*' " & _
            "OR [LocationCode] Like 'P2*' OR [LocationCode] Like 'Sx-*')"

SQLA9 = "Update InventoryList " & _
            "SET [AreaAssignment]='Area9' " & _
            "WHERE ([LocationCode] Like 'T-Fr*' OR [LocationCode] Like 'T-LC*' " & _
            "OR [LocationCode] Like 'T-RC*' OR [LocationCode] Like 'T-RD*' " & _
            "OR [LocationCode] Like 'T-UC1*' OR [LocationCode] Like 'T-UC2*')"
            
SQLAX = "Update InventoryList " & _
            "SET [AreaAssignment]='Area10' " & _
            "WHERE ([LocationCode] Like 'T-UC3*' OR [LocationCode] Like 'T-UC4*' " & _
            "OR [LocationCode] Like 'T-UC6*' OR [LocationCode] Like 'T-View*' " & _
            "OR [LocationCode] Like 'T-UCD*' OR [LocationCode] Like 'T-Cou*' " & _
            "OR [LocationCode] Like 'T-UC5*' OR [LocationCode] Like 'T-Mu*' OR [LocationCode] Like 'T-Mis*')"

     
DoCmd.SetWarnings False
DoCmd.RunSQL SQLA1
DoCmd.RunSQL SQLA2
DoCmd.RunSQL SQLA3
DoCmd.RunSQL SQLA4
DoCmd.RunSQL SQLA5
DoCmd.RunSQL SQLA6
DoCmd.RunSQL SQLA7
DoCmd.RunSQL SQLA8
DoCmd.RunSQL SQLA9
DoCmd.RunSQL SQLAX
DoCmd.SetWarnings True

End Sub

 

[plog]

I don't think its your code that needs cleaned up, its your table structure. UPDATE tables are usually a sign of a poor structure.

It seems you should have a table of Location Codes (LocationCodes). That table would have a field for Location Code values and a field for AreaAssignment values so that you can map the two to each other. Then, instead of this code, you just create a query to map the InventoryList to LocationCodes.

 

[namliam]

I don't think its your code that needs cleaned up, its your table structure. UPDATE tables are usually a sign of a poor structure.

QFT


Or if for some reason you are stuck doing it this way....
This may improve on readibility:

SQLA1 = "Update InventoryList " & _
            "SET [AreaAssignment]='Area1' " & _
            "WHERE ([LocationCode] Like 'BR*'   OR [LocationCode] Like 'MC*'   " & _
                "OR [LocationCode] Like 'B1*'   OR [LocationCode] Like 'LR*'   " & _
                "OR [LocationCode] Like 'G-A8*' OR [LocationCode] Like 'G-A9*' " & _ 
                "OR [LocationCode] Like 'G-Mis*')"

Or even more so..

SQLA1 = "Update InventoryList " & _
            "SET [AreaAssignment]='Area1' " & _
            "WHERE ([LocationCode] Like 'BR*'   " & _
                "OR [LocationCode] Like 'MC*'   " & _
                "OR [LocationCode] Like 'B1*'   " & _
                "OR [LocationCode] Like 'LR*'   " & _
                "OR [LocationCode] Like 'G-A8*' " & _
                "OR [LocationCode] Like 'G-A9*' " & _ 
                "OR [LocationCode] Like 'G-Mis*')"

Or.... if you want it "really clean", You can make a table

AreaName   LikeText
Area1      BR*
Area1      MC*
Area2      G-A1*

Etc...
Then use code like so:

Dim SQL As String
Dim rs As DAO.Recordset
Dim myArea As String

Set rs = CurrentDb.OpenRecordset("Select * from YourTable order by AreaName")

myleadsql = "Update InventoryList SET [AreaAssignment]= "
myArea = ""
Do While Not rs.EOF
    If myArea <> rs!AreaName Then
        Debug.Print SQL
        CurrentDb.Execute SQL
        myArea = rs!AreaName
        SQL = myleadsql & "'" & myArea & "' where 1=2 "
    End If
    SQL = SQL & "Or [LocationCode] like '" & rs!Liketext & "' " & String(10 - Len(rs!Liketext), " ")
    
    rs.MoveNext
Loop
Debug.Print SQL
CurrentDb.Execute SQL
rs.Close
Set rs = Nothing

To generate your SQL from the table and execute it, easy to maintain for users or yourself as well.

Though if you can, really go for a location table instead of this updating nonsense as per Plog's earlier suggestion.

 

[Zippersabrat]

Thanks to both responders. This is why I love to post these questions here. You make me rethink how I am using the database. Plog, I am going to go with your table suggestion. Once I read your post it was very obvious and something I would have done had I done the Area Assignments from the beginning but it is a new thing and it just did not occur to me.

Thanks again to both of you!