SQL Runtime Error 3075

[silentwolf]

Hi guys,

Not sure if the information I am giving is enough for you to help me.

This is a code I try to run but I am getting an runtime Error 3075 missing element or in query it is in the WHERE Clause..

Private Sub btnTest_Click()
    Dim strSQL As String
    Dim strSQLTeil As String

    Dim Dateiname As String


    Select Case Me!fraStatus
        Case 1
            strSQLTeil = "tblStatus.staID=1"
        Case 2
            strSQLTeil = "tblStatus.staID=2"
        Case Else
            strSQLTeil = "tblStatus.staID>0"
     End Select
     
strSQL = "SELECT tblObjekte.objID, IIf([kunFirma] Is Null,[kunVorname] & "" "" & [kunNachname],[kunFirma]) AS Kontakt, tblObjekte.objAdresse, tblObjekte.objOrt, tblStatus.staID " & _
"FROM tblStatus INNER JOIN (tblKunden INNER JOIN tblObjekte ON tblKunden.kunID = tblObjekte.objKunIDRef) ON tblStatus.staID = tblObjekte.objStatIDRef " & _
"WHERE [COLOR=#ff0000](((tblObjekte.objID)=[Formulare]![frmObjektAuswahl]![cboTest]) AND " & strSQLTeil[/COLOR]
    
    CurrentDb.QueryDefs("qryObjektTest").SQL = strSQL
    
         DoCmd.OpenQuery "qryObjektTest"
End Sub

does anyone of you guys know where I this is wrong?

Thanks

 

[silentwolf]

Hi James_lankford,
thank you for your mail!
I got it working now! But still got an issue..

Private Sub btnUpdate_Click()
    Dim strSQL As String
    Dim strSQLTeil As String
    Dim Dateiname As String
    
        Select Case Me!fraStatus
            Case 1
                strSQLTeil = "tblStatus.staID=3"
            Case 2
                strSQLTeil = "tblStatus.staID=2"
            Case Else
                strSQLTeil = "tblStatus.staID>0"
        End Select
    
        strSQL = strSQL & " SELECT tblObjekte.objID, IIf([kunFirma] Is Null,[kunVorname] & "" "" & [kunNachname],[kunFirma]) AS Kontakt, tblObjekte.objAdresse, tblObjekte.objOrt, tblStatus.staBezeichnung,tblStatus.staID  "
        strSQL = strSQL & " FROM tblStatus INNER JOIN (tblKunden INNER JOIN tblObjekte ON tblKunden.kunID = tblObjekte.objKunIDRef) ON tblStatus.staID = tblObjekte.objStatIDRef "
        strSQL = strSQL & " WHERE tblObjekte.objOrt=[Formulare]![frmObjektAuswahl]![cboObjOrte] AND " & strSQLTeil
    
        CurrentDb.QueryDefs("qryObjektAuswahl").SQL = strSQL
        
        Me!lstObjekte.Requery
        DoCmd.OpenQuery "qryObjektAuswahl"
End Sub

How can I now update the listbox "lstObjekte" ?
I got is sorted that the qryObjektAuswahl gets displayed and updated the listbox rowsource in the form for that query .. I guess there is the problem or?

I guess I supose to leave it blank or? Sorry am confused

Thanks

 

[Norie]

How are you populating the listbox?

 

[silentwolf]

Hi Norie,

On Load event

Private Sub Form_Load()
    Forms!frmObjektAuswahl!cboObjOrte.RowSourceType = "Table/Query"
    Forms!frmObjektAuswahl![cboObjOrte].RowSource = "qryObjektOrteDynamisch"
    
    
    Me!cboObjOrte = Me!cboObjOrte.ItemData(0)
    Me!lstObjekte.RowSource = "qryObjektAuswahl"
    Me!lstObjekte.Requery
End Sub

 

[silentwolf]

Hi Norie,

as soon I wrote this I got it sorted!
It works now!!
However there is one thing I like to change.. I don't like to press a button I like to have it updated as soon I change the frame.. so in the after update event of the fraStatus or?
Or is it better the change event? .)