lericks3
asked on
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?
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I just moved it to the end.
ASKER
I tried this and it worked better, but still freezes up after a minute or so. Mine was freezing up almost immediately.