f



"Run-Time Error '3012' : Object "------------" already exists

Dear Sir or Madam :

Hello !

Newbie to Access 2010 programming here ; I've a form which is almost complete -- HOWEVER, whenever selecting a specific pull-down, I'm thrown the error : "Run-time error '3012' : Object "------------" already exists "

Where "------------" is a 'set' statement...  Hmmm...  So...  My code's below and if anyone can take a gander, it would be greatly appreciated !  ( The error checking MsgBoxes are showing me the data being returned is correct. ) It will help my office run more smoothly...  Thanks in advance !  -- B.

Private Sub UserID_Click()

Dim dbs As Database, qdf As QueryDef, sqlQry As String, strSQL As String

Set dbs = CurrentDb

    If (Court222 = "Middlesex") Then
        strSQL = "SELECT Middlesex FROM Pricing WHERE Testpanel ='" & Panel & "'"
    ElseIf (Court222 = "Suffolk") Then
        strSQL = "SELECT Suffolk FROM Pricing WHERE Testpanel ='" & Panel & "'"
    ElseIf (Court222 = "Essex") Then
        strSQL = "SELECT Essex FROM Pricing WHERE Testpanel ='" & Panel & "'"
    Else
        strSQL = "SELECT Norfolk FROM Pricing WHERE Testpanel ='" & Panel & "'"
    End If
    
    MsgBox "strSQL: " & strSQL
    
   'THE LINE BELOW IS WHERE THE ERROR OCCURS FOR "SECONDQUARTER"
    Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)

    Amount = qdf
    

End Sub

0
bdkodt (1)
1/31/2014 5:57:23 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

2 Replies
854 Views

Similar Articles

[PageSpeed] 51

On 31/01/2014 17:57:24, bdkodt wrote:
> Private Sub UserID_Click()
> 
> Dim dbs As Database, qdf As QueryDef, sqlQry As String, strSQL As String
> 
> Set dbs = CurrentDb
> 
> If (Court222 = "Middlesex") Then
> strSQL = "SELECT Middlesex FROM Pricing WHERE Testpanel ='" & Panel & "'"
> ElseIf (Court222 = "Suffolk") Then
> strSQL = "SELECT Suffolk FROM Pricing WHERE Testpanel ='" & Panel & "'"
> ElseIf (Court222 = "Essex") Then
> strSQL = "SELECT Essex FROM Pricing WHERE Testpanel ='" & Panel & "'"
> Else
> strSQL = "SELECT Norfolk FROM Pricing WHERE Testpanel ='" & Panel & "'"
> End If
> 
> MsgBox "strSQL: " & strSQL
> 
> 'THE LINE BELOW IS WHERE THE ERROR OCCURS FOR "SECONDQUARTER"
> Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)
> 
> Amount = qdf
> 
> 
> End Sub

Are you saying you actually have 4 fields in your Pricing table called
"Essex, Suffolk", "Norfolk" and "Middlesex"

Have you also fields NAMED all the other counties?

I suspect that "Essex, Suffolk", "Norfolk" and "Middlesex" are actually the
value of a field called "County"

Anyway, when you get it to run correctly, you will create the Query
"SecondQuarter". If you run the routine a second time it will fail because
the query already exists and you can't create it again.

If you really want to have a new version of SecondQuarter then you need

    On Error Resume Next
    DoCmd.DeleteObject acQuery, "SecondQuarter"
 anywhere before

Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)

Phil

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

0
Phil
1/31/2014 7:04:28 PM
Phil wrote:
> On 31/01/2014 17:57:24, bdkodt wrote:
>> Private Sub UserID_Click()
>>
>> Dim dbs As Database, qdf As QueryDef, sqlQry As String, strSQL As
>> String
>>
>> Set dbs = CurrentDb
>>
>> If (Court222 = "Middlesex") Then
>> strSQL = "SELECT Middlesex FROM Pricing WHERE Testpanel ='" & Panel
>> & "'" ElseIf (Court222 = "Suffolk") Then
>> strSQL = "SELECT Suffolk FROM Pricing WHERE Testpanel ='" & Panel &
>> "'" ElseIf (Court222 = "Essex") Then
>> strSQL = "SELECT Essex FROM Pricing WHERE Testpanel ='" & Panel & "'"
>> Else
>> strSQL = "SELECT Norfolk FROM Pricing WHERE Testpanel ='" & Panel &
>> "'" End If
>>
>> MsgBox "strSQL: " & strSQL
>>
>> 'THE LINE BELOW IS WHERE THE ERROR OCCURS FOR "SECONDQUARTER"
>> Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)
>>
>> Amount = qdf
>>
>>
>> End Sub
>
> Are you saying you actually have 4 fields in your Pricing table called
> "Essex, Suffolk", "Norfolk" and "Middlesex"
>
> Have you also fields NAMED all the other counties?
>
> I suspect that "Essex, Suffolk", "Norfolk" and "Middlesex" are
> actually the
> value of a field called "County"
>
> Anyway, when you get it to run correctly, you will create the Query
> "SecondQuarter". If you run the routine a second time it will fail
> because
> the query already exists and you can't create it again.
>
> If you really want to have a new version of SecondQuarter then you
> need
>
>    On Error Resume Next
>    DoCmd.DeleteObject acQuery, "SecondQuarter"
> anywhere before
>
> Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)
>
> Phil
>

Also the line   "Amount = qdf"    is pointless.  For a start "Amount" is not 
defined and I suspect it is intended to be a variable to receive some data 
which will not happen doing this.   This line will not run the query nor 
yield any data even if "Amount" was defined.  You need to study more the 
relationship between querydefs, recordsets and fields.  You would open a 
recordset based on the querydef then inspect fields within it.



D



0
David
1/31/2014 9:39:33 PM
Reply: