SQL VBA WHERE Clause Issue

[silentwolf]

Hi guys,

I am again a bit stuck and not really be able to work it out on my own.

Private Sub Form_Load()
    Dim strSQL As String
    Dim strSQLSelect As String
    Dim strSQLWHERE As String

'Combobox Aktualisieren
    With Me!cboObjStatus
        .AddItem "(Alle)"
        .AddItem "Aktiv"
        .AddItem "Inaktiv"
    End With

'Standartwerte Setzen
    Me!cboOrtAuswahl = Me.cboOrtAuswahl.ItemData(0)
    Me!cboObjStatus = Me!cboObjStatus.ItemData(1)
   
'Basisabfrage(SELECT TEIL)
    strSQL = "SELECT tblObjekte.objID, tblObjekte.objAdresse, tblObjekte.objOrt, IIf([kunFirma] Is Null,[kunVorname] & "" "" & [kunNachname],[kunFirma]) AS Kontakt, tblObjekte.objAktiv " & _
    "FROM tblKunden INNER JOIN tblObjekte ON tblKunden.kunID = tblObjekte.objkunIDREF " & _
    "ORDER BY tblObjekte.objID;"

'Erstes Kombinationsfeld auswerten
    If IsNull(Me!cboOrtAuswahl) Then
        MsgBox "Nothing selected"
    Else
        MsgBox "Es wurde " & Me!cboOrtAuswahl.Value & " ausgewählt"
[COLOR=#ff0000]'here I am nor sure how to get the cboOrtAuswahl to filter my SQL Statement ....objOrt is a field [/COLOR]        
        strSQLWHERE = "objOrt =" & Me!cboOrtAuswahl
    End If
    
'SELECT TEIL zu strSQL Hinzufügen
    strSQL = strSQLSelect

    Me!lstObjekte.RowSource = strSQL
    Me!lstObjekte.Requery

End Sub

I marked above where the issue is and I am not able to work it aout..
The combobox returnes the value what I select in the combobox but the strSQLWHERE is not able to update... It is most likely again a small thing but for me a big one

Hope someone could help??

Many thanks

Albert

 

[silentwolf]

Yes I guess you are right this is to complicated for me right now!
Would have been nice if I was able to get it running but .. I will try a different approach so it is working..

Thanks alot to you and all the other guys who helped me with this!!!

Cheers

 

[Norie]

Albert

This is rough and of course untested but give it a try anyway.

Private Sub cboObjektStatus_Change()
Dim strSQL As String
Dim strSQLWhere As String

    'Evaluate first Combobox
    If Not IsEmpty(Me!cboOrt) Then
        strSQLWhere = " WHERE objOrt ='" & Me!cboOrt & "'"
    End If

    'Evaluate second Combobox
    If Len(strSQLWhere) = 0 Then
        Select Case Me!cboObjektStatus.Column(1)
            Case 0
                strSQLWhere = " WHERE objAktiv = No"
            Case 1
                strSQLWhere = " WHERE objAktiv = Yes"
        End Select
    Else
        Select Case Me!cboObjektStatus.Column(1)
            Case 0
                strSQLWhere = strSQLWhere & " AND objAktiv = No"
            Case 1
                strSQLWhere = strSQLWhere & " AND objAktiv = Yes"
        End Select
    End If

    strSQLSelect = " SELECT tblObjekte.objID, tblObjekte.objAdresse, tblObjekte.objOrt, IIf([kunFirma] Is Null,[kunVorname] & "" "" & [kunNachname],[kunFirma]) AS Kontakt, tblObjekte.objAktiv " & _
                   " FROM tblKunden INNER JOIN tblObjekte ON tblKunden.kunID = tblObjekte.objkunIDREF " & _
                   strSQLWhere & _
                   " ORDER BY tblObjekte.objID;"

    Debug.Print strSQL

    'New Recordset and Requery Listbox
    Me!lstObjekte.RowSource = strSQL
    Me!lstObjekte.Requery

End Sub

PS You can always test the SQL statement produced by the code by copying it from the Immediate Window (CTRL+G), going to Create>Query Design, pasting it into the SQL view and clicking Run!.

 

[silentwolf]

Thanks Norie,
I will try it!
Still not quite there but I will try I keep you posted!

Many thanks for your input! Much appreciated!!

Cheers

 

[silentwolf]

Norie,

I made a mistake now it seams to work but what am I supose to write into the cboObjektStatus?

What values?
Sorry for that but that should be the last thing for today

 

[Norie]

Eh, to be honest I don't know - I thought you had already populated that combobox.

Mind you if it's only going to be Yes/No then it might be an idea to use a checkbox (or option buttons).

 

[silentwolf]

hmm ok well I had a value List with (Alle);Aktiv;Inaktiv ....

So I will try with an optiongroup and option buttons

Thanks I should be able to get there I hope

Cheeers!!

 

[Norie]

I'm confused, I thought you had a combobox that was a simple Yes/No.

If you don't ignore my suggestion of using a combobox or option buttons.

 

[silentwolf]

in the combobox I have a value list wich has (Alle) supose to mean all, then Aktiv supose to be Yes and Inaktiv supose to be No...
As I like to be able to either show all or aktiv or inaktiv objects