Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

Access not responding after running recordset update code

Hi,

I have some code that updates a field in a table based on fields in another table. The code is doing what I want it to do, except that it makes access not respond. After I run this code and wait for a while and then force Access to quit, when I open it back up, the fields are filled in.

How do I stop access from not responding?
Public Sub Updatesizes()
Dim DB As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim var As String
 
Set DB = CurrentDb
Set rs1 = DB.OpenRecordset("tbl_Product")
 
 
rs1.MoveFirst
Do While Not rs1.EOF
        
  'Set rs2 = db.OpenRecordset("SELECT tbl_Product.str_AvailableSizes FROM tbl_Product")
  'rs2.MoveFirst
 
  'Do While Not rs2.EOF
 
        If rs1![str_SizeCategory] = "A" Then
        Debug.Print rs1!str_SMSTY
            var = rs1!str_SMSTY
            Debug.Print var
            If DLookup("SMV02", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = "XS,"
            End If
            
            If DLookup("SMV03", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "S,"
            End If
            
            If DLookup("SMV04", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "M,"
            End If
            
            If DLookup("SMV05", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "L,"
            End If
            
            If DLookup("SMV06", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "XL,"
            End If
            
            If DLookup("SMV07", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "2XL,"
            End If
            
            If DLookup("SMV08", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "3XL,"
            End If
            
            If DLookup("SMV09", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "4XL,"
            End If
            
            If DLookup("SMV10", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "5XL,"
            End If
            
            
            If DLookup("SMV11", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "6XL,"
            End If
            
            If DLookup("SMV12", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "and up"
            End If
        
        End If
        
        If rs1![str_SizeCategory] = "B" Then
            var = rs1!str_SMSTY
            If DLookup("SMV02", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            
            strSQL = "26W,"
            End If
            
            If DLookup("SMV03", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "28W,"
            End If
            
            If DLookup("SMV04", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "30W,"
            End If
            
            If DLookup("SMV05", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "32W,"
            End If
            
            If DLookup("SMV06", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            
            strSQL = strSQL & "34W,"
            End If
            
            If DLookup("SMV07", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "36W,"
            End If
            
            If DLookup("SMV08", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "38W,"
            End If
            
            If DLookup("SMV09", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "40W,"
            End If
            
            If DLookup("SMV10", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "42W,"
            End If
            
            
            If DLookup("SMV11", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "44W,"
            End If
            
            If DLookup("SMV12", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "46W,"
            End If
            
            If DLookup("SMV13", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "48W,"
            End If
            
            If DLookup("SMV14", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "50W,"
            End If
            
            If DLookup("SMV15", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "52W,"
            End If
            
            If DLookup("SMV16", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "54W,"
            End If
            
            If DLookup("SMV17", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "56W,"
            End If
            
            If DLookup("SMV18", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "58W,"
            End If
            
            If DLookup("SMV19", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "60W,"
            End If
            
            If DLookup("SMV20", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "62W,"
            End If
            
            If DLookup("SMV21", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "64W,"
            End If
            
            If DLookup("SMV22", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "66W,"
            End If
            
            If DLookup("SMV23", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "68W,"
            End If
            
            If DLookup("SMV24", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "70W,"
            End If
        End If
        
        If rs1![str_SizeCategory] = "C" Then
            var = rs1!str_SMSTY
            
            If DLookup("SMV02", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            
            strSQL = "32,"
            End If
            
            If DLookup("SMV03", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "34,"
            End If
            
            If DLookup("SMV04", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "36,"
            End If
            
            If DLookup("SMV05", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "38,"
            End If
            
            If DLookup("SMV06", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            
            strSQL = strSQL & "40,"
            End If
            
            If DLookup("SMV07", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "42,"
            End If
            
            If DLookup("SMV08", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "44,"
            End If
            
            If DLookup("SMV09", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "46,"
            End If
            
            If DLookup("SMV10", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "48,"
            End If
            
            
            If DLookup("SMV11", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "50,"
            End If
            
            If DLookup("SMV12", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "52,"
            End If
            
            If DLookup("SMV13", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "54,"
            End If
            
            If DLookup("SMV14", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "56,"
            End If
            
            If DLookup("SMV15", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "58,"
            End If
            
            If DLookup("SMV16", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "60,"
            End If
            
            If DLookup("SMV17", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "62,"
            End If
            
            If DLookup("SMV18", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "64,"
            End If
            
            If DLookup("SMV19", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "66,"
            End If
            
            If DLookup("SMV20", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "68,"
            End If
            
            If DLookup("SMV21", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "70,"
            End If
            
            If DLookup("SMV22", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "72,"
            End If
            
            If DLookup("SMV23", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "74,"
            End If
            
            If DLookup("SMV24", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            
            strSQL = strSQL & "76,"
            End If
        
        End If
        
        If rs1![str_SizeCategory] = "D" Then
            var = rs1!str_SMSTY
            
       
            If DLookup("SMV02", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = "6,"
            End If
            
            If DLookup("SMV03", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "8,"
            End If
            
            If DLookup("SMV04", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "10,"
            End If
            
            If DLookup("SMV05", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "12,"
            End If
            
            If DLookup("SMV06", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "14,"
            End If
            
            If DLookup("SMV07", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "16,"
            End If
            
            If DLookup("SMV08", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "18,"
            End If
            
            If DLookup("SMV09", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "20,"
            End If
            
            If DLookup("SMV10", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "22,"
            End If
            
            
            If DLookup("SMV11", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "24,"
            End If
            
            If DLookup("SMV12", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "26,"
            End If
            
            If DLookup("SMV13", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "28,"
            End If
            
            If DLookup("SMV14", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "30,"
            End If
            
            If DLookup("SMV15", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "32,"
            End If
            
            If DLookup("SMV16", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "34,"
            End If
            
            If DLookup("SMV17", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "36,"
            End If
            
            If DLookup("SMV18", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "38,"
            End If
            
            If DLookup("SMV19", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "40,"
            End If
            
            If DLookup("SMV20", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "42,"
            End If
            
            If DLookup("SMV21", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "44,"
            End If
            
            If DLookup("SMV22", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "46,"
            End If
            
            If DLookup("SMV23", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "48,"
            End If
            
            If DLookup("SMV24", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "50,"
            End If
        
        End If
        
        If rs1![str_SizeCategory] = "F" Then
            var = rs1!str_SMSTY
            
            If DLookup("SMV02", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = "7,"
            End If
            
            If DLookup("SMV03", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "8,"
            End If
            
            If DLookup("SMV04", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "9,"
            End If
            
            If DLookup("SMV05", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "10,"
            End If
            
            If DLookup("SMV06", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "11,"
            End If
            
            If DLookup("SMV07", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "12,"
            End If
            
            If DLookup("SMV08", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "13,"
            End If
            
            If DLookup("SMV09", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "14,"
            End If
            
            If DLookup("SMV10", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "15,"
            End If
            
            
            If DLookup("SMV11", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "16,"
            End If
            
            If DLookup("SMV12", "ABS400F_MSTSTYLM", "SMSTY = " & """" & var & """") = "G" Then
            strSQL = strSQL & "17,"
            End If
        End If
        
        
        Debug.Print strSQL
        
                Dim strsql2 As String
                    strsql2 = "UPDATE tbl_Product SET tbl_Product.str_AvailableSizes =" & """" & strSQL & """"
                    strsql2 = strsql2 & " WHERE str_SMSTY = " & """" & rs1![str_SMSTY] & """"
                    Debug.Print strsql2
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL (strsql2)
                    DoCmd.SetWarnings True
                    strSQL = ""
 
  rs1.MoveNext
Loop
rs1.Close
Set rs1 = Nothing
 
End Sub

Open in new window

0
lericks3
Asked:
lericks3
  • 2
1 Solution
 
tbsgadiCommented:
Hi lericks3,
Remove the DoCmd.SetWarnings True and at the end of your code after Loop try adding

DoCmd.SetWarnings True
 Echo True
 DoCmd.Hourglass False

Good Luck!

Gary
0
 
lericks3Author Commented:
Do you mean that I should not have the DoCmd.SetWarning True at all?

I tried this and it worked better, but still freezes up after a minute or so. Mine was freezing up almost immediately.
0
 
tbsgadiCommented:
I just moved it to the end.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now