Solved

Access not responding after running recordset update code

Posted on 2009-05-02
4
295 Views
Last Modified: 2013-11-27
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
Comment
Question by:lericks3
  • 2
4 Comments
 
LVL 46

Accepted Solution

by:
tbsgadi earned 500 total points
ID: 24288944
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
 

Author Comment

by:lericks3
ID: 24291373
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
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24292787
I just moved it to the end.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question