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]

Hi guys many thanks to you all I will need a little bit of time to work through all your input!

@Norie.. I thought it would need to be in the load event as I am using addItem for the comboxes..??

As I said I will get back to you in a while and see if I can work it out from your suggestions!

Many thanks to all of you in the meantime!!

Albert

 

[Norie]

Albert

Where are the comboboxes located?

 

[silentwolf]

Hi again,

Now I changed my code to following... but not sure where to go from here

Public Sub ListenfeldAktualisieren()
    Dim strSQL As String
    Dim strSQLSelect As String
    Dim strSQLWhere As String
    
'Basic Query (SELECT TEIL)
    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 " & _
    "ORDER BY tblObjekte.objID;"
  
'Evaluate first Combobox
    If Not IsEmpty(Me!cboOrtAuswahl) Then
        strSQLWhere = "WHERE objOrt ='" & Me!cboOrtAuswahl & "'"
    End If
   
'Evaluate second Combobox
     If Not IsEmpty(Me!cboObjektStatus) Then
        If Len(strSQLWhere) > 0 Then
            strSQLWhere = strSQLWhere & " AND"
        End If
        strSQLWhere = strSQLWhere _
        & Me!cboObjektStatus.Column(1)
    End If

'ADD SELECT Part to SQL-Expression
        strSQL = strSQLSelect
        
'If WHERE Condition Exist
'ADD WHERE-Part to SQL-Expression
    If Len(strSQLWhere) > 0 Then
        strSQL = strSQL & " WHERE " & strSQLWhere
    End If
   
'New Recordset and Requery Listbox
    Me!lstObjekte.RowSource = strSQL
    Me!lstObjekte.Requery
End Sub

So I got this code now but not sure where to go from here...?

 

[silentwolf]

Hi Norie,

the comboboxes and listbox is in one form..
So I like to open the form add Items to the comboboxes and change the listbox after the comboboxes are selected..

 

[silentwolf]

I changed the Public Sub to Private Sub and put it into the codemodule of the form...

 

[Norie]

Albert

If you want the form to be filtered when selections are made in the combobox you should be calling from the change event of one of the comboboxes.

Also, you aren't adding or creating the WHERE clause correctly, this is how I would add it -

Public Sub ListenfeldAktualisieren()
Dim strSQL As String
Dim strSQLWhere As String

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

    'Evaluate second Combobox
    If Not IsEmpty(Me!cboObjektStatus) Then
        If Len(strSQLWhere) > 0 Then
            strSQLWhere = strSQLWhere & " AND"
        End If
        strSQLWhere = strSQLWhere _
                      & Me!cboObjektStatus.Column(1)
    End If

    'If WHERE Condition Exist
    'ADD WHERE-Part to SQL-Expression
    If Len(strSQLWhere) > 0 Then
        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 " & _
                 strSQLWhere & _
                 " ORDER BY tblObjekte.objID;"

    End If

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

I'd post something for constructing the WHERE clause but I'm not sure which field the value from cboObjektStatus is the criteria for.

 

[silentwolf]

Hi Norie,

the second combobox is a value list.. I got (Alle);Aktiv;Inaktiv in the cboObjStatus

I misspelt it!!!
However here I am pretty sure the code is wrong what I got..

The field I need to filter is a YES/No Field .. so I guess I need some Select Case Statement somewhere ..

So if I have a Private Sub ListenfeldAktualisieren()
in the form code module and then call this sub from the change_event of the combobox is that what you mean?

Thanks!!

 

[Norie]

Have you tried doing the filtering with a simple hard-coded SQL statement?

If you can get that to work the code can be adapted to incorporate the comboboxes.

 

[silentwolf]

Hi,
yes here is the SQL Statement if I like to get all aktiv Objects

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
WHERE (((tblObjekte.objAktiv)=Yes))
ORDER BY tblObjekte.objID;

or this if there is a town and Aktiv Objects

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
WHERE (((tblObjekte.objOrt)="Testinghausen") AND ((tblObjekte.objAktiv)=Yes))
ORDER BY tblObjekte.objID;

is that what you meant?

This is driving me insane lol..

 

[Norie]

You should start with simple SQL like that just to get the code up and running.

Once you've done that then you can add code that takes the values from the comboboxes and incorporates them in the SQL.