Runtime Error 3012 Object Exist

[Vfletch]

Hi, I have several users of a database that I built who keep getting the runtime error 3012. Not all users are getting it. Its a split database and users can enter a case number. The code is:

Private Sub Select_Agreement_Type_BeforeUpdate(Cancel As Integer)
MsgBox "Please wait while we verify this request. " & vbCr & vbCr & "This may take a couple minutes. You will be prompted when you can proceed.", vbOKOnly + vbExclamation, "Request Verification"
If Me.Select_Agreement_Type = "WAIVER" Then
Dim RS As DAO.Recordset
Dim DB As DAO.Database
Dim qdf As QueryDef
Dim qdfOutput As QueryDef
Dim strQryName As String
Dim strOutputQryName As String
Dim SETS_Case_Number As String
Dim strSql As String
Dim strSQLOutput As String

Set DB = CurrentDb
Set RS = CurrentDb.OpenRecordset("Reduction Details")

With RS
.MoveFirst
Do While Not .EOF
strSql = "SELECT [qry_Waiver_Check].* FROM [qry_Waiver_Check] " & _
"WHERE ((([qry_Waiver_Check].[SETS Case Number])=""" & Me.SETS_Case_Number & """));"

Set qdf = DB.CreateQueryDef("qrySETSNumberWaiver", strSql)
strQryName = "qrySETSNumberWaiver"

If DCount("*", "qrySETSNumberWaiver") > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "There is already an active negotiation on this case." _
& vbCr & vbCr & "Only one waiver allowed per case. Please Review Case.", _
vbInformation, "Duplicate Information"
Cancel = True
End If
DB.QueryDefs.Delete qdf.Name
.MoveNext
Loop
End With
RS.Close
ElseIf Me.Select_Agreement_Type = "LUMP SUM COMPROMISE" Then
Set DB = CurrentDb
Set RS = CurrentDb.OpenRecordset("Reduction Details")

With RS
.MoveFirst
Do While Not .EOF
strSql = "SELECT [qry_compromise_check].* FROM [qry_compromise_check] " & _
"WHERE ((([qry_compromise_check].[SETS Case Number])=""" & Me.SETS_Case_Number & """));"

Set qdf = DB.CreateQueryDef("qrySETSNumberCompromise", strSql)
strQryName = "qrySETSNumberCompromise"

If DCount("*", "qrySETSNumberCompromise") > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "There is already an active negotiation on this case." _
& vbCr & vbCr & "Please Review Case.", _
vbInformation, "Duplicate Information"
Cancel = True
End If
DB.QueryDefs.Delete qdf.Name
.MoveNext
Loop
End With

RS.Close

ElseIf Me.Select_Agreement_Type = "INSTALLMENT PLAN COMPROMISE" Then
Set DB = CurrentDb
Set RS = CurrentDb.OpenRecordset("Reduction Details")

With RS
.MoveFirst
Do While Not .EOF
strSql = "SELECT [qry_compromise_check].* FROM [qry_compromise_check] " & _
"WHERE ((([qry_compromise_check].[SETS Case Number])=""" & Me.SETS_Case_Number & """));"

Set qdf = DB.CreateQueryDef("qrySETSNumberCompromise", strSql)
strQryName = "qrySETSNumberCompromise"

If DCount("*", "qrySETSNumberCompromise") > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "There is already an active compromise on this case." _
& vbCr & vbCr & "Please Review Case.", _
vbInformation, "Duplicate Information"
Cancel = True
End If
DB.QueryDefs.Delete qdf.Name
.MoveNext
Loop
End With

RS.Close
ElseIf Me.Select_Agreement_Type = "FAMILY SUPPORT PROGRAM" Then
Set DB = CurrentDb
Set RS = CurrentDb.OpenRecordset("Reduction Details")

With RS
.MoveFirst
Do While Not .EOF
strSql = "SELECT [qry_compromise_check].* FROM [qry_compromise_check] " & _
"WHERE ((([qry_compromise_check].[SETS Case Number])=""" & Me.SETS_Case_Number & """));"

Set qdf = DB.CreateQueryDef("qrySETSNumberCompromise", strSql)
strQryName = "qrySETSNumberCompromise"

If DCount("*", "qrySETSNumberCompromise") > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "There is already an active compromise on this case." _
& vbCr & vbCr & "Please Review Case.", _
vbInformation, "Duplicate Information"
Cancel = True
End If
DB.QueryDefs.Delete qdf.Name
.MoveNext
Loop
End With

RS.Close

End If
End Sub



Please help!

 

[pbaldy]

What line throws the error?

FYI I moved this out of the introductions forum.

 

[The_Doc_Man]

It is good to post the code, but can you give us the purpose of that sequence in English?

Object existence errors are usually caused by "loose" handling of objects. Don't forget that recordsets, because they use the "Set xxx = something.Openxxxx" syntax, ARE objects. Look for opening but not closing, or of not opening before attempting to use. But you have to figure each possible path for your code and see what paths do or don't open or close objects.

 

[static]

You can't randomly create, delete or change objects in multi user environments.
You keep opening recordset "Reduction Details" but I can't see that it's actually used by anything.

Private Sub Select_Agreement_Type_BeforeUpdate(Cancel As Integer)

	Dim DB As DAO.Database
	Dim RS As DAO.Recordset
	Dim qdf As QueryDef
	Dim strQryName As String
	Dim strSql As String
	dim msg as string
	
	Set DB = CurrentDb
	'Set RS = DB.OpenRecordset("Reduction Details")
		
	MsgBox "Please wait while we verify this request. " & vbCr & vbCr & "This may take a couple minutes. You will be prompted when you can proceed.", vbOKOnly + vbExclamation, "Request Verification"

	select case Me.Select_Agreement_Type
	case "WAIVER" 
		strSql = "SELECT [qry_Waiver_Check].* FROM [qry_Waiver_Check] WHERE ((([qry_Waiver_Check].[SETS Case Number])=""" & Me.SETS_Case_Number & """));"
		Msg = "There is already an active negotiation on this case." & vbCr & vbCr & "Only one waiver allowed per case. Please Review Case."
		strQryName = "qrySETSNumberWaiver"
	case "LUMP SUM COMPROMISE" , "INSTALLMENT PLAN COMPROMISE" , "FAMILY SUPPORT PROGRAM" 
		strSql = "SELECT [qry_compromise_check].* FROM [qry_compromise_check] WHERE ((([qry_compromise_check].[SETS Case Number])=""" & Me.SETS_Case_Number & """));"
		Msg = "There is already an active negotiation on this case." & vbCr & vbCr & "Please Review Case."
		strQryName = "qrySETSNumberCompromise"
	end select
	

	'Do While Not RS.EOF
		'Set qdf = DB.CreateQueryDef(strQryName, strSql)
		set rs = db.openrecordset(strSql)
		if not rs.eof then
		'If DCount("*", strQryName) > 0 Then
			Me.Undo
			MsgBox msg, vbInformation, "Duplicate Information"
			Cancel = True
		End If
		'DB.QueryDefs.Delete qdf.Name
		'RS.MoveNext
	'Loop

	RS.Close
	
End Sub

 

[Vfletch]

Hi, the purpose is to allow users to enter a new case into the database. A query is ran behind the scenes to determine if the case and agreement type have ever been entered. If the agreement with that case it must be completed or end dated before a new one can be entered. What I am finding is the query of the case of the case is being entered, but not deleting itself later. My error occurs at:
Set qdf = DB.CreateQueryDef("qrySETSNumberWaiver", strSql)

FYI.. I'm not a programmer, however I have some access experience.

 

[The_Doc_Man]

If the error is telling you the object exists (as opposed to "Does not exist") then I think it means that somehow, qdf is still open from a prior iteration. As I suggested earlier, it is going to be caused by careless maintenance of your objects that need to be opened and closed.

Here is a bit of advice since you are in the learning stages for programming (and yes, when you start looking into explicit recordset manipulation, you ARE becoming a programmer, like it or not). Consider these "rules" (actually, guidelines) from the book "Everything I Needed to Know, I Learned in Kindergarten."

* If you open it, close it when done with it.
* If you took it out, put it back when done with it. (By extension, if you created it - such as an application or openable object - after closing, release it with Set object = Nothing.)
* If you are going to play with others, be sure to share.

 

[Cronk]

The issue here is that once the query qrySETSNumberWaiver is created, you can't create a second query with the same name.

You could, having created the query during design, just replace the sql at runtime

set qry = db.querydefs("qrySETSNumberWaiver")
qry.sql = strSql
set qry = nothing

Only do this if each user is running a separate front end.