Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

DoCmd.RunSQL memory error - Please help

I'm new to this group and an admitted MSAccess hack.
I'm trying to run the following loop in a click command button. It runs through twice successfully until it hits the command "DoCmd.RunSQL strSQL1" for the third time. Then I receive an application error 'The instruction at "0x0000000a" referenced memory at "0x0000000a". The memory could not be "read".'
I've tried chnaging the starting value of COUNTER with no difference. I've also created regular queries and ran them in succession successfully, so I'm sure it's by code. Do I need to somehow clear the memory before assigning the new value to strSQL1?

Counter = 1

While Counter < 81
stQryName = "qry_621_Delete Audit Level 2 and 3_Temp"
DoCmd.OpenQuery stQryName, acNormal, acEdit

strSQL1 = "INSERT INTO [tbl_Audit_Level 2 and 3_Temp] ( [Level], SubLevelID, UserID, UserName,"
strSQL1 = strSQL1 + " Level1Description, Level2Description, Description, AccessLevel )"
strSQL1 = strSQL1 + " SELECT tbl_SubLevels_Master.Level, tbl_SubLevels_Users.SubLevelID,"
strSQL1 = strSQL1 + " tbl_SubLevels_Users.UserID, tbl_Users.UserName, tbl_SubLevels_Master.Level1Description,"
strSQL1 = strSQL1 + " tbl_SubLevels_Master.Level2Description, tbl_SubLevels_Descriptions_1to60.Description_" & Counter & ","
strSQL1 = strSQL1 + " [tbl_Access Levels].AccessDescription"
strSQL1 = strSQL1 + " FROM tbl_Users LEFT JOIN (tbl_SubLevels_Master RIGHT JOIN ((tbl_SubLevels_Users"
strSQL1 = strSQL1 + " LEFT JOIN tbl_SubLevels_Descriptions_1to60 ON tbl_SubLevels_Users.SubLevelID"
strSQL1 = strSQL1 + " = tbl_SubLevels_Descriptions_1to60.SubLevelID) LEFT JOIN [tbl_Access Levels]"
strSQL1 = strSQL1 + " ON tbl_SubLevels_Users.Access_" & Counter & " = [tbl_Access Levels].AccessID)"
strSQL1 = strSQL1 + " ON tbl_SubLevels_Master.SubLevelID = tbl_SubLevels_Users.SubLevelID)"
strSQL1 = strSQL1 + " ON tbl_Users.UserID = tbl_SubLevels_Users.UserID"
strSQL1 = strSQL1 + " WHERE (((tbl_SubLevels_Users.Access_" & Counter & ")>0));"

MsgBox "strSQL1 set"

strSQL2 = "INSERT INTO [tbl_Audit_Level 2 and 3_Temp] ( [Level], SubLevelID, UserID, UserName,"
strSQL2 = strSQL2 + " Level1Description, Level2Description, Description, AccessLevel )"
strSQL2 = strSQL2 + " SELECT tbl_SubLevels_Master.Level, tbl_SubLevels_Users.SubLevelID,"
strSQL2 = strSQL2 + " tbl_SubLevels_Users.UserID, tbl_Users.UserName, tbl_SubLevels_Master.Level1Description,"
strSQL2 = strSQL2 + " tbl_SubLevels_Master.Level2Description, tbl_SubLevels_Descriptions_61to80.Description_" & Counter & ","
strSQL2 = strSQL2 + " [tbl_Access Levels].AccessDescription"
strSQL2 = strSQL2 + " FROM tbl_Users LEFT JOIN (tbl_SubLevels_Master RIGHT JOIN ((tbl_SubLevels_Users"
strSQL2 = strSQL2 + " LEFT JOIN tbl_SubLevels_Descriptions_61to80 ON tbl_SubLevels_Users.SubLevelID"
strSQL2 = strSQL2 + " = tbl_SubLevels_Descriptions_61to80.SubLevelID) LEFT JOIN [tbl_Access Levels]"
strSQL2 = strSQL2 + " ON tbl_SubLevels_Users.Access_" & Counter & " = [tbl_Access Levels].AccessID)"
strSQL2 = strSQL2 + " ON tbl_SubLevels_Master.SubLevelID = tbl_SubLevels_Users.SubLevelID)"
strSQL2 = strSQL2 + " ON tbl_Users.UserID = tbl_SubLevels_Users.UserID"
strSQL2 = strSQL2 + " WHERE (((tbl_SubLevels_Users.Access_" & Counter & ")>0));"

MsgBox "strSQL2 set"


If Counter < 61 Then
MsgBox strSQL1 'just to make sure it is set
DoCmd.RunSQL strSQL1
MsgBox "RAN 1"
Else
DoCmd.RunSQL strSQL2
MsgBox "RAN 2"
End If

strSQL1 = ""

stMacName = "macro_Audit_Append to Audit Level 2 and 3"
DoCmd.RunMacro stMacName
MsgBox "RAN MACRO"

Counter = Counter + 1
MsgBox Counter
Wend



Sign In or Register to comment.